Amuseic Amuseic - 1 year ago 37
PHP Question

Update multiple existing rows with php without PATCH?

Currently I'm quite unexperienced with php but needed a little simple program. I've created a little website where some data can be stored (The name of a product, the amount of those products and the price of that products)

I've called it back as a table with in every table row an input(text) field with the containing data.

I need this data to be editable and all be saved with just one press on the button but I just can't get it to work...;(

Here's my current code:

$products_array = array("");
$amounts_array = array("");
$prices_array = array("");
$id_array = array("");

<form method="POST" name="update">
<?php
foreach($object as $objects){
array_push($products_array, $objects['product']);
array_push($amounts_array, $objects['amount']);
array_push($prices_array, $objects['price']);
array_push($id_array, $objects['id']);

echo "<tr>
<td><input type='text' name='".$objects['id']."' value='". $objects['product'] ."'></td>
<td><input type='text' name='".$objects['id']."' value='". $objects['amount'] ."'></td>
<td><input type='text' name='".$objects['id']."' value='". $objects['price'] ."'></td>
</tr>";
}
?>
<input type="submit" value="Opslaan">
</form>


this receives all the rows from the database in text fields and store their data in an array.

This is how I tried to update it:

<?php
if(isset($_POST['update'])){
for($i = 1; $i <= $count; $i++){
$sql = 'UPDATE objects SET product = "$products_array[$i]", amount = "$amounts_array[$i]", price = "$prices_array[$i]" WHERE id = "$id_array[$i]" ';
$sql = $conn->query($sql);
}
unset($_POST);
header('Location: index.php');
}
?>


Anyone has any clue how to fix this or has a better method?

Thanks in advance ^_^

Answer Source

You need to uniquely identify each product details i.e. it's name, amount and price by it's id to perform the update operation. So the solution would be like this:

  • There's no need to declare separate arrays for each attribute of a product, only $object array is enough to solve this problem.
  • Your <form> should be like this: (Look closely at the <input> elements)

    <form method="POST">
        <table>
        <?php
        foreach($object as $objects){
            ?>
            <tr>
                <td><input type="text" name="objects[<?php echo $objects['id']; ?>][product]" value="<?php echo $objects['product']; ?>" /></td>
                <td><input type="text" name="objects[<?php echo $objects['id']; ?>][amount]" value="<?php echo $objects['amount']; ?>" /></td>
                <td><input type="text" name="objects[<?php echo $objects['id']; ?>][price]" value="<?php echo $objects['price']; ?>" /></td>
            </tr>
            <?php
        }
        ?>
        </table>
        <input type="submit" name="update" value="Opslaan">
    </form>
    
  • And this is how you can process the form to perform the UPDATE operation.

    if(isset($_POST['update'])){
        foreach($_POST['objects'] as $id => $products_array){
            $sql = "UPDATE objects SET product = '" . $products_array['product'] . "', amount = '" . $products_array['amount'] . "', price = '" . $products_array['price'] . "' WHERE id = '" . $id . "'";
            $sql = $conn->query($sql);
        }
    
        // your code
    
    }
    

Sidenotes:

  1. If you want to see the complete structure of $_POST['objects'], do var_dump($_POST['objects']);
  2. Your query is susceptible to SQL injection. Always prepare, bind and execute your queries to prevent any kind of SQL injection. Here's a good SO Q/A on how to prevent SQL injection in PHP.