Help with while loop - categories

PHP
1

We are building a form so that we can edit business listings and place them in various categories.

We have 3 tables

  1. Business listing table business2 (has busid as primary key)
  2. Categories table category2 (has cid and category fields)
  3. Master table catmaster that links the above two table so that a business can belong to multiple categories (has busid and cid fields)

We want the form to display the list of all categories with a checkbox beside each. The category(s) that the business belongs to should be checked the others unchecked.

The code below displays all the categories and their unchecked checkboxes but displays the 2 categories that our business belongs to twice, once checked and once unchecked.

Are the while loops in the wrong order? Can anyone help?

 
echo" <tr> 
      <td width=18&#37; valign=top><p>Select a category</p></td>
        <td width=80% valign=top><p>";
//Get the categories values from the category table
$sql22 = "SELECT * FROM category2 ORDER BY category ASC";
$result22 = mysql_query($sql22, $db); 

while ($row22=mysql_fetch_array($result22)) 
{
	$sql22b = "SELECT * FROM catmaster WHERE busid=$busid AND cid=$row22[cid]";
$result22b = mysql_query($sql22b, $db);
	while ($row22b=mysql_fetch_array($result22b)) 
	{
	if ($row22['cid'] == $row22b['cid'])
	{
	echo "<input type=\\"checkbox\\" name=\\"cat[]\\" value=\\"$row22[cid]\\" checked>$row22[category]<br>";
	}
	} //end while2

if ($row22['cid'] != $row22b['cid'])
	{
	echo "<input type=\\"checkbox\\" name=\\"cat[]\\" value=\\"$row22[cid]\\">$row22[category]<br>";
	}
} //end while1
echo"      </p> </td>
    </tr>";
2

You have the second if statement inside the wrong loop.

Try putting it as an ‘else’ after the initial if.

3

doing a query inside a loop is extremely inefficient

better is to return everything you need in a single query

SELECT category2.cid
     , category2.category
     , [COLOR="Red"]CASE WHEN catmaster.cid IS NULL
            THEN 0 ELSE 1 END  AS checked[/COLOR]
  FROM category2 
LEFT OUTER
  JOIN catmaster
    ON catmaster.cid = category2.cid
   AND catmaster.busid = $busid 
ORDER
    BY category2.category

the column “checked” will be 0 or 1 depending on whether $busid has that particular category

:slight_smile:

4

Hey thanks!

This is the modified code and it seems to be working!


$sql22 = "SELECT category2.cid
     , category2.category
     , CASE WHEN catmaster.cid IS NULL
            THEN 0 ELSE 1 END  AS checked
  FROM category2 
LEFT OUTER
  JOIN catmaster
    ON catmaster.cid = category2.cid
   AND catmaster.busid = $busid 
ORDER
    BY category2.category";
$result22 = mysql_query($sql22, $db); 

while ($row22=mysql_fetch_array($result22)) 
{
	if ($row22['checked'] == 1)
	{
	echo "<input type=\\"checkbox\\" name=\\"cat[]\\" value=\\"$row22[cid]\\" checked>$row22[category]<br>";
	}
	else
	{
	echo "<input type=\\"checkbox\\" name=\\"cat[]\\" value=\\"$row22[cid]\\">$row22[category]<br>";
	}
	


} //end while1