Page 1 of 1

MYSQL queries

Posted: Thu Jul 05, 2007 10:05 am
by aceconcepts
Hi,

I am a bit perplexed with this loop problem I have.

I have 3 tables tblCategory and tblSubCategory.

What I want to do is display a list (left menu) of all categories with their sub categories below each parent category.

To get this working I currently use 2 while loops, one to loop the categories and the other to loop the sub categories where related to the parent category

e.g.

Code: Select all

$getCat=mysql_query("SELECT * FROM tblCategory") or bla bla;
   while($catRow=mysql_fetch_array($getCat))
   {
      extract($catRow);
      //display the category name

            $getSubCat=mysql_query("SELECT * FROM tblSubCategory") or bla bla;
                while($catSubRow=mysql_fetch_array($getSubCat))
                {
                    extract($catSubRow);
                   //display the category name
                 }
   }
Is there a more efficient was of doing this, like within one query?

Posted: Thu Jul 05, 2007 10:56 am
by superdezign
One query? Yes, but it's not very practical.

What you'll want to do is save them in arrays that identify their relationship.

Code: Select all

$result = mysql_query('select `id, `name` from `categories`;');
while($res = mysql_fetch_object($result))
{
    $cat[$res->id] = $res->name;
}
$result = mysql_query('select `id`,`name`,`parentCategory` from `subcategories`;');
while($res = mysql_fetch_object($result))
{
    $sub[$res->parentCategory][$res->id] = $res->name;
}

print_r($cat);
print_r($sub);

Posted: Thu Jul 05, 2007 12:53 pm
by RobertGonzalez
This has been asked many times before. And superdezign's answer is the one I have given probably 30 times this year. 2 queries, 2 loops with the resultant array of the queries.

Code: Select all

<?php
// Start with categories
$sql = 'SELECT id, cat_name FROM cats';
if (!$result = mysql_query($sql)) {
  die('I have to die because I suck at talking to database tables.');
}

$cats = array();
while ($row = mysql_fetch_array($result)) {
  $cats[] = $row;
}
$cats_count = count($cats);

// Now subcats
$sql = 'SELECT * FROM sub_cats';
if (!$result = mysql_query($sql)) {
  die('I have to die because I suck at talking to other database tables.');
}

$subcats = array();
while ($row = mysql_fetch_array($result)) {
  $subcats[] = $row;
}
$subcats_count = count($subcats);

// Now loop them and match them
// Start with the cats
for ($i = 0; $i < $cats_count; $i++) {
  $cat_name = $cats[$i]['cat_name'];
  $cat_id = $cats[$i]['id'];
  echo 'Category: ' . $cat_name . '<br />';
  
  // Now loop the subcats for each cat
  for ($j = 0; $j < $subcats_count; $j++) {
    if ($subcats[$j]['cat_id'] == $cat_id) {
      echo $cat_name . ' -- ' . $subcats[$j]['subcat_name'] . '<br />';
    }
  }
}
?>