'Insert prepare statement mutiple checkbox with multiple queries in php

I know this question may have been asked before but I cannot find the solution for my issue. I want to implement prepare statements (procedural) to multiple checkbox I do understand how it works and has worked with it before. But in this situation, I am not sure how to implement it. Can someone please provide me with a clue?

<?php
if(isset($_POST) && count($_POST) > 0){
    if (isset($_POST['checkbox1'])) {
        $sql1="UPDATE switch SET status = 1 WHERE id = 1";
    } else {
        $sql1="UPDATE switch SET status = 0 WHERE id = 1";
    }
    $result=$conn->query($sql1);

    if (isset($_POST['checkbox2'])) {
        $sql2="UPDATE switch SET status = 1 WHERE id = 2";
    } else {
        $sql2="UPDATE switch SET status = 0 WHERE id = 2";
    }
    $result=$conn->query($sql2);

    if (isset($_POST['checkbox3'])) {
        $sql3="UPDATE switch SET status = 1 WHERE id = 3";
    } else {
        $sql3="UPDATE switch SET status = 0 WHERE id = 3";
    }
    $result=$conn->query($sql3);

    if (isset($_POST['checkbox4'])) {
        $sql4="UPDATE switch SET status = 1 WHERE id = 4";
    } else {
        $sql4="UPDATE switch SET status = 0 WHERE id = 4";
    }
    $result=$conn->query($sql4);
}

?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
</head>
<body>
    <form id="form" action="" method="POST" >

        <label>
            Checkbox 1
            <input type="checkbox" name="checkbox1" value="1" onchange="document.getElementById('form').submit();"
                <?php if(isset($_POST['checkbox1'])) { echo 'checked="checked"'; } ?>>
        </label>

        <label>
            Checkbox 2
            <input type="checkbox" name="checkbox2" value="1" onchange="document.getElementById('form').submit();"
                <?php if(isset($_POST['checkbox2'])) { echo 'checked="checked"'; } ?>>
        </label>

        <label>
            Checkbox 3
            <input type="checkbox" name="checkbox3" value="1" onchange="document.getElementById('form').submit();"
                <?php if(isset($_POST['checkbox3'])) { echo 'checked="checked"'; } ?>>
        </label>

        <label>
            Checkbox 4
            <input type="checkbox" name="checkbox4" value="1" onchange="document.getElementById('form').submit();"
                <?php if(isset($_POST['checkbox4'])) { echo 'checked="checked"'; } ?>>
        </label>

    </form>
</body>
</html>
phpmysqlprepared-statement


Solution 1:[1]

Starting from the form construction, you should not make name attributes with appended numbers when the data is more meaningfully established in an array structure. By using arrays, multiple techniques can be implemented you make your code less bloated, hardcoded, and repetitious. Perhaps for easier reading, rename your name attributes boxes instead of checkbox which is identical to the type attribute.

Use a loop to construct your fields.

$maxBoxes = 4;  // assign this near the top of your php file so that multiple sections of your file can use it.

$fieldTemplate = <<<HTML
<label>
    Checkbox %1$d
    <input type="checkbox" name="boxes[%1$d]" value="1"%2$s>
</label>

HTML;

for ($x = 1; $x <= $maxBoxes; ++$x) {
    printf(
        $fieldTemplate,
        $x,  // %1$d
        isset($_POST['boxes'][$x]) ? " checked" : '' // %2$s
    );
}

Move your onchange inline event to the <head> of your HTML document or better yet, into an external .js file. This keeps your HTML markup very clean, lean, and readable.

Now, when your submission hits your receiving code, you can enjoy a loop again. (untested snippet)

$sql = "UPDATE switch SET status = CASE id WHEN" 
for ($x = 1; $x <= $maxBoxes; ++$x) {
    $sql .= sprintf(
        " %d THEN %d",
        $x, // %d
        (int)isset($_POST['boxes'][$x]) // %d
    );
}
$sql .= " END WHERE id BETWEEN $x AND $maxBoxes";

Because you are 100% in control of the dynamic data being fed to the query and you the placeholders in sprintf() are strongly typed, using a prepared statement can be avoided -- not that there is anything wrong with using a prepared statement (I merely chose not use one).

Perhaps the only thing not mentioned is: How are you going to determine that a submission was made when a submission includes no checkboxes? You are submitting every time a checkbox is toggled. So if the on the first submission, a checkbox is ticked, then on the checkbox is unticked, the second submission will not trigger an UPDATE to the database. To prevent this, you may want send a hidden value from the form so that you have something to confirm a submission. (The $_POST superglobal array is always declared; you cannot determine anything by checking if it isset().)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 mickmackusa

Related Questions