Use a checkbox to update a mysql table

lurtnowski · July 10, 2018, 10:07pm

I display a form which shows every record from my mysql table

I’m trying to turn the display field for the record to either 1 or 0 (depending on if the box is checked or not)
I got the form to display

	while($row = $stmt->fetch()) {
		
		switch($row['display']) {
		
		case 1:
			$display = 'checked';
			break;
		default:
			$display = '';
			break;
		}

	  echo '<tr>';
	  echo "<td>".$row['id']."</td>";
	  echo '<td>'.$row['userName'].' ('.$row['userID'].')</td>';
	  echo '<td><input type="hidden" name="userID['.$row['id'].']" value="'.$row['id'].'"><input type="checkbox" name="display['.$row['id'].']" value="'.$row['display'].'" '.$display.'></td>';
	  echo '</tr>';
	  echo "\r\n";

But how do I make it so that once the form is submitted, i only change the changed display property (1 or 0) in the mysql table?

Thanks

chorn · July 11, 2018, 6:42am

The row must have an ID that corresponds to the row within the database, so you can use a WHERE clause on your UPDATE-query.

droopsnoot · July 11, 2018, 8:58am

Have a look at the contents of your $_POST array, in particular the names of the checkboxes that are included. If I’m reading your HTML correctly, you’ve named them using the ID field, so you should be able to iterate through them using foreach() to update your database.

I don’t see the point in your hidden variable, as that just duplicates things, by using the ID as both the array index and the value. You could do something like that to provide the original value of the checkbox, so you can see whether the database needs to be updated for each entry.

lurtnowski · July 11, 2018, 10:19pm

ok, glad to get rid of anything I don’t need
so, heres how I use PHP to generate the form,

	while($row = $stmt->fetch()) {
		
		switch($row['display']) {
		
		case 1:
			$display = 'checked';
			break;
		default:
			$display = '';
			break;
		}

	  echo '<tr>';
	  echo "<td>".$row['id']."</td>";
	  echo '<td>'.$row['userName'].' ('.$row['userID'].')</td>';
	  echo '<td><input type="checkbox" name="display['.$row['id'].']" value="'.$row['display'].'" '.$display.'></td>';
	  echo '</tr>';
	  echo "\r\n";
	}

the result…

HTML

<form action="manage_properties.php" method="post">
<table cellspacing=10>
<caption><h2>Select any property to disable/enable</h2></caption>
<thead>
<tr>
<th>PropertyID</th>
<th>Owner (ID)</th>
<th>Disable</th>
</tr>
</thead> 
<tbody>
<tr><td>1</td><td> ()</td><td><input type="checkbox" name="display[1]" value="1" checked></td></tr>
<tr><td>2</td><td> ()</td><td><input type="checkbox" name="display[2]" value="1" checked></td></tr>
<tr><td>3</td><td> ()</td><td><input type="checkbox" name="display[3]" value="0" ></td></tr>
<tr><td>4</td><td> ()</td><td><input type="checkbox" name="display[4]" value="0" ></td></tr>
<tr><td>5</td><td> ()</td><td><input type="checkbox" name="display[5]" value="1" checked></td></tr>
<tr><td>6</td><td> ()</td><td><input type="checkbox" name="display[6]" value="1" checked></td></tr>
</tbody>
<tfoot>
<tr><td>
6</td>
<td colspan="2">
<input type="submit" value="Submit">
<input type="reset">
</td></tr>
</tfoot>
</table>

</form>

then, heres the page its submitted to

The PHP

$display = $_POST['display'];
echo '<pre>';print_r($display);echo '</pre>';  

foreach ( $display as $key => $value ) {
	$sql = "UPDATE providers SET display = :display WHERE id = :id";

    $prepare = $conn->prepare($sql); // Prepare the SQL Statement
  
    $parameters = array(
	  ':display' => $key,
	  ':id' => $value
	);

  echo '<pre>';print_r($parameters);echo '</pre>';
  echo 'SQL:'.$sql;

If the checkbox isn’t selected, im trying to make it be added to the array as a 0 though
It seems only the checked ones are being used though

Mittineague · July 11, 2018, 10:32pm

That’s how they are meant to work.

Note: If a checkbox is unchecked when its form is submitted, there is no value submitted to the server to represent its unchecked state (e.g. value=unchecked); the value is not submitted to the server at all.

lurtnowski · July 12, 2018, 9:28pm

oh dang, didn’t think about that, I guess a select box would be best then

lurtnowski · July 12, 2018, 10:47pm

k, I changed it to select boxes (yes/no) in order to display the property
echo ‘No’;

heres the source…

<form action="manage_properties.php" method="post">
<table cellspacing=10>
<caption><h2>Select any property to disable/enable</h2></caption>
<thead>
<tr>
<th>PropertyID</th>
<th>Owner (ID)</th>
<th>Display</th>
</tr>
</thead> 
<tbody>
<tr><td>1</td><td> ()</td><td> <select name="display[1]"><option>Yes</option><option value="0">No</option><option value="1">Yes</option></select></td></tr>
<tr><td>2</td><td> ()</td><td> <select name="display[2]"><option>Yes</option><option value="0">No</option><option value="1">Yes</option></select></td></tr>
<tr><td>3</td><td> ()</td><td> <select name="display[3]"><option>No</option><option value="0">No</option><option value="1">Yes</option></select></td></tr>
<tr><td>4</td><td> ()</td><td> <select name="display[4]"><option>No</option><option value="0">No</option><option value="1">Yes</option></select></td></tr>
<tr><td>5</td><td> ()</td><td> <select name="display[5]"><option>Yes</option><option value="0">No</option><option value="1">Yes</option></select></td></tr>
<tr><td>6</td><td> ()</td><td> <select name="display[6]"><option>Yes</option><option value="0">No</option><option value="1">Yes</option></select></td></tr>
</tbody>
<tfoot>
<tr><td>
6</td>
<td colspan="2">
<input type="submit" value="Submit">
<input type="reset">
</td></tr>
</tfoot>
</table>

</form>

But when I submit the form to

$display = $_POST['display'];
echo '<pre>';print_r($display);echo '</pre>';  

foreach ( $display as $key => $value ) {
	$sql = "UPDATE providers SET display = :display WHERE id = :id";

    $prepare = $conn->prepare($sql); // Prepare the SQL Statement
  
    $parameters = array(
	  ':display' => $key,
	  ':id' => $value
	);

  echo '<pre>';print_r($parameters);echo '</pre>';
  echo 'SQL:'.$sql;

  }

the result…

Why after only changing display[3] and display[4] to Yes do all 6 records appear in the array (instead of only 2) Wouldn’t it be best to only run the UPDATE query 2 times instead of six?
If the option has no value how is Yes recorded?

ahundiak · July 13, 2018, 12:03am

Checkboxes will only post if they are selected. Which is annoying sometimes. By adding a hidden element with the same name and an unchecked value, you ensure that something is always posted. Can save you from a bunch of issets.

Mittineague · July 13, 2018, 1:41am

Something like this could work

<input type="hidden" name="option_count" value="6">

Though unlikely, that value could be changed client-side and cause issues. IMHO, it would be better if the same script that populates the values saves the information server-side.

chorn · July 13, 2018, 5:51am

What is a nice convenience feature for clients, but at least you always have to validate null values serverside.

droopsnoot · July 13, 2018, 9:11am

Yes, but you would have to code that yourself as I mentioned earlier.

What I meant by that is to use your hidden variable to contain the original value, then when you get the $_POST array, you can check to see what has changed.

If you don’t specify a value in your <option> tag, the display string is sent through as the value. On each of your <select> tags, you have the first option that contains either No or Yes, with no specific value set, so if that selection isn’t changed, it will send the string. If these are coming from a database in the real code, it might be better to set the selected flag on the appropriate option, rather than adding another one.

In your foreach() loop to run the query, I’d have the prepare() before the loop is executed, then just call it each time. I doubt it will make any difference to perceived execution time, but one of the features of prepared statements is the ability to prepare it once and then just keep calling it with new parameters so it seems “right”.

Yes, sorry, I’d forgotten that.

lurtnowski · July 13, 2018, 9:15pm

ok, I did that, so now when the form is submitter I have two arrays (original & display). Tried the check to see if the original value was changed.
The result

echo '<pre>';print_r($display);echo '</pre>';  
$original = $_POST['original'];
echo '<pre>';print_r($original);echo '</pre>';  


foreach ( $display as $key => $value ) {
	
  if($display[$value] != $original[$value]) {
	
	$sql = "UPDATE providers SET display = :display WHERE id = :id";

    $prepare = $conn->prepare($sql); // Prepare the SQL Statement
  
    $parameters = array(
	  ':display' => $value,
	  ':id' => $key
	);

  echo '<pre>';print_r($parameters);echo '</pre>';
  echo 'SQL:'.$sql;

  }

why isn’t the comparison working?

lurtnowski · July 13, 2018, 11:08pm

my logic was all messed up, its working now.

I can prepare() the query b4 efore the foreach() and execute() in the loop?

droopsnoot · July 14, 2018, 2:19pm

Yes. Call the prepare before the loop and just pass your array in to execute in each iteration of the loop. I usually use bindParam() rather than passing an array, so I would put both of those statements before the loop, then just set the correct values within the loop and call execute.

lurtnowski · July 14, 2018, 9:09pm

ok, maybe im not using the execute() correctly then (but im not getting an error)

when I make a change (turn a 0 to a 1) no change happens and the form is reset (so the header() works

$sql = "UPDATE providers SET display = :display WHERE id = :id";

$prepare = $conn->prepare($sql); // Prepare the SQL Statement

foreach ( $display as $key => $value ) {
	
  if($display[$key] != $original[$key ]) {
	

  
    $parameters = array(
	  ':display' => $value,
	  ':id' => $key
	);

	execute($parameters);
	
	}
}

header("location: manage_properties.php");

droopsnoot · July 15, 2018, 5:48pm

Surely it would be like any other time you call execute:

$prepare->execute($parameters);

You need to call it against the prepared statement object.

system · October 15, 2018, 12:48am

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Checkbox needs to reflect the database value PHP	9	3291	July 20, 2017
Gettng checkbox values PHP	3	368	March 20, 2011
Updating checkbox PHP	2	498	January 2, 2010
Display checkboxes from mysql table rows PHP	5	5520	August 2, 2017
Need help with checkboxes (php/mysql) PHP	6	1990	February 21, 2010

Use a checkbox to update a mysql table

Related topics