php mysql get info from multiple tables

[drooh]

I am using php to peform mysql queries. I have a few tables that have certain fields in common. Is there a way to query more than one table and fetch an array of the same named fileds?

for instance I want to get an array of task_id and order it ASC from the following tables.

table name = task_type_print
table name = task_type_video

$sql = "SELECT * FROM `task_type_print`, `task_type_video`";

$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){

echo $row['task_id']."<br />";

}

I've read some on using JOIN's and UNION's but I can't seem to get this to work correctly. Can someone post an example of how to do this?

[Demaestro]

When you are selecting from more then one table you have to be explicit about what fields you want from which table.

Typically you would do this by giving the tables an alias

So here I have aliased the tables as ttp and ttv respectively and I am asking for it to return all the fields from ttp, and from ttv

SELECT ttp.*, ttp.* FROM task_type_print ttp, task_type_video ttv"

Now if they have ids that relate them to each other it would be: (the following method uses an inner join)

SELECT ttp.*, ttv.* FROM task_type_print ttp, task_type_video ttv where ttp.task_id = ttv.task_id"

But this will only work if they both have a field named task_id.

To pick and choose what fields you want remove the star and replace with field names.... If they have the same name you can assign them unique ones like this.

SELECT ttp.task_id as pt_id, ttv.task_id as vt_id, ttp.task_title FROM task_type_print ttp, task_type_video ttv where ttp.task_id = ttv.task_id"

If you need to show data even if it is missing then a left join can work

SELECT ttp.task_id as pt_id, ttv.task_id as vt_id, ttp.task_title FROM task_type_print ttp left join task_type_video ttv on ttp.task_id = ttv.task_id"

[edited by: Demaestro at 8:00 pm (utc) on Mar. 25, 2008]

[drooh]

thank you sir! love this forum & community :)