search in all tables #2

[TwitterRooms]

Hi guys if you read my other post http://www.dynamicdrive.com/forums/showthread.php?t=67166 then you know last time i was asking about fields not tables now this time i want tables LOL

ie

$result = mysql_query("SELECT * FROM table1,table2,table3 order by 'time'");

all tables all field order by "time" field basiclly i want to search everywhere for latest inputs

[james438]

Try this:


$get_list = "(SELECT * FROM table1)
UNION
(SELECT * FROM table2)
UNION
(SELECT * FROM table3)
ORDER BY time DESC";
$get_list_res = mysql_query($get_list,$conn);

By placing the ORDER command after the last closing parenthesis you will order all of the tables by the time column. This is assuming that your tables all have the time column, which they should.

[TwitterRooms]

I have found this only works IF the tables have the same fields how would i go about doinging it with differant fields in each table??

[james438]

Sorry for the late reply. I only just checked back on this thread.

To answer your question it should be fine. Just make sure that you use the same number of columns for each table. You may want to specify the columns like this:


(SELECT ID, title, review_date, va_english FROM table1)
UNION
(SELECT ID, title, review_date, artwork FROM table2)
UNION
(SELECT ID, title, review_date, author title FROM table3)
ORDER BY review_date DESC LIMIT 900

In the example above the column names may be different and not exist in all of the tables, but the review_date does exist in all of the tables. If review_date did not exist in all three tables it would still sort it based on the tables that do have review_date, but the other tables may be sorted a little oddly. Try making sure that the number of columns specified for each table is the same. For now try to avoid using * to specify all columns and write out the ones you want making sure that the same number of columns is listed for each table.

If you are still having trouble try posting the error message you are getting and the query you are using.