UNION trouble

macca_25

Hi all,
I am currently having alot of trouble trying to select * from 2 tables and ordering these results via their timestamp. The two tables have different column names and data but are connected by the pm_project_id.
Here are the 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 pm_messages.pm_message_id, pm_messages.pm_message_title AS title, date_format(pm_messages.pm_message_date, '%D %b %T') AS date FROM pm_messages) 
UNION ALL 
(SELECT pm_milestones.pm_milestone_title AS title, date_format(pm_milestones.pm_milestone_subdate, '%D %b %T') AS date FROM pm_milestones) ORDER BY date DESC");

Unfortunately i get the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\easyphp1-7\www\cartgurus\admin\pm_guru\index.php on line 139

I need a way of knowing which table the data has come from and if so display the results accoringly.
Any ideas on how to achieve this?
Cheers,
macca

bastien

try a inner join query na dchanging the query format to this

$query= "SELECT pm_messages.pm_message_id, pm_messages.pm_message_title AS title, date_format(pm_messages.pm_message_date, '%D %b %T') AS message_date, pm_milestone_title AS title, date_format(pm_milestones.pm_milestone_subdate, '%D %b %T') AS  milestone_date 
FROM pm_messages inner join pm_milestones. on pm_messages.pm_project_id = pm_milestone.pm_project_id  ORDER BY date DESC";

$query_messages_today = mysql_query($query) or die ("Can't complete query ".mysql_error());

Will get you better error handling and more informative error messages...

hth

macca_25

Thanks for the reply bastein.

I have changed the code to this (with the help from your last post):

$query= "SELECT pm_messages.pm_message_title AS title, date_format(pm_messages.pm_message_date, '%D %b %T') AS date, pm_milestones.pm_milestone_title AS title, date_format(pm_milestones.pm_milestone_subdate, '%D %b %T') AS date 
FROM pm_messages inner join pm_milestones on pm_messages.pm_project_id = pm_milestones.pm_project_id ORDER BY date DESC"; 
$query_messages_today = mysql_query($query) or die ("Can't complete query ".mysql_error());

I don't get any errors now but the output is not quite right.
I'll give an example of what i would like to have happen. In the pm_messages table i have two rows of data, eg;

1,1,1,4,Hows it coming along?,Just wanted to know if you had time to finish the site,20040510205716
3,1,1,4,Just checking,Any other updates at this stage?, 20040510205527

and in the pm_milestones table i have the following:

1,1,Complete Entire Database Structure,2004-05-30,8, 20040510205616

(these comma seperated values are associated to the table structures posted earlier).
What i want to do is get all three of these results outputted because they all have a pm_project_id of 1 and i also want to order them by their timestamp, so the results should look like (assuming i am just getting the title from each row):

Hows it coming along?
Complete Entire Database Structure
Just checking

Would you have anymore ideas, i know i don't 🙂
Does that make sense? If not i'll try and explain it in more detail.
Cheers,
macca

Lars_Berg

I don't see why you should use a join for this.

The problem with your union is that the subselects have an unequal number of columns

SELECT pm_messages.pm_message_id, 
       pm_messages.pm_message_title AS title, 
       date_format(pm_messages.pm_message_date, '%D %b %T') AS date 
  FROM pm_messages
UNION ALL 
SELECT 0,
       pm_milestones.pm_milestone_title, 
       date_format(pm_milestones.pm_milestone_subdate, '%D %b %T') 
  FROM pm_milestones 
ORDER BY date DESC

macca_25

OK, but how do i add, for example a WHERE clause that only gets those rows with a pm_project_id = 1
I've tried putting the WHERE statement to no avail.

Cheers,
macca

bastien

$query= "SELECT pm_messages.pm_message_title AS title, date_format(pm_messages.pm_message_date, '%D %b %T') AS date, pm_milestones.pm_milestone_title AS title, date_format(pm_milestones.pm_milestone_subdate, '%D %b %T') AS date 
FROM pm_messages inner join pm_milestones on pm_messages.pm_project_id = pm_milestones.pm_project_id 
WHERE pm_messages.pm_project_id = 1
ORDER BY date DESC"; 

$query_messages_today = mysql_query($query) or die ("Can't complete query ".mysql_error());

What are the results you are getting? compared to what you want?

macca_25

i'm getting the following output:

Complete Entire Database Structure
Complete Entire Database Structure

I was wanting to get:

Hows it coming along?
Complete Entire Database Structure
Just checking

Lars_Berg

As Mysql does not support derived tables you need to specify it in both subselects.

SELECT pm_messages.pm_message_id, 
       pm_messages.pm_message_title AS title, 
       date_format(pm_messages.pm_message_date, '%D %b %T') AS date 
  FROM pm_messages
where pm_project_id = 1
UNION ALL 
SELECT 0,
       pm_milestones.pm_milestone_title, 
       date_format(pm_milestones.pm_milestone_subdate, '%D %b %T') 
  FROM pm_milestones 
where pm_project_id = 1
ORDER BY date DESC