CODEWITHSUNDEEP
phpmysqli
amuntazar
amuntazar

Reputation: 37

Use loop on sql "UPDATE" for setting value

So, I'm learning about how to update a database using loop. I have a table with field names like "id", "nh1", "nh2", "nh3".

Here's the example of input boxes:

<?php
echo "<form action='' method='post'>";
for ($i = 1; $i <= 3; $i++) {
    echo "<input type='text' name='input$i'>";
}
echo "<input type='submit' value='Submit'>";
echo "</form>";

What I'm trying to do is that I want to update my database using loop, Like:

<?php

for ($i = 1; $i <= 3; $i++) {
    ${'NH'.$i} = $_POST['input'.$i];
}
for ($i = 1; $i <= 3; $i++) {
    $q = mysqli_query("update myTable set nh$i=${'NH'.$i} where id=1");
}

Is it even possible? Or is there any other way to use it correctly?

Hope I made my question clear.

Upvotes: 0

Views: 40

Answers (1)

Nigel Ren
Nigel Ren

Reputation: 57121

This version uses both prepared statements as well as building a single SQL statement. I've added code in the comments to help...

// Values used to bind to prepare
$binds = [];
// The types of the binds
$types = "";
// The SQL statement
$update = "update myTable set ";
for($i=1; $i<=3; $i++){
    // If this input is set
    if ( isset($_POST['input'.$i])) {
        // Store it in the array
        $binds[] = $_POST['input'.$i];
        // Add in part of SQL - e.g. nh1 = ?,
        $update .= "nh$i = ?,";
        // Add type of parameter (string)
        $types .= "s";
    }
}
// Add where clause (remove trialing comma from list of fields to update
$update = substr ($update, 0, -1)." where id=1";
$stmt = $mysqli->prepare($update);
// Bind the variables
$stmt->bind_param($types, ...$binds );
$stmt->execute();

The statement it builds would be something like...

update myTable set nh1 = ?,nh3 = ? where id=1

with the data to bind shown below, so each of these elements will be put in place of the corresponding placeholder (the ?) in the SQL.

Array
(
    [0] => value for input 1
    [1] => value for input 3
)

Upvotes: 1

Related Questions