search two mysql tables and then order by date

[johnskywalker99]

Hello
I'm creating a guestbook where entries from public and private mysql tables are all collected simultaneously and displayed on a page ordered by date.

there is a field called TYPE and DATE in both tables. I've got something like:

$guestbook_query = "guestbook";
$result = mysql_query("SELECT * FROM $newpages_db, $forum_db WHERE $newpages_db.type = '$guestbook_query' OR $forum_db.type = '$guestbook_query' ORDER BY $newpages_db.date,$forum_db.date ",$db);

but each entry returned is duplicated by the number of entries meeting the criteria ie.

entry1 = the guest book title1
entry2 = the guest book title2
entry3 = the guest book title3

it prints:

entry1 = the guest book title1
entry1 = the guest book title1
entry1 = the guest book title1
entry2 = the guest book title2
entry2 = the guest book title2
entry2 = the guest book title2
entry3 = the guest book title3
entry3 = the guest book title3
entry3 = the guest book title3

i've tried DISTICT but that obviously only returns the distinct date or type I request ie if two entries have been posted on the same day it will display the one distinct date the two were created on

Can anyone help?

Cheers John

[Lars_Berg]

You need a union query which is only supported in Mysql 4.0.x.

If Mysql 4.0 is not an option you need to do it in multiple steps.

create temp table
populate temp table with both queries
select distinct from temp table order by
drop temp table

[johnskywalker99]

Hello
I got that far I found union and thought fanatastic what a highly functional erm function...then I looked at my sql version v3.23.45 and saw that it wasn't supported (bugger).

So I went down the temp table route but thought that it was a bit o.t.t just for a guestbook but i got this far:

mysql_query("CREATE TEMPORARY TABLE Temp_Table TYPE=HEAP SELECT type, date FROM $newpages_db");
mysql_query("INSERT INTO Temp_Table SELECT type, date FROM $forum_db");
mysql_query("SELECT * FROM Temp_Table ORDER BY Date ASC");

but can't get the temp table to populate from the existing dbs. See anything wrong?

Cheers JOhn