Compare 2 Mysql_Fetch_Array Arrays

[vampke]

Hi guys,

 

I have this simple script that I am struggling with for hours....

I want to show an editable list of submitted checkboxes: a user selects a couple of widgets from a list. When submitted I want to show the same list with the selected checkboxes checked.

Could someone please explain to me why this code works (difference is the location of the selectedwidgets_qry in or out the while loop):

$widget_result = mysql_query("SELECT id, description FROM widget_item") or die(mysql_error());

while ($info = mysql_fetch_array($widget_result)) {
$selected = "";
$selectedwidgetsqry_result=mysql_query("select item_id from selected_widgets where sessionId = '$sessionid'") or die(mysql_error());
while($arr = mysql_fetch_array($selectedwidgetsqry_result)){
 if($info['id'] == $arr['item_id']){
 $selected = " checked=\"checked\"";
 }	
		 }
$out .="<input name=\"items[]\" type=\"checkbox\" value=\"".$info['id']."\" id=\"".$info['id']."\"".$selected." /><label for=\"".$info['id']."\">".$info['description']."</label><br />";
}

 

and why this code does not?

$widget_result = mysql_query("SELECT id, description FROM widget_item") or die(mysql_error());

$selectedwidgetsqry_result=mysql_query("select item_id from selected_widgets where sessionId = '$sessionid'") or die(mysql_error());

while ($info = mysql_fetch_array($widget_result)) {
$selected = "";
while($arr = mysql_fetch_array($selectedwidgetsqry_result)){
 if($info['id'] == $arr['item_id']){
 $selected = " checked=\"checked\"";
 }	
		 }
$out .="<input name=\"items[]\" type=\"checkbox\" value=\"".$info['id']."\" id=\"".$info['id']."\"".$selected." /><label for=\"".$info['id']."\">".$info['description']."</label><br />";
}

 

It took me hours to figure this out but I still don't understand why the difference.

[Psycho]

The problem is that the first while loop pulls the records from the first query. Then you have an inner while loop to go through the results of the second query. So, on the first iteration of the outer loop the second loop is consuming all of the records from the 2nd query. So, on the second iteration of the outer loop there are no records for the inner loop to process.

 

But, to put it bluntly, you are doing it wrong. You should NEVER run queries within loops. In many cases, such as this one, you should be using a JOIN query. This will be much more efficient. The JOIN query below will provide a record set of all possible selections and JOIN the checked options of the user. I used a dynamically created variable "checked" that will be set to a 1 if it was checked by the user, otherwise it will be 0.

 

//Create and run ONE query to get all the checkbox options and which ones
//are checked for the users
$query = "SELECT wi.id, wi.description, IF(wi.id=sw.item_id., 1, 0) AS checked
 FROM widget_item AS wi
 LEFT JOIN selected_widgets AS sw
 ON wi.id = sw.item_id
 AND sw.sessionId = '$sessionid'";
$result = mysql_query($query) or die(mysql_error());

//Process the records into output
while ($row = mysql_fetch_assoc($result))
{
   //Set variable for whether the option is checked or not
   $checked = ($row['checked']) ? ' checked="checked"' : '';
   $out .= "<input name='items[]' type='checkbox' value='{$row['id']}' id='{$row['id']}'{$checked} />";
   $out .= "<label for='{$row['id']}'>{$row['description']}</label><br />\n";
}

Edited October 12, 2012 by Psycho

[vampke]

Psycho,

 

Thank you very much for your reply. I know I should urgently polish my sql skills, it has been a while :/ ...

I got it working with the one query solution (there actually already was a JOIN involved which I skipped because it didn't have anything to do with the problem.

I am still wondering why the resultset is empty after the first iteration. Isn't it just a variable that should keep it's array?

[Pikachu2000]

It isn't really empty, but as mysql_fetch_(anything) iterates over it, the internal data pointer is advanced. After the first iteration of the outer while, the data pointer is at the end of the array used within the inner while. To user the data again, you'd need to reset it with mysql_data_seek.

[Psycho]

It isn't really empty, but as mysql_fetch_(anything) iterates over it, the internal data pointer is advanced. After the first iteration of the outer while, the data pointer is at the end of the array used within the inner while. To user the data again, you'd need to reset it with mysql_data_seek.

 

Yeah, my terminology may have been lacking as the records aren't really "consumed". Thanks for clarifying. And, although you *could* achieve the desired results using mysql_data_seek() I'm sure you aren't proposing that this is a scenario where it would make sense to do so.

Edited October 12, 2012 by Psycho

[Pikachu2000]

No, like you said, a join is a much better way.

[vampke]

Cheers guys,

I learned a lot today