homepage Welcome to WebmasterWorld Guest from 54.227.40.166
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
php mysql get info from multiple tables
php mysql get info from multiple tables
drooh




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

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




msg:3610407
 7:59 pm on Mar 25, 2008 (gmt 0)

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




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

thank you sir! love this forum & community :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved