Page 1 of 1

displaying from 2 tables and order by 1 row

Posted: Fri Feb 06, 2009 2:01 pm
by meee
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:

Code: Select all

 
$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!

Re: displaying from 2 tables and order by 1 row

Posted: Fri Feb 06, 2009 2:27 pm
by Skoalbasher
meee wrote: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:

Code: Select all

 
$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!
Try this instead

Code: Select all

 
$result = "SELECT table1.mainTitle, table2.xTitle FROM table1, table2 WHERE table1.userID='$id' AND table2.id='$id' ORDER BY table1.time";
 

Re: displaying from 2 tables and order by 1 row

Posted: Fri Feb 06, 2009 3:31 pm
by meee
thanks, that almost works but still I have some problems. From the following query, it will shows only the rows from table2.

Code: Select all

 
$result = mysql_query("SELECT table1.UserID as test, table2.ID as test FROM users, table2 WHERE table1.UserID='$ID' AND table2.ID='$ID' ORDER BY table2.updateTime");
while($row = mysql_fetch_assoc($result)) {
    extract ($row);
    echo $test." ";
    }
 
Second problem is I need first to order by time data from both table1 and table2 as it was one table and then print it together as it was one table.

Re: displaying from 2 tables and order by 1 row

Posted: Fri Feb 06, 2009 3:35 pm
by VladSun
[sql](SELECT     * FROM     One WHERE     UserID=$IDUNION SELECT    * FROM     Two WHERE     ID=$ID) ORDER BY     time [/sql]

Why do you put selects into single quotes?

Re: displaying from 2 tables and order by 1 row

Posted: Fri Feb 06, 2009 5:06 pm
by meee
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:

Code: Select all

 
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?

Re: displaying from 2 tables and order by 1 row

Posted: Fri Feb 06, 2009 5:15 pm
by VladSun
No! Don't use temp tables!

I had a mistake in my SQL query - remove the brackets:

[sql]SELECT     * FROM     One WHERE     UserID=$IDUNION SELECT    * FROM     Two WHERE     ID=$IDORDER BY     time[/sql]

Re: displaying from 2 tables and order by 1 row

Posted: Fri Feb 06, 2009 5:31 pm
by Skoalbasher
VladSun wrote:No! Don't use temp tables!

I had a mistake in my SQL query - remove the brackets:

[sql]SELECT     * FROM     One WHERE     UserID=$IDUNION SELECT    * FROM     Two WHERE     ID=$IDORDER BY     time[/sql]
psssh. And they call you "The Manual" :wink:

Re: displaying from 2 tables and order by 1 row

Posted: Fri Feb 06, 2009 6:12 pm
by meee
it doesn't work. Maybe the problem is there are columns with different names. That is why I tried also with tmp table.

Code: Select all

 
$result=mysql_query("SELECT * FROM One WHERE UserID='$ID' UNION SELECT * FROM Two WHERE ID='69' ORDER BY time");
while($row = mysql_fetch_assoc($result)) {
    echo $row[time];
    }
 
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in E:\xampp\htdocs\Home.php on line 27.
The line 27 is while...

Re: displaying from 2 tables and order by 1 row

Posted: Sat Feb 07, 2009 4:38 am
by VladSun
Skoalbasher wrote:psssh. And they call you "The Manual" :wink:
The man tried :) Now he needs help.

Re: displaying from 2 tables and order by 1 row

Posted: Sat Feb 07, 2009 4:40 am
by VladSun
meee wrote:it doesn't work. Maybe the problem is there are columns with different names. That is why I tried also with tmp table.

Code: Select all

 
$result=mysql_query("SELECT * FROM One WHERE UserID='$ID' UNION SELECT * FROM Two WHERE ID='69' ORDER BY time");
while($row = mysql_fetch_assoc($result)) {
    echo $row[time];
    }
 
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in E:\xampp\htdocs\Home.php on line 27.
The line 27 is while...
Use

Code: Select all

mysql_query(' .... ') or die(mysql_error());
Post the results from:
[sql]DESCRIBE One;[/sql]
[sql]DESCRIBE Two;[/sql]

Re: displaying from 2 tables and order by 1 row

Posted: Sat Feb 07, 2009 6:05 am
by meee
Skoalbasher tnx, you are great! The error was: The used SELECT statements have a different number of columns
So I change from * to name of columns and it works!

And for first example where I used temporary table the error was in second query. I still haven't solved the problem yet for it. I will use UNION, but anyway I am curious what is wrong below:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO tmp' at line 1

mysql_query("CREATE TEMPORARY TABLE tmp (`ID` INT( 5 ) NOT NULL , `updateTime` DATETIME NOT NULL , `uTitle` VARCHAR( 25 ) NOT NULL) ENGINE = MYISAM") or die(mysql_error());
mysql_query("SELECT ID, updateTime FROM users WHERE ID='69' INSERT INTO tmp") or die(mysql_error());

Re: displaying from 2 tables and order by 1 row

Posted: Sat Feb 07, 2009 7:33 pm
by Skoalbasher
meee wrote:Skoalbasher tnx, you are great!
I know I am, but I think you're giving me credit for something I didn't do. :lol:

I think Vlad is the one you should be thanking.