How to use checkboxes to enter true (1) or false (0) into mySQL AND show as checked in php / html form?

Question

I have a table with a field name Shift_Trig that is meant to have either a true or false value entered into it in the form of a 1 or 0. I have an html form with a checkbox for each row that, when checked, should enter a 1 into the field. I also want that checkbox to reflect the current value stored in the Shift_Trig field within the database.

The problem I'm having with my current code is whenever I UPDATE the Shift_Trig field, I'm not getting the results I want. For example, if I select:

----------------------
| ROW 1   |   false  |
----------------------
| ROW 2   |   true   |
----------------------
| ROW 3   |   false  |
----------------------
| ROW 4   |   true   |
----------------------
| ROW 5   |   true   |
----------------------

It will UPDATE like this:

----------------------
| ROW 1   |   true   |
----------------------
| ROW 2   |   true   |
----------------------
| ROW 3   |   true   |
----------------------
| ROW 4   |   false  |
----------------------
| ROW 5   |   false  |
----------------------

Here is my form code:

while($shift_query = $result_shift_query->fetch_assoc())
    {
    echo "<tr>";    
    echo "<td><input type=\"hidden\" name=\"Shift_ID[]\"></td>";
    echo "<td><input type=\"text\" name=\"Shift_Name[]\"></td>";
    echo "<td><input type=\"text\" name=\"Shift_Short_Name[]\"></td>";
    echo "<td><input type=\"text\" name=\"Shift_Color[]\"></td>";
    echo "<td><input type=\"hidden\" name=\"Shift_Trig[]\" value=\"0\"><input type=\"checkbox\" name=\"Shift_Trig[]\"";
        if($shift_query['Shift_Trig'] == '1')
            {
            echo " checked=\"checked\"";
            }
    echo " value=\"1\"></td>";
    echo "<td><input type=\"checkbox\" name=\"deleteBox[]\"></td>";
    echo "</tr>";
    }

Here is my UPDATE code:

