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.
$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.
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.
$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.
$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.
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?
$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...
$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...
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());