How to pass multiple values with a common index key in a foreach loop?

Question

I need to pass two or more inputs with a common index key in a foreach loop to update multiple mysql rows at once.

Frontend:

<table>
                <thead>
                <tr>
                  <th>ID</th>
                  <th>Column 1</th>
                  <th>Column 2</th>
                </tr>
                </thead>

                <tbody>

        <form method="post" action="process.php">

        <?php
        $stmt = $mysqli->prepare("SELECT id,column1,column2 FROM table");
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($id,$column1,$column2);
        while ($stmt->fetch()) {

            ?>
                <tr>
                  <td><?php echo $id ?></td>

                  <!-- Here User will input values for the below two fields in all the rows -->

                  <td><input type="text" name="column1[<?php echo $id; ?>]"/></td>
                  <td><input type="text" name="column2[<?php echo $id; ?>]"/></td>


                </tr>
        <?php } ?>
                <input type="submit" name="submit" value="Update all">
                </form>
                </tbody>

</table>

Backend:

<?php
if(isset($_POST['submit'])){

        foreach($_POST['column1'],$_POST['column2'] as $key=>$value,$value1){ //column1 and column2 have common $id in each row

            $stmt = $mysqli->prepare("UPDATE table SET column1 = ?, column2 = ? WHERE id = ?");
            $stmt->bind_param('ssi',$value,$value1,$key);
            $stmt->execute();

        }

        if ($stmt->execute()) {
            echo "Done!";
            exit();
        }

}

?>

Here, index key (id) is common between "column1" and "column2" in each row. All the rows have unique ids.

I need to pass the values of "column1" and "column2" with common index key in foreach loop. So that I can update the database table columns "column1" and "column2" in all the rows in a single query.

All your help will be appreciated.

You should do prepare and bind_param before the loop, not every time. — Barmar
– Barmar, Commented Apr 10, 2017 at 21:17

Jaime · Accepted Answer · 2017-04-10 21:08:15Z

2

<?php
foreach (array_keys($_POST['column1']) as $key) {
  $value = $_POST['column1'][$key];
  $value1 = $_POST['column2'][$key];
  $stmt = $mysqli->prepare("UPDATE table SET column1 = ?, column2 = ? WHERE id = ?");
  $stmt->bind_param('ssi',$value,$value1,$key);
  $stmt->execute();
}

answered Apr 10, 2017 at 21:08

Jaime

1,3817 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass multiple values with a common index key in a foreach loop?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related