PHP/MYSQL ordering 2 combined tables by time columns

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
User avatar
1337_grl
Forum Newbie
Posts: 3
Joined: Sat May 05, 2007 12:30 am

PHP/MYSQL ordering 2 combined tables by time columns

Post by 1337_grl »

Jcart | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I'm using mysql5.0.32/php5.2.0-8, trying to get 2 tables echo'd out together and ordered by their time columns. column_id and column_id are the primary keys for each table. The data type is the same for each of the tables and looks like:

table1(column_id, time)
table2(column_id, time)

I've tried doing a UNION ALL between the sql statements, but have not been successful getting everything ordered by time.

Code: Select all

<?php
include("config.php");
$sql = "(SELECT column_id, time FROM table1) UNION ALL (SELECT column_id, time FROM table2) ORDER BY time DESC";
$query = mysql_query($sql);

while($row = mysql_fetch_array($query)) {
echo $row['column_id'] . '&nbsp;' . $row['time'] . '<br>';
}
?>
This continues to echo all rows from each table, but does not put everything in descending order by time. If you know how this can be done, please help. Thanks :!:


Jcart | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

$sql = "SELECT table1.column_id, table2.time
           FROM table1
           INNER JOIN table2 USING (column_id)
           ORDER BY table2.time DESC";
How about using an inner join

Moved to Databases.
User avatar
1337_grl
Forum Newbie
Posts: 3
Joined: Sat May 05, 2007 12:30 am

INNER JOIN

Post by 1337_grl »

INNER JOIN will not work once I start pulling data from more than 2 tables, right? I will eventually (if I ever find out how to do it) have more than 2 tables, possibly 25-30 tables being pulled together and ordered by time.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

it will work as long as there is a one to one cardinality between the relating tables.
User avatar
1337_grl
Forum Newbie
Posts: 3
Joined: Sat May 05, 2007 12:30 am

solved

Post by 1337_grl »

First, I made all of my sql statement separately. Then, I just made a while statement with multiple conditions, allowing me to output based on if statements for ordering by time. Thanks for your help 8)
Post Reply