嵌套While While with mysql_fetch_array给出空白结果

Question

I have used following code, Please solve any error if exist

I am trying to catch Category name from category table using parentID from Subcategory table

here is the code

$query_select = "SELECT * FROM subcategory where parentID<>0 and isDisabled=0 and isDeleted=0";
$result_select = mysql_query($query_select) or die(mysql_error());
$rows = array();
while($row = mysql_fetch_array($result_select))
  $rows[] = $row;

foreach($rows as $row){ 
  $id = $row['subcatID'];
  $subname = $row['subcatName'];
  $parentID= $row['parentID'];
  $status = $row['isDisabled'];
  $catname="";
  echo "<tr><td style='display:none;'>$id</td>";
  echo "<td><center>$subname</center></td>";
  $resultnew=mysql_query("select catName from category where catID=$parentID");

  while(mysql_fetch_row($resultnew)){
    $catname = $resultnew[0];
    echo "<script>alert($catname);</script>";
    echo "<td class='center'><center>$catname</center></td>";
  }
  if($status==0)
    {
      echo "<td class='center'><center><span class='label label-success'>Active</span></center></td>";
    }else{
    echo "<td class='center'><center><span class='label label-important'>Disabled</span></center></td>";
  }
  echo "<td class='center'><center>";
  echo "<a class='btn btn-success' href='#'><i class='icon-zoom-in icon-white'></i>View</a>";
  echo "<a class='btn btn-info' href='#'><i class='icon-edit icon-white'></i>Edit</a>";
  echo "<a class='btn btn-danger' href='#'><i class='icon-trash icon-white'></i>Delete</a>";
  echo "</center></td>";
  echo "</tr>";
}

Answer 1

Change:

  while(mysql_fetch_row($resultnew)){
    $catname = $resultnew[0];
    echo "<script>alert($catname);</script>";
    echo "<td class='center'><center>select catName from category where catID=$parentID $catname</center></td>";
  }

to:

  while($newrow = mysql_fetch_row($resultnew)){
    $catname = $newrow[0];
    echo "<script>alert($catname);</script>";
    echo "<td class='center'><center>select catName from category where catID=$parentID $catname</center></td>";
  }

However, I presume catID is a unique key, so this query can only return one row, so there's no need for a while loop at all. Just write:

if ($newrow = mysql_fetch_row($resultnew)) {
    $catname = $newrow[0];
    echo "<script>alert($catname);</script>";
    echo "<td class='center'><center>select catName from category where catID=$parentID $catname</center></td>";
  }

But you can also combine this with your main query, so you don't need to do multiple queries:

SELECT s.subcatID, s.subcatName, s.parentID, s.isDisabled, c.catName
FROM subcategory s
LEFT JOIN category c ON c.catID = s.parentID
WHERE s.parentID <> 0 and s.isDisabled = 0 and s.isDeleted = 0