Getting results from 2 tables and ordering by timestamp

macca_25

Hi all,
I am currently having alot of trouble trying to select from 2 tables and ordering these results via their timestamp.
I have the following table structures:
pm_messages

pm_message_id int(10) unsigned NOT NULL auto_increment,
client_id int(10) NOT NULL default '0',
pm_project_id int(10) NOT NULL default '0',
pm_categories_id int(10) NOT NULL default '0',
pm_message_title tinytext NOT NULL,
pm_message_text text NOT NULL,
pm_message_date timestamp(14) NOT NULL,
PRIMARY KEY  (pm_message_id)

pm_milestones

pm_milestone_id int(10) unsigned NOT NULL auto_increment,
pm_project_id int(10) NOT NULL default '0',
pm_milestone_title varchar(255) NOT NULL default '',
pm_milestone_date date NOT NULL default '0000-00-00',
assigned_group int(10) NOT NULL default '0',
pm_milestone_subdate timestamp(14) NOT NULL,
PRIMARY KEY  (pm_milestone_id)

And this is my query so far:

$query_messages_today = mysql_query("SELECT * FROM pm_messages,pm_milestones WHERE pm_messages.pm_project_id=pm_milestones.pm_project_id AND pm_messages.pm_project_id = '" . $_SESSION['project_info']['2'] . "' AND pm_messages.pm_message_date BETWEEN CURDATE() AND NOW()");
    $total_messages = mysql_num_rows($query_messages_today);

Any ideas on how i can achieve this?
Cheers,
macca

sid

depending on which timestamp you want to sort it by, add

ORDER BY pm_messages.pm_message_date

ORDER BY pm_milestones.pm_milestone_subdate

at the end of the query.
if you want the newest dates displayed first, add "DESC" as well.

one hint: use aliases for the table names - that makes it a lot easier to read and maintain that sucker.

macca_25

Thanks for the reply sid,
I should have explained it better, i'll give it another go now.

OK, i understand what you mean by the ORDER BY but what if i want to join the results of both tables and ORDER BY both timstamps (from the two tables). So, for example, if pm_messages table has rows that looks like:

1,1,1,4,Hows it coming along?,Just wanted to know if you had time to finish the site, 20040509031206 
2,2,6,8,Example Skin,I have given details of the kind of design i am after,20040507235843 
3,1,1,4,Just checking,Any other updates at this stage?, 20040507235243

and table pm_milestones looks like:

1,1,Complete Entire Database Structure,2004-05-30,25, 20040508043706

how can i get them to output the data to look something like this:

Message Title: Hows it coming along?
Milestone Title: Complete Entire Database Structure
Message Title: Example Skin
Message Title: Just checking

So i am wanting to join the tables and order the data by the timstamp field.
I hope this makes sense, if not please ask further questions and i will try and explain better.
Cheers,
macca