displaying from 2 tables and order by 1 row

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
meee
Forum Newbie
Posts: 18
Joined: Wed Feb 04, 2009 1:38 pm

displaying from 2 tables and order by 1 row

Post 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!
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: displaying from 2 tables and order by 1 row

Post 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";
 
meee
Forum Newbie
Posts: 18
Joined: Wed Feb 04, 2009 1:38 pm

Re: displaying from 2 tables and order by 1 row

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: displaying from 2 tables and order by 1 row

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
meee
Forum Newbie
Posts: 18
Joined: Wed Feb 04, 2009 1:38 pm

Re: displaying from 2 tables and order by 1 row

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: displaying from 2 tables and order by 1 row

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: displaying from 2 tables and order by 1 row

Post 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:
meee
Forum Newbie
Posts: 18
Joined: Wed Feb 04, 2009 1:38 pm

Re: displaying from 2 tables and order by 1 row

Post 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...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: displaying from 2 tables and order by 1 row

Post by VladSun »

Skoalbasher wrote:psssh. And they call you "The Manual" :wink:
The man tried :) Now he needs help.
Last edited by VladSun on Sat Feb 07, 2009 4:40 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: displaying from 2 tables and order by 1 row

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
meee
Forum Newbie
Posts: 18
Joined: Wed Feb 04, 2009 1:38 pm

Re: displaying from 2 tables and order by 1 row

Post 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());
User avatar
Skoalbasher
Forum Contributor
Posts: 147
Joined: Thu Feb 07, 2008 8:09 pm

Re: displaying from 2 tables and order by 1 row

Post 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.
Post Reply