[RESOLVED] looping query in table with checkboxes

[matthewst]

My database is structured like this:
rest_id--rest_name--city--state--zip--ad_space1--ad_space2--ad_space3
1---------test1-------L.A.---CA---90210--1------------1---------------------
2---------test2-------N.Y.---NY---12345--------------1------------1---------
3---------test3-----Boston--MA--54321--1------------------------1---------
4---------test4-------N.Y.---NY---12345--1------------1---------------------

Right now I am able to query the database for an individual city and see the first entry matching the criteria (if I query NY I only get record 2). I have the results echoing in a table. If ad_space = "_" then the cell has a checkbox to change it in the database to a "1". All that is great and exactly what I need to happen.

The problem:
I need to be able to echo more than one at a time. How do I do that and still keep them straight?

Example:
If I query LA my table looks like this;
ad_1-------ad_2-------ad_3-----
test1=1---test1=1----test1=_ []

If I query NY my table looks like this;
ad_1-------ad_2-------ad_3-----
test2=_[]---test2=1----test2=1

Then I can tic the box and click submit and everything works. How do I make it echo multiple records(per cell) and still work?

Example:
Query NY
ad_1-------ad_2-------ad_3-----
test2=[]---test2=1----test2=1
test4=1-----test4=1----test4=[]

[matthewst]

session_start();
echo "<form name='mark_sold' action='mark_sold.php' enctype='multipart/form-data' method='post'><table width='1100' border='1'>";
$Get_City = mysql_query("SELECT * FROM restaurant_info WHERE state = '$state1' AND city = '$city'") or die(mysql_error());
while ($got_city = mysql_fetch_array($Get_City)) {
	$rest_id = $got_city['rest_id'];
	$rest_name = $got_city['rest_name'];
	$city = $got_city['city'];
	$state = $got_city['state'];
	$ad1 = $got_city['ad_space1'];
	$ad2 = $got_city['ad_space2'];
	$ad3 = $got_city['ad_space3'];
	$ad4 = $got_city['ad_space4'];
	$ad5 = $got_city['ad_space5'];
	$ad6 = $got_city['ad_space6'];
	$ad7 = $got_city['ad_space7'];
	$ad8 = $got_city['ad_space8'];
	$ad9 = $got_city['ad_space9'];
	$ad10 = $got_city['ad_space10'];
	$ad11 = $got_city['ad_space11'];
	$ad12 = $got_city['ad_space12'];
	$ad13 = $got_city['ad_space13'];
	$ad14 = $got_city['ad_space14'];
	$ad15 = $got_city['ad_space15'];
	$ad16 = $got_city['ad_space16'];
	$ad17 = $got_city['ad_space17'];
	$ad18 = $got_city['ad_space18'];
	$ad19 = $got_city['ad_space19'];
	$ad20 = $got_city['ad_space20'];
	$ad21 = $got_city['ad_space21'];
	$ad22 = $got_city['ad_space22'];
	$ad23 = $got_city['ad_space23'];
	$ad24 = $got_city['ad_space24'];
	$ad25 = $got_city['ad_space25'];
	}

