is it possilbe to get data from +two tables with a single query?

[onedumbcoder]

Hi guys,

 

I was wondering is it possible to get information from two tables with one query.

 

So instead of having

 

$query = "SELECT * FROM user";
$result = mysql_query($query);
while($list = mysql_fetch_arra($result))
{
       $query = "SELECT * FROM letter WHERE user_id='" . $list['id'] . "'";
       $result2 = mysql_query($query);
       while($list2 = mysql_fetch_arra($result2))
       {
                 echo "Hello I am" . $list['name'] . " and here is my " . $list2['letter'];
        }
}

 

you could do it all with one query?

 

 

 

 

 

 

 

 

 

[zenag]

yeah  ,its possible to get data from more than two query also........

[zenag]

sorry guy ,more than two tables also..........

[Barand]

<?php 
$query = "SELECT u.name, l.letter 
            FROM user u
            INNER JOIN letter l ON u.id = l.user_id";
$result = mysql_query($query);
while($list = mysql_fetch_array($result))
{
       
     echo "Hello I am " . $list['name'] . " and here is my " . $list['letter'] . '<br/>';

}
?>

[onedumbcoder]

Thanks, but i dont understand the i and the u

if we have :

 

= "SELECT u.name, l.letter 
            FROM user u
            INNER JOIN letter l ON u.id = l.user_id";

 

 

i

 

why cant you just have

 

= "SELECT name, letter 
            FROM user 
            INNER JOIN letter ON user.id = letter.user_id";

 

can someone kindly clear that up for me?

 

Also how would i be able to grab their ids using this method?

 

for example what would $list['id'] return? the users? what if i wanted both of them

[Barand]

The "l" and the "u" are table aliases. Sometimes they are optional (as in this case), other times they're essential.

 

Saves having to repeat the table names and makes the code easier to read. OK, you have short tablenames, both from the same database and no ambiguous column names, but suppose

[pre]

widget_category      widget_sub_category

    id          --+        id

    description  |        description

                  +-      cat_id

[/pre]

 

and the category table is in the database "North_european_data"

 

Your way

SELECT North_european_data.widget_category.id, North_european_data.widget_category.description
    widget_sub_category.id,  widget_sub_category.description
FROM  North_european_data.widget_category 
    INNER JOIN widget_sub_category
    ON North_european_data.widget_category.id = widget_sub_category.cat_id

 

my way

SELECT c.id, c.description, s.id,  s.description
FROM  North_european_data.widget_category c
    INNER JOIN widget_sub_category s
    ON c.id = s.cat_id

[onedumbcoder]

Thanks, but also how would i be able to grab their ids using this method?

 

for example what would $list['id'] return? the users? what if i wanted both of them

[Barand]

"SELECT u.id, u.name, l.user_id, l.letter 
            FROM user u
            INNER JOIN letter l ON u.id = l.user_id";

 

In my last example in previous post you would alias the column names to distinguish between ids and descriptions

 

SELECT c.id, c.description, s.id as subid,  s.description as subdesc
FROM  North_european_data.widget_category c
    INNER JOIN widget_sub_category s
    ON c.id = s.cat_id