SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    displaying from 2 tables and order by 1 row

    I have two tables:
    table One:
    UserID | mainTitle | time
    table Two:
    ID | xTitle | time

    Time is in datetime format.

    I would like to select rows mainTitles and time from table one and rows xTitles and time from table two. Then I would like to order all results by time.

    I tried the following code, but it doesn't work:
    PHP Code:
    $result mysql_query("('SELECT * FROM One WHERE UserID=$ID') UNION ('SELECT * FROM Two WHERE ID=$ID') ORDER BY time");
    while(
    $row mysql_fetch_assoc($result)) {
    ...
        } 
    Please help me how to print fields from mainTitle and xTitle in the way to be ordered by time.

    Thanks!

  2. #2
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found something about temporary table, I think that would be best solution. But still it doesn't work, I don't know what I did wrong:
    PHP Code:
    mysql_query("CREATE TEMPORARY TABLE tmp (`ID` INT( 5 ) NOT NULL , `updateTime` DATETIME NOT NULL , `uTitle` VARCHAR( 25 ) NOT NULL) ENGINE = MYISAM");
    mysql_query("SELECT ID, updateTime FROM users WHERE ID='69' INSERT INTO tmp");
    mysql_query("SELECT ID, uTitle, updateTime FROM uTitles WHERE ID='69' INSERT INTO tmp");
    $result mysql_query("SELECT ID, uTitle, updateTime FROM tmp WHERE ID='69'");
    while(
    $row mysql_fetch_assoc($result)) {
        echo 
    $row[ID].$row[updateTime].$row[uTitle];
        } 
    I was expecting to get ID, updateTime and uTitle and the next step would be to order by updateTime. But why nothing shows as there are data inside table users and uTitles?
    Last edited by meee; Feb 6, 2009 at 17:07.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by meee View Post
    I tried the following code, but it doesn't work:
    yes, and here's why...

    the query that you sent to mysql consisted of a UNION of two character strings
    Code:
    ('SELECT * FROM One WHERE UserID=$ID') 
    UNION 
    ('SELECT * FROM Two WHERE ID=$ID') 
    ORDER BY time
    mysql expects to see SELECT statements in a UNION

    can you see why those are strings, and not SELECT statements?

    your query should look like this instead --
    Code:
    ( SELECT * FROM One WHERE UserID=$ID ) 
    UNION 
    ( SELECT * FROM Two WHERE ID=$ID ) 
    ORDER BY time
    see the difference?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    still doesn't work, probably because there are columns with different names in each table. But I thought creating temporary table will solve this problem, but still the code I posted for tmp table doesn't work.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    different column names do not matter -- different numbers of columns does matter

    however, your tables only have 3 columns each, so that cannot be the problem

    please test the query outside of php and show us the results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •