Query Help

[asa_carter]

Hi,

I have the following fields in a table:

id
title
parent_id

I have written the code below to display the data. However, I need to check at each level whether each category has a child or not. Any idea's how to check for this?

<? 
include("connect.php");

$category1 = mysql_query("SELECT id, title FROM category WHERE parent_id='00000000' ORDER BY id");
while($level1 = mysql_fetch_row($category1))
{
  echo "Level 1 - $level1[0] $level1[1]<br>";
//  if (substr($level1[0], 2, 2)!="00")
  $category2 = mysql_query("SELECT id, title FROM category WHERE parent_id='$level1[0]' ORDER BY id");
  while($level2 = mysql_fetch_row($category2))
  {
    echo "Level 2 - $level2[0] $level2[1]<br>";
    $category3 = mysql_query("SELECT id, title FROM category WHERE parent_id='$level2[0]' ORDER BY id");
    while($level3 = mysql_fetch_row($category3))
	{
      echo "Level 3 - $level3[0] $level3[1]<br>";
      $category4 = mysql_query("SELECT id, title FROM category WHERE parent_id='$level3[0]' ORDER BY id");
      while($level4 = mysql_fetch_row($category4))
	  {
	    echo "Level 4 - $level4[0] $level4[1]<br>";
	  }
	}
  }
}
?>

Regards

Asa

[asa_carter]

sorted it....

<? 
include("connect.php");

$category1 = mysql_query("SELECT id, title FROM category WHERE parent_id='00000000' ORDER BY id");
while($level1 = mysql_fetch_row($category1))
{
  $c_check = mysql_query("SELECT id, title FROM category WHERE parent_id='$level1[0]' ORDER BY id");
  $cats = mysql_num_rows($c_check);
  if ($cats >= 1)
    echo "Level 1 - $level1[0] $level1[1] - <b>$cats children</b><br>";
  else
    echo "Level 1 - $level1[0] $level1[1] - <b>No children</b><br>";  

  $category2 = mysql_query("SELECT id, title FROM category WHERE parent_id='$level1[0]' ORDER BY id");
  while($level2 = mysql_fetch_row($category2))
  {
    $c_check = mysql_query("SELECT id, title FROM category WHERE parent_id='$level2[0]' ORDER BY id");
    $cats = mysql_num_rows($c_check);
    if ($cats >= 1)
      echo "Level 2 - $level2[0] $level2[1] - <b>$cats children</b><br>";
    else
      echo "Level 2 - $level2[0] $level2[1] - <b>No children</b><br>";

$category3 = mysql_query("SELECT id, title FROM category WHERE parent_id='$level2[0]' ORDER BY id");
while($level3 = mysql_fetch_row($category3))
{
  $c_check = mysql_query("SELECT id, title FROM category WHERE parent_id='$level3[0]' ORDER BY id");
  $cats = mysql_num_rows($c_check);
  if ($cats >= 1)
    echo "Level 3 - $level3[0] $level3[1] - <b>$cats children</b><br>";
  else
    echo "Level 3 - $level3[0] $level3[1] - <b>No children</b><br>";

  $category4 = mysql_query("SELECT id, title FROM category WHERE parent_id='$level3[0]' ORDER BY id");
  while($level4 = mysql_fetch_row($category4))
  {
    $c_check = mysql_query("SELECT id, title FROM category WHERE parent_id='$level4[0]' ORDER BY id");
    $cats = mysql_num_rows($c_check);
    if ($cats >= 1)
      echo "Level 4 - $level4[0] $level4[1] - <b>$cats children</b><br>";
	else
	  echo "Level 4 - $level4[0] $level4[1] - <b>No children</b><br>";
  }
}
  }
}
?>

[thorpe]

This is pretty poor design, I can see like 8 queries there and all of them are inside of loops. You might want to look into nested sets or at least the adjacent model.