Amuseic Amuseic - 5 months ago 12
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

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.