if(isset($_POST['update_submit']))
    {
    $id = $_POST['Shift_ID'];
    $name = $_POST['Shift_Name'];
    $short_name = $_POST['Shift_Short_Name'];
    $color  = $_POST['Shift_Color'];
    $trig = $_POST['Shift_Trig'];

    $i = 0;

foreach($id as $update) 
    {
    $sql = ("UPDATE shift SET Shift_Name = '$name[$i]', Shift_Short_Name = '$short_name[$i]', Shift_Color = '$color[$i]', Shift_Trig = '$trig[$i]' WHERE Shift_ID = '$update'");
    if(!$result_shift_update = $mysqli->query($sql))
        {
        die = ("There was an error updating the shift table [" . $mysqli->error . "]");
        }
    echo "sql: " . $sql . "<br>";
    $i++;
    }

I've removed some of the code in an effort to make it easier to read, such as where it populates the text boxes with the field data from the database.

When I select rows 2 and 3 in the form, then echo out the $sql statements, I get this:

sql: UPDATE shift SET Shift_Name = 'None', Shift_Short_Name = 'None', Shift_Color = '#FF0000', Shift_Trig = '1' WHERE Shift_ID = '1'
sql: UPDATE shift SET Shift_Name = 'Morning (05:30 - 14:30)', Shift_Short_Name = 'AM', Shift_Color = '#FFF8AF', Shift_Trig = '1' WHERE Shift_ID = '2'
sql: UPDATE shift SET Shift_Name = 'Evening (14:30 - 23:30)', Shift_Short_Name = 'PM', Shift_Color = '#AF9BCF', Shift_Trig = '' WHERE Shift_ID = '3'
sql: UPDATE shift SET Shift_Name = 'General (07:30 - 17:30)', Shift_Short_Name = 'GEN', Shift_Color = '#ABFFB3', Shift_Trig = '' WHERE Shift_ID = '4'
sql: UPDATE shift SET Shift_Name = 'Night (18:00 - 06:00)', Shift_Short_Name = 'EMMA', Shift_Color = '#BFFAFF', Shift_Trig = '' WHERE Shift_ID = '5'
sql: UPDATE shift SET Shift_Name = 'Training', Shift_Short_Name = 'TRN', Shift_Color = '#FFD9BF', Shift_Trig = '' WHERE Shift_ID = '6'
sql: UPDATE shift SET Shift_Name = 'Day Off', Shift_Short_Name = 'OFF', Shift_Color = '#FFD859', Shift_Trig = '' WHERE Shift_ID = '7'

What's happening makes sense, because the first checkbox is not selected, it's not being passed to the $_POST data, but how do I write the code so I get the desired results?

EDIT : I added : <input type=\"hidden\" name=\"Shift_Trig[]\" value=\"0\"> but now I'm getting every second checkbox showing as checked.

Consider this approach: ``. If the checkbox is checked, it will overwrite the hidden value, if not, the hidden value will be sent. In my opinion, the most logical `$id` in this case would be the shift's ID instead of some index. (Edit: also, look into [prepared statements and bind variables](http://us.php.net/manual/en/mysqli.quickstart.prepared-statements.php#example-1680). ) — DCoder, Dec 23 '12 at 10:53
@DCoder I was just reading this [answer](http://stackoverflow.com/questions/1944019/how-do-i-get-all-checkbox-variables-even-if-not-checked-from-html-to-php) and it suggested the same thing. I'm going to try that. — Andrew Fox, Dec 23 '12 at 10:59
@DCoder I edited the question, for some reason it's not working :/ — Andrew Fox, Dec 23 '12 at 11:06
You missed the part where the hidden input and the checkbox have the same name, based on $id. Actually, I would recommend a more radical change in the HTML structure (put each `shift` into a separate array instead of relying on this indexing based solution). — DCoder, Dec 23 '12 at 11:10
Ahh, I see where I missed the $i now, that makes sense. I'm not sure what you mean by putting each `shift` into a separate array, but I would be interested to see what you mean. As you can see, I've already marked a solution to this question, however I still really appreciate your time and help :) — Andrew Fox, Dec 23 '12 at 11:15
I meant doing something like this: ` ` ... . Then PHP will receive all shifts in `$_POST['shift']`, where every value will be an array of all the values related to a single shift. You can use Shift_ID for `$id`. — DCoder, Dec 23 '12 at 11:25

Barmar · Accepted Answer · 2012-12-23T10:59:44.300

You need to give the checkboxes an explicit index in the HTML:

$i = 0;
while($shift_query = $result_shift_query->fetch_assoc())
    {
    echo "<tr>";    
    echo "<td><input type=\"hidden\" name=\"Shift_ID[]\"></td>";
    echo "<td><input type=\"text\" name=\"Shift_Name[]\"></td>";
    echo "<td><input type=\"text\" name=\"Shift_Short_Name[]\"></td>";
    echo "<td><input type=\"text\" name=\"Shift_Color[]\"></td>";
    echo "<td><input type=\"checkbox\" name=\"Shift_Trig[$i]\"";
        if($shift_query['Shift_Trig'] == '1')
            {
            echo " checked=\"checked\"";
            }
    echo " value=\"1\"></td>";
    echo "<td><input type=\"checkbox\" name=\"deleteBox[$i]\"></td>";
    echo "</tr>";
    $i++;
    }

In the update code, at the beginning of the loop, do:

    if (!isset($trig[$i])) { $trig[$i] = 0; }

The rest of the update code is unchanged.

Thank you for the reply. Can you show me what the `UPDATE` query would look like? — Andrew Fox, Dec 23 '12 at 10:54
That fixed it! I'm marking it as solved, thanks for your help, I learned something valuable today! — Andrew Fox, Dec 23 '12 at 11:10

How to use checkboxes to enter true (1) or false (0) into mySQL AND show as checked in php / html form?

1 Answers1

Linked

Related