Select all records from multiple tables

[rayeldacx]

Hi! I\'m a newbie in php/mysql....

 

I just want to ask how can I select all the data from different tables (table_a, table_b, table_c, table_d...etc) using only one query. All the fields in the tables are the same (ID, Lname, FName, DatePosted) and I want to output the result by DatePosted (example all data with DatePosted 2003-10-30 will be displayed).

 

Thanks in advance. I really need help.

 

 

DacX

[GeoffOs]

Try the union clause. If all the tables have the same definition then you can add mulitple selects together to become one recordset:

SELECT ...

UNION [ALL]

SELECT ...

 [UNION

  SELECT ...]

Have a look at the mySQL Help here : http://www.mysql.com/doc/en/UNION.html

 

NOTE, this is implemented in MySQL 4.0.0

 

Though somebody posted on the mysql web site this work around if you are working on mysql 3....

An alternative, rather simpler (especially with very complex select statements) way to \'use union\' in 3.x might be the following:



Build a nice union query. (save it somewhere, so you can use that if you upgrade)

If you would say that query was \'(*cool_select_statement_1*) UNION (*cool_select_statement_2*) *order_and_group_by_stuff*\'.

You could make an replacement set of query\'s like this:

CREATE TEMPORARY TABLE temp_union TYPE=HEAP *cool_select_statement_1*;

INSERT INTO temp_union *cool_select_statement_2*;

SELECT * FROM temp_union *order_and_group_by_stuff*;

DROP TABLE temp_union;



Note that I\'ve use a HEAP and TEMPORARY table because that combination is rather fast and, well, temporary.

You can\'t execute these query\'s on one line (well I coudn\'t), so it would look like this in PHP:

mysql_query(\'CREATE..\', $connection);

mysql_query(\'INSERT..\', $connection);

$query = mysql_query(\'SELECT..\', $connection);

mysql_query(\'DROP..\', $connection);

[rayeldacx]

thanks man...

 

im using mysql 3.23.53 so what I did was to upgrade it to 4.0.15

so I can use UNION clause but I got another problem in running the new mysql version. I\'ll just post another topic.

 

thanks again

 

DacX