[SOLVED] Updating an Array of Checkboxes (MySQL)

[lepage71]

I'm trying to do something that (I think) should be relatively simple.

 

In an "admin page" on a website, I have created an HTML table that displays data from a MySQL database. The table contains a form that has textareas prepopulated with data from MySQL.

 

The code looks like this (I cut out the label rows and a few other unimportant things):

 

$result = mysql_query ("SELECT ID, Driver, Password, Status, Active, Debut FROM Drivers ORDER BY Driver ASC LIMIT $offset, $display"); 

.
.
.


while ($row = mysql_fetch_array ($result))
	{
	echo "<tr>";
	echo "<td align='center'><strong>"; $id[] = $row ['ID']; echo "" . $row ['ID'] . "</strong></td>";
	echo "<td align='center'><input type='text' name='Driver[]' id='Driver' value='" . $row ['Driver'] . "' class='text'></td>";
	echo "<td align='center'><input type='text' name='Password[]' id='Password' value='" . $row ['Password'] . "' class='text'></td>";
	echo "<td align='center'><input type='text' name='Status[]' id='Status' value='" . $row ['Status'] . "' class='text' ></td>";
	echo "<td align='center'><input type='text' name='Active[]' id='Active' value='" . $row ['Active'] . "' class='text' ></td>";
	echo "<td align='center'><input type='text' name='Debut[]' id='Debut' value='" . $row ['Debut'] . "' class='text' ></td>";
	echo "<td><a href='resources/delete.php?id=" . $row ['ID'] . "'>Delete</a></td>";
	echo "</tr>";
	}
echo "</table>
</td>
</tr>
<tr><td height='24'></td></tr>
<tr><td align='center'><input type='submit' name='submit' value='Save Changes' class='button' id='save'></td></tr>
</form>
</table>";

 

Since textareas are output, this table acts like an editable grid. Pressing "submit" updates the data in MySQL. The MySQL update code looks like this:

 

if ($submit) 
{
for ($i = 0; $i < $count; $i++) 
	{
	$update = mysql_query ("UPDATE Drivers SET Driver = '$Driver[$i]', Password = '$Password[$i]', Status = '$Status[$i]', Active = '$Active[$i]', Debut = '$Debut[$i]' WHERE ID = '$id[$i]'");
	}
}

 

 

Simple enough - the table works perfectly. What I want to do, however, is change the "Active" column from an array of textareas into an array of checkboxes. The reason for this is simple: "Active" can only have two possible values, 1 (default) or 0.

 

I'd prefer NOT to have a column of data that just repeats 1s and 0s, instead checkboxes representing 1s and 0s would be much more convenient. "Checked" would equal 1 and "unchecked" would equal 0. When you press submit, the form updates MySQL with 1s and 0s depending on what rows are checked or not checked.

 

I have tried replacing

 

echo "<td align='center'><input type='text' name='Active[]' id='Active' value='" . $row ['Active'] . "' class='text' ></td>";

 

with

 

		if ($row ['Active'] == "1") {$checked = "checked";} else {$checked = "";}

	echo "<td><input type='checkbox' name='Active' value='" . $row ['Active'] . "' $checked></td>";

 

but I know I'm missing something here.

 

Is there a way to assign, on this form, checked = 1 and unchecked = 0, and then pressing submit updates the rows accordingly? If anyone has an idea, I would appreciate any advice you could offer. Thanks in advance

[rhodesa]

your form code looks good...just get rid of the value part (i also shortened up the IF):

$checked = ($row ['Active']) "checked";} else {$checked = "";}
echo "<td><input type='checkbox' name='Active' $checked /></td>";

 

then in your PHP:

$update = mysql_query ("UPDATE Drivers SET Driver = '$Driver[$i]', Password = '$Password[$i]', Status = '$Status[$i]', Active = '".($Active[$i]?1:0)."', Debut = '$Debut[$i]' WHERE ID = '$id[$i]'");

[lepage71]

