Welcome to WebmasterWorld Guest from 54.145.209.34

Forum Moderators: coopster & jatar k

php mysql get info from multiple tables

php mysql get info from multiple tables

   
7:45 pm on Mar 25, 2008 (gmt 0)

5+ Year Member



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?
7:59 pm on Mar 25, 2008 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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]

10:24 pm on Mar 25, 2008 (gmt 0)

5+ Year Member



thank you sir! love this forum & community :)
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month