Best way to check checkboxes from mysql database

PHP
1

I’m not sure if I’m doing this the best practice way or not, it is working but I wasn’t sure if I did it right. Anyway, as background information, let’s say I have a database with one table that stores two pieces of information. The first is a varchar(255) value that stores a name. The second is a char(1) value that stores either ‘Y’ or ‘N’ to represent yes or no respectively.

I want to edit this data with a php generated form so I create a form like so (assuming I’ve already connected and pulled the information from the database):


<input type="text" name="name" value="<?php $name; ?>" />
<input type="checkbox" name="yesno" value="Y" <?php echo $checked; ?> />

I want to have the checkbox default value checked if the data read from the database is ‘Y’.

So this is my code to assign the $checked variable in the form above:


if ($row['yesno'] == 'Y') $checked = 'checked="checked"';

With the above code if the value read from the database is ‘Y’ then it will look like this:


<input type="checkbox" name="yesno" value="Y" checked="checked" />

Otherwise, if the value is ‘N’ then it will look like this:


<input type="checkbox" name="yesno" value="Y" />

Then if the user submits the form this is my code:


$yesno = mysqli_real_escape_string($link, $_POST['yesno']);
if (!isset($_POST['yesno'])) $yesno = 'N';

$sql = "UPDATE table SET yesno='$yesno' WHERE id='$id'";

It all works, but did I do it the best practices way?

2

accidentally clicked an add and lost my post :frowning:

Anyway, I prefer UNSIGNED TINYINT over CHAR(1). You can set a default value too, store 0 for no, 1 for yes.

It looks like you have PHP configured to not warn you when you try to use a variable that doesn’t exist. That can hide programming mistakes.

This should give an error when the checkbox was not checked:

$yesno = mysqli_real_escape_string($link, $_POST['yesno']);

So to use a TINYINT field, pull your data like this.
$checked will always exist (no warnings), and will be an empty string if the value is false


<?php
$yesno = (bool)$the_value_from_my_db; //1 = true, 0 = false

$checked = ($yesno) ? 'checked="checked"' : ''; //see ternary operator
?>
<input type="checkbox" name="yesno" value="1" <?php echo $checked; ?> />

Then when updating the DB:


$yesno = ( isset($_POST['yesno']) ) ? 1 : 0;
$sql = "UPDATE table SET yesno=$yesno WHERE id=$id";

You don’t need to escape $yesno in this case because your explicitly setting it to int 1 or 0.
(I also removed the quotes around $id because I assume this is a numeric field)

3

I sometimes try and model checkboxes such as these as being NULL or 1 in my database.

When you have a multitude of these options, detecting and handling NULL in the tables is much easier - with you single form element it might not make much difference.

I know there is a semantic charge others could level at me for doing that, 1 means “yes”, 0 means “no” and NULL means “not yet set or nothing” - but there are some benefits.

otherwise following up on what cranial_bore says really…

Be concious that an unchecked checkbox is going to deliver nothing back to your backend PHP form handler, in a POST situation then:

here is a simple way of describing the form handling


<?php

$cbox1 = 'NULL';  // declare the default here, could be in array

// otherwise, if set, it must be a 1 - no dithering with analysing 0s
if( isset($_POST['cbox1']) ) $cbox1 = 1;

ps Checkboxes don’t need a value, they either return ‘on’ or don’t come back with anything.

Here’s another discussion on checkboxes and nulls etc you might like:

Ultimately, you may end up wanting to semi-automate the generation of your sql statements and perhaps form elements from a single PHP array, in my mind this is easier when you database defaults to nulls for on/off option checkboxes.

4

Wow, busy week, I was unable to check back on this earlier.

Thank you to both of you. I was unaware of the “Ternary Operator” (still learning PHP) but that makes things so much easier.

5

I’ve now turned on error_reporting(-1) so that I get notices. I seem to have one problem I’m not sure how to deal with now.

When I pull stuff from my database I’ll do something like this for example:


$sql = "SELECT * FROM test";
$result = mysqli_query($link, $sql);