if($ad1 == '1'){
$is_sold1 = "<font color='red'>sold</font>";
}else{
$is_sold1 = "<font color='green'>available</font><input name='ad1' id='ad1' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<tr><td><h2>Spot 1</h2>" .$rest_name." in ".$city." is ".$is_sold1."<br />";
echo "</td>";

if($ad2 == '1'){
$is_sold2 = "<font color='red'>sold</font>";
}else{
$is_sold2 = "<font color='green'>available</font><input name='ad2' id='ad2' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 2</h2>" .$rest_name." in ".$city." is ".$is_sold2."<br />";
echo "</td>";

if($ad3 == '1'){
$is_sold3 = "<font color='red'>sold</font>";
}else{
$is_sold3 = "<font color='green'>available</font><input name='ad3' id='ad3' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 3</h2>" .$rest_name." in ".$city." is ".$is_sold3."<br />";
echo "</td>";

if($ad4 == '1'){
$is_sold4 = "<font color='red'>sold</font>";
}else{
$is_sold4 = "<font color='green'>available</font><input name='ad4' id='ad4' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 4</h2>" .$rest_name." in ".$city." is ".$is_sold4."<br />";
echo "</td>";

if($ad5 == '1'){
$is_sold5 = "<font color='red'>sold</font>";
}else{
$is_sold5 = "<font color='green'>available</font><input name='ad5' id='ad5' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 5</h2>" .$rest_name." in ".$city." is ".$is_sold5."<br />";
echo "</td></tr>";

if($ad6 == '1'){
$is_sold6 = "<font color='red'>sold</font>";
}else{
$is_sold6 = "<font color='green'>available</font><input name='ad6' id='ad6' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<tr><td><h2>Spot 6</h2>" .$rest_name." in ".$city." is ".$is_sold6."<br />";
echo "</td>";

if($ad7 == '1'){
$is_sold7 = "<font color='red'>sold</font>";
}else{
$is_sold7 = "<font color='green'>available</font><input name='ad7' id='ad7' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 7</h2>" .$rest_name." in ".$city." is ".$is_sold7."<br />";
echo "</td>";

if($ad8 == '1'){
$is_sold8 = "<font color='red'>sold</font>";
}else{
$is_sold8 = "<font color='green'>available</font><input name='ad8' id='ad8' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 8</h2>" .$rest_name." in ".$city." is ".$is_sold8."<br />";
echo "</td>";

if($ad9 == '1'){
$is_sold9 = "<font color='red'>sold</font>";
}else{
$is_sold9 = "<font color='green'>available</font><input name='ad9' id='ad9' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 9</h2>" .$rest_name." in ".$city." is ".$is_sold9."<br />";
echo "</td>";

if($ad10 == '1'){
$is_sold10 = "<font color='red'>sold</font>";
}else{
$is_sold10 = "<font color='green'>available</font><input name='ad10' id='ad10' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 10</h2>" .$rest_name." in ".$city." is ".$is_sold10."<br />";
echo "</td></tr>";

if($ad11 == '1'){
$is_sold11 = "<font color='red'>sold</font>";
}else{
$is_sold11 = "<font color='green'>available</font><input name='ad11' id='ad11' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<tr><td><h2>Spot 11</h2>" .$rest_name." in ".$city." is ".$is_sold11."<br />";
echo "</td>";

if($ad12 == '1'){
$is_sold12 = "<font color='red'>sold</font>";
}else{
$is_sold12 = "<font color='green'>available</font><input name='ad12' id='ad12' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 12</h2>" .$rest_name." in ".$city." is ".$is_sold12."<br />";
echo "</td>";

if($ad13 == '1'){
$is_sold13 = "<font color='red'>sold</font>";
}else{
$is_sold13 = "<font color='green'>available</font><input name='ad13' id='ad13' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 13</h2>" .$rest_name." in ".$city." is ".$is_sold13."<br />";
echo "</td>";

if($ad14 == '1'){
$is_sold14 = "<font color='red'>sold</font>";
}else{
$is_sold14 = "<font color='green'>available</font><input name='ad14' id='ad14' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 14</h2>" .$rest_name." in ".$city." is ".$is_sold14."<br />";
echo "</td>";

if($ad15 == '1'){
$is_sold15 = "<font color='red'>sold</font>";
}else{
$is_sold15 = "<font color='green'>available</font><input name='ad15' id='ad15' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 15</h2>" .$rest_name." in ".$city." is ".$is_sold15."<br />";
echo "</td></tr>";

if($ad16 == '1'){
$is_sold16 = "<font color='red'>sold</font>";
}else{
$is_sold16 = "<font color='green'>available</font><input name='ad16' id='ad16' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<tr><td><h2>Spot 16</h2>" .$rest_name." in ".$city." is ".$is_sold16."<br />";
echo "</td>";

if($ad17 == '1'){
$is_sold17 = "<font color='red'>sold</font>";
}else{
$is_sold17 = "<font color='green'>available</font><input name='ad17' id='ad17' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 17</h2>" .$rest_name." in ".$city." is ".$is_sold17."<br />";
echo "</td>";

if($ad18 == '1'){
$is_sold18 = "<font color='red'>sold</font>";
}else{
$is_sold18 = "<font color='green'>available</font><input name='ad18' id='ad18' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 18</h2>" .$rest_name." in ".$city." is ".$is_sold18."<br />";
echo "</td>";

if($ad19 == '1'){
$is_sold19 = "<font color='red'>sold</font>";
}else{
$is_sold19 = "<font color='green'>available</font><input name='ad19' id='ad19' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 19</h2>" .$rest_name." in ".$city." is ".$is_sold19."<br />";
echo "</td>";

if($ad20 == '1'){
$is_sold20 = "<font color='red'>sold</font>";
}else{
$is_sold20 = "<font color='green'>available</font><input name='ad20' id='ad20' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 20</h2>" .$rest_name." in ".$city." is ".$is_sold20."<br />";
echo "</td></tr>";

if($ad21 == '1'){
$is_sold21 = "<font color='red'>sold</font>";
}else{
$is_sold21 = "<font color='green'>available</font><input name='ad21' id='ad21' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "</tr><td><h2>Spot 21</h2>" .$rest_name." in ".$city." is ".$is_sold21."<br />";
echo "</td>";

if($ad22 == '1'){
$is_sold22 = "<font color='red'>sold</font>";
}else{
$is_sold22 = "<font color='green'>available</font><input name='ad22' id='ad22' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 22</h2>" .$rest_name." in ".$city." is ".$is_sold22."<br />";
echo "</td>";

if($ad23 == '1'){
$is_sold23 = "<font color='red'>sold</font>";
}else{
$is_sold23 = "<font color='green'>available</font><input name='ad23' id='ad23' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 23</h2>" .$rest_name." in ".$city." is ".$is_sold23."<br />";
echo "</td>";

if($ad24 == '1'){
$is_sold24 = "<font color='red'>sold</font>";
}else{
$is_sold24 = "<font color='green'>available</font><input name='ad24' id='ad24' type='checkbox' value='1'/><input name='rest_id' type='hidden' value='" .$rest_id."' />";
}
echo "<td><h2>Spot 24</h2>" .$rest_name." in ".$city." is ".$is_sold24."<br />";
echo "</td>";

//removed 25 so it would post

echo "<tr><td colspan='5'><center><input type='submit' name='submit' value='Submit'></center></td></tr></table></form>";
mysql_close();		
?>

[bradgrafelman]

Ouch.. 25 "ad_space" columns? You really need to find an article that talks about database normalization! :o Normalizing your DB schema even just a little would help simplify your code a lot. For example, I would get rid of all of your ad_spaceXX columns and add a separate ad_space table that has two columns: rest_id, space. Thus, for test1, you'd have two rows in the table: (1, 1) and (1, 2) based on the example output you posted above.

Your main problem is that you can't have multiple form elements that share the same name. Rather than trying to use a hidden form element, put the restaurant ID right into the checkbox name and make the checkbox fields submit as an array, e.g.:

echo "<input type='checkbox' name='ad1[$rest_id]' value='1'>";

You could also reduce those 25 sets of if() blocks down to one block inside of a loop.

[matthewst]

Yeah, I'm doing things the hard way cause I'm still learning. The idea about putting the id in the checkbox name is great! Thank you!
One more question; how would I "extract" that in order to update my database?

mysql_query("UPDATE restaurant_info SET ad_space1='$ad1', ad_space2='$ad2' WHERE rest_id='$rest_id'") or die(mysql_error());

[matthewst]

When I echo $ad1 and $rest_id on my mark_sold.php (database updating page) page I get:
Array
37 (or whatever the proper rest_id is

What am I doing wrong?

[matthewst]

Changed some things around. Still can't get $rest_id and which ad boxes have been checked to update to my database. Any help is greatly appreciated.

$selected_cities = implode("' or '", array_unique($names));
echo "<form name='mark_sold' action='mark_sold.php' enctype='multipart/form-data' method='post'><table width='1100' border='1'><tr><td><h2>Spot 1</h2>";

$Get_City1 = mysql_query("SELECT rest_id, rest_name, city, ad_space1 FROM restaurant_info WHERE state = '$state1' AND city = ('$selected_cities') ORDER BY rest_id");
while ($got_city1 = mysql_fetch_array($Get_City1)) {
	$rest_id = $got_city1['rest_id'];
	$rest_name = $got_city1['rest_name'];
	$city = $got_city1['city'];
	$ad1 = $got_city1['ad_space1'];
	if($ad1 == '1'){
	$is_sold1 = "<font color='red'>sold</font>";
	}else{
	$is_sold1 = "<font color='green'>available</font><input name='ad1[$rest_id]' id='ad1' type='checkbox' value='1[$rest_id]'/>";
	}
	echo $rest_name." in ".$city." is ".$is_sold1."<br />";
	}
	echo "</td><td><h2>Spot 2</h2>";

$Get_City2 = mysql_query("SELECT rest_id, rest_name, city, ad_space2 FROM restaurant_info WHERE state = '$state1' AND city = ('$selected_cities') ORDER BY rest_id");
while ($got_city2 = mysql_fetch_array($Get_City2)) {
	$rest_id = $got_city2['rest_id'];
	$rest_name = $got_city2['rest_name'];
	$city = $got_city2['city'];
	$ad2 = $got_city2['ad_space2'];
	if($ad2 == '1'){
	$is_sold2 = "<font color='red'>sold</font>";
	}else{
	$is_sold2 = "<font color='green'>available</font><input name='ad2[$rest_id]' id='ad2' type='checkbox' value='1[$rest_id]'/>";
	}
	echo $rest_name." in ".$city." is ".$is_sold2."<br />";
	}
	echo "</td><td><h2>Spot 3</h2>";

$Get_City3 = mysql_query("SELECT rest_id, rest_name, city, ad_space3 FROM restaurant_info WHERE state = '$state1' AND city = ('$selected_cities') ORDER BY rest_id");
while ($got_city3 = mysql_fetch_array($Get_City3)) {
	$rest_id = $got_city3['rest_id'];
	$rest_name = $got_city3['rest_name'];
	$city = $got_city3['city'];
	$ad3 = $got_city3['ad_space2'];
	if($ad3 == '1'){
	$is_sold3 = "<font color='red'>sold</font>";
	}else{
	$is_sold3 = "<font color='green'>available</font><input name='ad3[$rest_id]' id='ad3' type='checkbox' value='$rest_id'/>";
	}
	echo $rest_name." in ".$city." is ".$is_sold3."<br />";
	}
	echo "</td>";

echo "<tr><td colspan='5'><center><input type='submit' name='submit' value='Submit'></center></td></tr></table></form>";
mysql_close();		
?>

With this code when I view the source through firefox I can see that the names of my checkboxes are coinciding with the restaurant ids "<input name='ad1[37]'"(which is AWESOME!). My problem now is how do I use that to update my database?

My mark_sold.php page consists mainly of this:

mysql_query("UPDATE restaurant_info SET ad_space1='$ad1', ad_space2='$ad2' WHERE rest_id='$rest_id'") or die(mysql_error());

I imagine I need to do something array() related but I have no idea what.

[bradgrafelman]

You can get the key/index of an array along with its value if you use this form of a foreach() loop:

foreach($array as $key => $value)

Again, though, you could really simplify a lot of this code by a) normalizing your DB schema, and b) generate and process the similar form entries using loops (meaning instead of 25 sets of if() blocks and whatnot, you'd have one set inside of a loop... after all, what's different about ad_space #1 than #25, other than the position/id?).