Use a checkbox to update a mysql table

#1

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

image.png1920x1080 30 KB

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

#2

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.

#3

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.

#4

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...

image.png1920x1080 31.3 KB

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

image.png1920x1080 36.7 KB

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

#5

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.

#6

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

#7

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...

image.png1920x1080 40.9 KB

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?

#8

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.

#9

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.

#10

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

#11

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.

#12

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

image.png1920x1080 32.8 KB 

['display'];
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?

#13

my logic was all messed up, its working now.

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

#14

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.

#15

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");
#16

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

$prepare->execute($parameters);

You need to call it against the prepared statement object.

Cannot insert data into a table
closed #17

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