current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

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>";
}
share|improve this question
 
Please note that mysql_* functions are deprecated, unsafe, and should not be used. Consider using mysqli_* functions or PDO. –  Steven Liao Sep 12 '13 at 7:19
 
Well resuse of $row in two different contexts isn't going to help; use $oldRow and $newRow or similar to make the logic clearer –  Mark Baker Sep 12 '13 at 7:25
 
dot re-use $row, try while($row = mysql_fetch_array($result_select)) foreach($row as $row1){ instead of while($row = mysql_fetch_array($result_select)) $rows[] = $row; foreach($rows as $row){ –  Deepanshu Sep 12 '13 at 7:25
 
In your inner while loop, you're not assigning the result of mysql_fetch_row($resultnew) to anything. The next line then uses $resultnew as if it were the returned row. –  Barmar Sep 12 '13 at 7:37
 
You shouldn't use nested queries at all, you should do a LEFT JOIN between the categories and subcategories table, to get everything in one query. –  Barmar Sep 12 '13 at 7:38
show 1 more comment

1 Answer

up vote 0 down vote accepted

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
share|improve this answer
 
Thanks a lot dude, this worked for me..!!! –  LuFFy Sep 12 '13 at 8:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.