Select 1 row from 1 table and ALL rows from a second table ,

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Kais99
Forum Newbie
Posts: 2
Joined: Thu Mar 05, 2009 4:11 pm

Select 1 row from 1 table and ALL rows from a second table ,

Post by Kais99 »

hey all
i stuck with this, i spent 3 hours of internet search but didnt find solution.
i want to make a navigation like
nokia
  • nokia N95
    nokia E71
samsung
  • samsung D500
    D900
Sony Erricson
  • w110
    p10
i made 2 tables , phone, model
phone > id, name
model > id, name, phoneid

i tried this query in php
select phone.id, phone.name, model.id, model.name, model.phoneid from phone, model where model.phoneid= phone.id

it shows
nokia
nokia N95
nokia
nokia E71
nokia
some model
nokia
some model

samsung
samsung D500

samsung
D900
and soo on
if i use

Code: Select all

$query= "select * from phone";
$result = mysql_query($query);
while ($row= mysql_fetch_array($result))
{
$id =$row['id'];
$phone= $row['name'];
echo "<h1>$phone</h1>";
echo "<ul>";
//[b]2nd while loop[/b]
$query2= "select * from model where phoneid='$id'";
$result2 = mysql_query($query2);
while ($row= mysql_fetch_array($result2))
{
 
$model= $row['name'];
echo "<li>$phone</li>";
}
echo "</ul>";
this code just fetch one result
nokia
nokia n95

please help me , i shall be greatful.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Select 1 row from 1 table and ALL rows from a second table ,

Post by Benjamin »

Without knowing more I believe this is what you are looking for:

Code: Select all

 
 
$result = mysql_query("SELECT * FROM phone ORDER BY name ASC");
 
while ($brand = mysql_fetch_array($result)) {
    echo "<h1>{$brand['name']}</h1>";
    echo "<ul>";
 
    $result2 = mysql_query("SELECT * FROM model WHERE phoneid ='{$brand['id']}'");
 
    while ($models = mysql_fetch_array($result2)) {
        echo "<li>{$models['name']}</li>";
    }
 
    echo "</ul>";
}
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Select 1 row from 1 table and ALL rows from a second table ,

Post by VladSun »

Or:

Code: Select all

$result = mysql_query("
     SELECT 
          phone.name as phone_name,
          model.name as model_name
     FROM 
          phone 
     INNER JOIN 
          model ON model.phoneid = phone.id 
     ORDER BY 
          phone.name, model.name
");
 
$phonename = ''; 
while ($brand = mysql_fetch_array($result)) 
{
    if ($phonename != $brand['phone_name'])
    {
        if ($phoname)
            echo "</ul>";
        $phonename = $brand['phone_name'];
        echo "<h1>{$brand['phon_ename']}</h1>";
        echo "<ul>";
    }
    else
    { 
        echo "<li>{$brand['model_name']}</li>";
    } 
}
 
There are 10 types of people in this world, those who understand binary and those who don't
Kais99
Forum Newbie
Posts: 2
Joined: Thu Mar 05, 2009 4:11 pm

Re: Select 1 row from 1 table and ALL rows from a second table ,

Post by Kais99 »

Thanks VladSun, it works
Astions thanks for reply but it fetch just one result and then both while loops terminate.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Select 1 row from 1 table and ALL rows from a second table ,

Post by Benjamin »

Yeah it's definitly better to use one query when you can. I didn't fully understand your table structure.
Post Reply