while ($row = mysqli_fetch_array($result)) {
$items[] = array('id' => $row['id'], 'name' => $row['name']');
}

Then later in my HTML I’ll do something like this to display the content in a table:


foreach ($items as $item) {
echo '<td>' . $item['id'] . '</td>';
echo '<td>' . $item['name'] . '</td>';
}

However, my problem is that if my SQL query returns an empty result set then I get this:


Notice: Undefined variable: items in /webapp1/index.html.php on line 46 Warning: Invalid argument supplied for foreach() in /webapp1/index.html.php on line 46

I assume because the variable is empty I am getting this. What is the best way to handle this? I could just throw in another if statement to check if it’s empty but something tells me this is wrong.

6

Above the while ($row = ...), put $items = array ();

Also, don’t use select *, but explicitly select the fields you want

7

Perfect, that works, thanks.

Also, since you bring up the SELECT * thing, is it ever alright to use SELECT * if you actually want every column in the table or should I explicitly select each column?

8

It’s better to name them, even if you want them all. It’s faster for MySQL and helps document your code in that you know what array keys will be present.

The only time I use SELECT * is when debugging, or looking at data manually in Query Browser.

Regarding the above, you probably want an if() to avoid an empty table. Scallio’s suggestion will fix the error you get, but you’ll probably be rendering an empty table with no rows. A simple ‘no records found’ statement without the table might be nicer.

9

Alright, I have a few more questions that have come up.

First, this one is unrelated to the thread but it’s straight forward.

Using $_GET variables, people will do things like ?fruit=apple which is a proper way of doing this. However, sometimes I just want to check if something is set and don’t want to pass a value along. For example, can I just do ?edit instead of ?edit=yes or something similar? I just want to check if edit is set but don’t care about the value so that my controller can now display an edit template.

Second, and this is related to checkboxes again and seems to be giving me troubles.
In Kevin’s PHP book and course, he explains how to loop forms with values to be submitted. For example:


foreach ($rows as $row) {
  <form action="" method="post">
    <input type="text" name="test1" value="<?php echo $row['value']; ?>">
    <input type="hidden" name="test2" value="<?php echo $row['id']; ?>">
    <input type="submit" name="test3" value="Save">
  </form>
}

The code above can be repeated with a loop for all the DB values but the problem is that it creates a submit button for each row and only one row can be acted upon at a time.

What I want to do is list out a bunch of information from my database with one submit button at the bottom to send everything, however, I’m not sure how to wright the SQL statement or process the submitted data because I need to update the data for changed values according to their IDs right?

For a little help, here is a screenshot of something I want to mass edit with a single submit button and then I’m not sure how to process it on the server side.

1473×430 29.5 KB

10

You can make the fields work as arrays, using the record ID as the key.


foreach ($rows as $row) {
  $id = $row['id'];
  $value = htmlspecialchars($row['value'], ENT_QUOTES);

$form .= "<form action='' method='post'>
    <input type='text' name='test1[$id]' value='$value'>
    <input type='hidden' name='test2[$id]' value='$value'>
  </form>";
}

Then when the form is submitted $_POST[‘test1’] will be an array, with the key matching the DB record ID, and the value being whatever the user entered.
Same with $_POST[‘test2’]

You can loop through those records to update your DB.
To avoid resetting a lot of unchanged data you might want to detect change. SitePoint published a nice little article about testing an unchanged form with JS a while ago. Worth searching for. A bit beyond the scope of this thread though.

11

I’m still slightly confused by this.

Let’s say I have 10 rows in the database (realistically it is like 1000) and each row will have 5 fields updated.

So after the form is submitted I’ll end up with 5 $_POST arrays in the following format (assuming I didn’t check updated fields with javascript like you said):


$_POST['field1'] // this is 10 items in this format: $id => $field1
$_POST['field2'] // this is 10 items in this format: $id => $field2
$_POST['field3'] // this is 10 items in this format: $id => $field3
$_POST['field4'] // this is 10 items in this format: $id => $field4
$_POST['field5'] // this is 10 items in this format: $id => $field5

So, to update my database I have to do this:


foreach ($_POST['field1'] as $id => $field1) {
    $sql = "UPDATE table SET field1 = '$field1' WHERE id = $id";
    mysqli_query($sql);
}

And them repeat the above for the other 4 fields.

This means I’m doing 50 update queries (10 rows times 5 fields).

In reality I have about 1000 records and 9 fields making that 9000 update queries everytime I submit the bulk edit form.

It seems like this is the wrong way of doing this, am I missing something or should I be doing this a different way?

Is there a better way to bulk edit a table? This is important to the web app I’m making, otherwise the users will still use Excel.

I didn’t really want to use javascript because I wanted to keep this as simple as possible.

12

Your on the right line of thinking, however you don’t need to execute a different query for every field that is changing.
The 5 different $_POST arrays are known as Parallel Arrays. Have a look at that link.

So if the id/key was 26 then $_POST['field1][26], $_POST[‘field2’][26] (and so on…) together provide all the data for product #26.

You can loop over the first field, and look up that product’s other data from the other POST arrays. They same key is used on all, which will match your DB ID.


foreach($_POST['field1'] as $key =&gt; $field1) {

	//$field1 is the value from the form for this iteration of the loop
	
	$field2 = $_POST['field2'][$key];
	$field3 = $_POST['field2'][$key];
	$field4 = $_POST['field2'][$key];
	$field5 = $_POST['field2'][$key];
	
	//escape data before using in SQL!
	//this is example only
	
	$sql = "UPDATE `table` SET field1='$field1', field2='$field2', field3='$field3', field4='$field4', field5='$field5' WHERE id = $key";
}

This way you have one query per row and not one query per row/field combination.

Big improvement, however if there are 1000 rows you probably still want to detect if anything has changed on each row, and avoid the query if not.
From a form with 1000 rows the vast majority will be unchanged each submission.

This can be achieved by writing hidden field(s) to the form which contains the original values of the fields. Then when you process the form if the new values are the same as the old ones you can bypass that query.

13

Perfect, thank you for all your help. I have a lot to learn :slight_smile:

14

Sorry, I have one more question and instead of checkboxes it is with drop boxes.

Your solution for checking checkboxes is working flawlessly. I am using 1 and 0 in the database for true and false respectively and using the ternary operator and everything is working that way.

But, now I’ve implemented a drop box in one of the forms that gets populated by one of the tables in the database.

I’ve implemented a solution that is working, I just want to know if this is the proper way to do it.

Here is my code example of how it is:
Note: this is an example and not escaped strictly for readability here.


$sql = "SELECT contractid, contractname, cropid, cropname
          FROM contracts
          INNER JOIN crops ON contractcrop = cropid
          WHERE contractid = $id";
$result = mysqli_query($link, $sql);
$row = mysqli_fetch_array($result);
$contract = array('contractid' => $row['contractid']
                         'contractname' => $row['contractname']
                         'cropid' => $row['cropid']
                         'cropname' => $row['cropname']);

$sql = "SELECT cropid, cropname FROM crops";
$result = mysqli_query($link, $sql);
$row = mysqli_fetch_array($result);
while ($row = mysqli_fetch_array($result)) {
	$crops[] = array('cropid' => $row['cropid'],
			       'cropname' => $row['cropname'],
			       'selected' => ($row['cropid'] == $contract['cropid']) ? ' selected="selected" ' : '');
}

So, with the above code I am getting two arrays. One is the single contract I’m requesting with the crop name from a relational link. However, I need to also pull the crop ID so that I can use it later in the next SQL statement. The second array is the crop table in its full which will populate the drop down box. Here I am checking if the cropid is equal to the relational link in the contract table to contractcrop and if it is, then $crops[‘selected’] is assigned a value of selected=“selected”.

Question: Is this the proper way to check if the item in the database is already selected?

My HTML template looks like this:


<tbody>
	<tr>
		<td><?php echo $contract['contractname']; ?></td>
		<td><input type="hidden" name="contractid" value="<?php echo $contract['contractid']; ?>" /><?php
			echo $contract['contractid']; ?></td>
		<td><select name="cropid">
			<?php foreach ($crops as $crop): ?>
				<option value="<?php echo $crop['cropid']; ?>"<?php
					echo $crop['selected']; ?>><?php
					echo $crop['cropname']; ?></option>
			<?php endforeach; ?>
		</select></td>
	</tr>
</tbody>

To process the form is quite simple:


	$id = $_POST['contractid'];
	$crop = $_POST['cropid'];

	$sql = "UPDATE contracts SET contractcrop = {$crop} WHERE contractid = {$id}";

And here is a screenshot to help explain what it is I’m doing:

I really appreciate all the help. I’m just really OCD about doing things the right way, thanks.