Best way to repeat this php code

Question

Still getting the hang of PHP and would like some guidance on the best way to make this code loop. I have got the php code that generates the html code working, it creates a checkbox for each entry in my MySQLi database using a WHILE command. I need to do something similar with this code.

mysqli_select_db($conn,'database_1')or die("Connection Failed");

$check_0 = $_POST['checkbox0'];
$query_0 = "UPDATE test2 SET state = '$check_0' WHERE id = '0'";
mysqli_query($conn, $query_0);

$check_1 = $_POST['checkbox1'];
$query_1 = "UPDATE test2 SET state = '$check_1' WHERE id = '1'";
mysqli_query($conn, $query_1);

$check_2 = $_POST['checkbox2'];
$query_2 = "UPDATE test2 SET state = '$check_2' WHERE id = '2'";
mysqli_query($conn, $query_2);

mysqli_close($conn);

As you can see from the code, I just need to have the numbers update, so that the next lines of code should have;

$check_3, 'checkbox3', $query_3, & id = '3'

If I could do something like;

$check_x where id = x that would be ideal, and then loop while id has a value. Thanks for any help.

EDIT 1

Here is the checkbox PHP that works. The code does 3 things; create a checkbox, send checkbox check status to the above code, and apply the state of the MySQLi database to the checkbox.

echo(string) "<form action='includes/checkbox.inc.php' method='post'>";
while($row_3 = mysqli_fetch_array($result_4, MYSQLI_ASSOC)) {
    echo    "<input type='hidden' name='checkbox";
    echo    $row_3['id'];
    echo    "' value='0'><input type='checkbox' name='checkbox";
    echo    $row_3['id'];
    echo    "' value='1'";

    /*Below section for check status*/
    if ($result_4 = $sql_3) {
    mysqli_data_seek($result_4, $row_3['id']);
    $row_4 = mysqli_fetch_row($result_4);
    }   
    if ($row_4 [0]=="1") {
        echo "checked";
        } else {
        echo " ";
        }
    /*Above section for check status*/

    echo    "> Item ";
    echo    $row_3['id'];
    echo    "<br>";

    }
    echo "<input type='submit' name='Submit' value='Submit'>";
    echo "</form>";
    ?>

Answer 1

Here is the how, First of all you need to change how you are creating the checkboxes:

 <input type="checkbox" name="checkbox[1]" />

So in your wile loop (i'm just guessing as you didnt post this),

while({something}){
   echo '<input type="checkbox" name="checkbox['.$id.']" />'
}

Then with the correct structure it makes everything much easier:

mysqli_select_db($conn,'database_1')or die("Connection Failed");
$stmt = mysqli_prepare("UPDATE test2 SET state = ? WHERE id = ?");

foreach($_POST["checkbox"] as $key => $value){
    $stmt->bind_param("ss", $value, $key);
    $stmt->execute();
}

mysqli_close($conn);

It's very important to put the id inside of the [] for the checkbox name, otherwise you wont be able to correlate it back to the id.

Later you will find out that if a checkbox is not checked it won't submit soe it wont even exist in the back end. You can fix this by putting a hidden field before it with the "off" value"

    <input type="hidden" name="checkbox[1]" value="off" />
    <input type="checkbox" name="checkbox[1]" value="on" />

This way, if the checkbox is not checked the hidden field is submitted, if it is checked then it overwrites the value of the hidden field.

You also had SQLInjection issues by using $check in the query directly, you should do a prepared statement. I don't use mysqli (for like the last 4 years) and I only use PDO now so hopefully I got that bit right. But I would be remiss in not even attempting to fix the security issue.

AS a side note, you get better performance when preparing the query outside of the loop, and executing it within.

I would also highly recommend turning on error reporting if its not:

<?php
 error_reporting(-1);
 ini_set('display_errors', 1);

UPDATE

You should be able to just put the brackets in []

while($row_3 = mysqli_fetch_array($result_4, MYSQLI_ASSOC)) {

   /*Below section for check status*/
    if ($result_4 = $sql_3) {
        mysqli_data_seek($result_4, $row_3['id']);
        $row_4 = mysqli_fetch_row($result_4);
    }   

    $checked = $row_4[0] == 1 ? "checked='checked' " : '';
    /*Above section for check status*/


    echo    "<input type='hidden' name='checkbox[{$row_3['id']}]' value='0'>":
    echo    "<input type='checkbox' name='checkbox[{$row_3['id']}' value='1' {$checked}>";
    echo    "Item ";
    ...

In any case I reorganized your code. I couldn't hep myself as the way you had it made it very hard to read. Readability is always #1, then #2 Security, then #3 Functionality, #4 performance. Nothing else matters if you cant glance at the code and tell what it is doing.

The "words {$var}" is just another way of doing "words ".$var."..." without using quotes, it only works for double quotes thought. Actually double quoted strings you can just do "words $var" but there is that readability thing again.

As far as how the logic of the [] type system works, if you mean how does it work (not how to do it), the short answer is, that's just the way it works. You an do just name="checkbox[]" but then you have to use the id as the value and limit what you can do. Not to mention the $id seems like a better fit as a key.

Hope that helps.

Answer 2

You could use either a while loop or a for loop to do that. I prefer a for loop:

$i = 0;
for($i < 10){
 $var = 'checkbox'.$i;
 $db = 'test'.$i;
 $check = $_POST[$var];
 $query = "UPDATE $db SET state = '$var' WHERE id = $i";
 mysqli_query($conn, $query);
 $i++;
}