Problem with looping

[bugzy]

Hello guys.. I have this loop

 

<?php

$cat_query = "Select cat_id, cat_name, cat_visibility from category where cat_id != 1 order by cat_position DESC";
$cat_result = mysql_query($cat_query,$connection);
$cat_num = mysql_num_rows($cat_result);

$ic = "Select category_id from item_category where item_id = {$edit_id}";
$ic_result = mysql_query($ic,$connection) or die (mysql_error());
$ic_num = mysql_num_rows($ic_result);



for($i = 0; $cat_num > $i; $i++)
	{

		for($c = 0; $ic_num > $c; $c++)
		{
			if(mysql_result($ic_result,$c,'category_id') == mysql_result($cat_result,$i,'cat_id'))
			{

				echo "<input type=\"checkbox\" name=\"item_cat[]\" value=". mysql_result($cat_result,$i,'cat_id') . " checked=\"checked\" />"; 					echo mysql_result($cat_result,$i,'cat_name'). "<br>";
			}
			else
			{

				echo "<input type=\"checkbox\" name=\"item_cat[]\" value=". mysql_result($cat_result,$i,'cat_id') . " />"; 					
				echo  mysql_result($cat_result,$i,'cat_name'). "<br>";
			}



		}



	}

?>

 

 

 

the value I'm getting is being doubled like this

 

 

☑ category1

☐ category1

☐ category2

☐ category2

☑ category3

☐ category3

☐ category4

☐ category4

 

 

I'm getting those categories that need to be check, problem is it doubles every value..

 

Anyone?

[jcbones]

You should really be using a JOIN query.  MySQL is a relational database, and this is where it really shines.

<?php
$sql = "SELECT c.cat_id,c.cat_name,c.cat_visibility FROM category AS c JOIN item_category AS i ON i.category_id = c.cat_id WHERE i.item_id = $edit_id";
$result = mysql_query($sql) or trigger_error($sql . ' has encountered an error.<br />' . mysql_error());
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_assoc($result)) {
   echo "<input type=\"checkbox\" name=\"item_cat[]\" value=\"{$row['cat_id']}\" checked=\"checked\" /> {$row['cat_name']}<br />";
}
}

If that throws an error, please post the code and the error(complete) back here.

[bugzy]

You should really be using a JOIN query.  MySQL is a relational database, and this is where it really shines.

<?php
$sql = "SELECT c.cat_id,c.cat_name,c.cat_visibility FROM category AS c JOIN item_category AS i ON i.category_id = c.cat_id WHERE i.item_id = $edit_id";
$result = mysql_query($sql) or trigger_error($sql . ' has encountered an error.<br />' . mysql_error());
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_assoc($result)) {
   echo "<input type=\"checkbox\" name=\"item_cat[]\" value=\"{$row['cat_id']}\" checked=\"checked\" /> {$row['cat_name']}<br />";
}
}

If that throws an error, please post the code and the error(complete) back here.

 

Hello..

 

It only showing those checkboxes that are "checked" and not those who are also "unchecked"..

 

?

[cpd]

You would need to use a LEFT JOIN else you'll only ever get results when there's a match in both tales. LEFT|RIGHT JOIN will return the data from the LEFT|RIGHT table regardless of whether or not there is a match in the joining table. With large amounts of data a LEFT|RIGHT JOIN can take a long time though, because it uses nested loops.

[bugzy]

Thanks guys... I just use in_array.. instead of the complicated codes above.

 

 

[jcbones]

Or, you could have just changed the query slightly, and add some slight validation.  Staying away from running two queries (reducing overhead), and making it efficient.

<?php
$sql = "SELECT c.cat_id,c.cat_name,c.cat_visibility,i.category_id FROM  item_category AS i LEFT JOIN category AS c ON i.category_id = c.cat_id WHERE i.item_id = $edit_id";
$result = mysql_query($sql) or trigger_error($sql . ' has encountered an error.<br />' . mysql_error());
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_assoc($result)) {
   $checked = ($row['cat_id'] == $row['category_id']) ? 'checked="checked"' : NULL;
   echo "<input type=\"checkbox\" name=\"item_cat[]\" value=\"{$row['cat_id']}\" $checked /> {$row['cat_name']}<br />";
}
}