There was some sort of error in your modified IF statement, so I kept what I had but deleted the "value" part. I changed my update statement to what you posted as well. I am still having some issues, however.

 

I ran into similar problems earlier, where the entire grid was getting messed up after trying to save changes by pressing submit. This is what happens when I try to make a modification...

 

Grid in textarea view, so you can see which rows hold which value (1 or 0). Just to note, the "Status" and "Active" columns ARE separate fields in MySQL, even though being banned happens to be correlated with inactiveness. 

 

 

INITIAL grid in the desired checkbox view:

 

 

What happens after I try unchecking the checkbox of the first row, the player "2Quick4u", and then pressing the submit button (button not pictured but it is below the table):

 

 

Do I need to change my IF statement to what you posted for this to work properly? I think you made a typo somewhere, but I'm not sure.

[rhodesa]

i did make a typo...it was supposed to be:

$checked = ($row ['Active']) ? "checked" : "";

 

...but that isn't the problem....

 

wasn't thinking right when i posted. checkboxes are unique in that they don't get passed if they aren't checked. so, change your form code to have the IDs added and then the PHP to check the IDs (confusing, but i think it will be obvious in the code):

 

	while ($row = mysql_fetch_array ($result))
	{
	// $id = $row ['ID']; If you don't need this, get rid of it
	$i = $row ['ID'];
	$checked = ($row['Active']) ? "checked" : "";
	echo "<tr>";
	echo "<td align='center'><strong>{$row ['ID']}</strong></td>";
	echo "<td align='center'><input type='text' name='Driver[{$id}]' value='{$row['Driver']}' class='text'></td>";
	echo "<td align='center'><input type='text' name='Password[{$id}]' value='{$row['Password']}' class='text'></td>";
	echo "<td align='center'><input type='text' name='Status[{$id}]' value='{$row['Status']}' class='text' ></td>";
	echo "<td align='center'><input type='checkbox' name='Active[{$i}]' {$checked} /></td>";
	echo "<td align='center'><input type='text' name='Debut[{$id}]' value='{$row['Debut']}' class='text' ></td>";
	echo "<td><a href='resources/delete.php?id=" . $row ['ID'] . "'>Delete</a></td>";
	echo "</tr>";
	}

 

if ($submit) 
{
foreach(array_keys($Driver) as $i)) 
	{
$update = mysql_query ("UPDATE Drivers SET Driver = '{$Driver[$i]}', Password = '{$Password[$i]}', Status = '{$Status[$i]}', Active = '".($Active[$i]?1:0)."', Debut = '{$Debut[$i]}' WHERE ID = '{$i}'");
	}
}

[lepage71]

Perfect...thanks.  One thing though...I had to make both $i and $id defined or my table goes crazy. I can't figure out exactly what happens, but I end up with random results: duplicated driver names, some active boxes checked, some boxes unchecked.

[rhodesa]

he...oops...that is what i get for copy/paste

 

	while ($row = mysql_fetch_array ($result))
	{
	$i = $row ['ID'];
	$checked = ($row['Active']) ? "checked" : "";
	echo "<tr>";
	echo "<td align='center'><strong>{$row ['ID']}</strong></td>";
	echo "<td align='center'><input type='text' name='Driver[{$i}]' value='{$row['Driver']}' class='text'></td>";
	echo "<td align='center'><input type='text' name='Password[{$i}]' value='{$row['Password']}' class='text'></td>";
	echo "<td align='center'><input type='text' name='Status[{$i}]' value='{$row['Status']}' class='text' ></td>";
	echo "<td align='center'><input type='checkbox' name='Active[{$i}]' {$checked} /></td>";
	echo "<td align='center'><input type='text' name='Debut[{$i}]' value='{$row['Debut']}' class='text' ></td>";
	echo "<td><a href='resources/delete.php?id={$row['ID']}'>Delete</a></td>";
	echo "</tr>";
	}

[lepage71]

No wonder...if I knew what I was doing I'd have noticed.