Updating Multiple HTML Fields into MySQL Database with PHP

Question

This seems like a very basic task; I am not sure if I am having a brain cramp or if I ran into something a bit more rare. But it doesn't seem so.

I have a table called products that have these columns: ID, Model, Cost, Quantity

My HTML form prints out all price and quantity discounts by model; I loop thru like this:

 <form method="post">

 <table>
       <tr>
            <th>Quantity</th>
            <th>Pricing</th>
       </tr>

      <?php while ($row = mysqli_fetch_array($result)) { ?>

       <tr>
           <td><?php echo $row['model']; ?></td>
           <td><input type="text" name="<?php echo $row['id']; ?>" value="<?php echo $row['cost']; ?>" /></td>

      </tr>

     <?php } ?>

 </table>


  <input type="submit" name="retail_price" value="Update Pricing"  />

 </form>

So it prints out all pricing in text fields, and has a button so that there can be "mass" update to pricing.

My question is regarding the PHP where I am stuck:

if (isset($_POST['retail_price'])) {  // THIS CODE IS NOT COMPLETE!

    $query = "
        UPDATE retail_pricing
        SET pricing = {$new price}
                    WHERE id = {$id} ";
    mysqli_query($connection, $query);

}

The issue I have run into, I want to go through each field and update each one. Doing a quick search for other Stack questions, it seems like I need to use a foreach to iterate through each field. Is this correct? Also, would the query be in the loop as well?

Why do you want to iterate over all the fields? You can make one update with all the fields if they are in the same table. — Hernan Velasquez
– Hernan Velasquez, Commented Sep 25, 2013 at 16:01
I think the biggest thing I am struggling with is the identifying mark. What to put in the name="" field. It should be unique, but the ID doesn't seem like that is appropriate. So on the POST, I'd have to manually spell out each field and since I am looping through the fields to create them in HTML, seemed like I would need to do the same on the php end. — TheLettuceMaster
– TheLettuceMaster, Commented Sep 25, 2013 at 16:06

Sébastien · Accepted Answer · 2017-12-22 20:18:42Z

2

foreach($_POST as $key => $value) {
    if(is_numeric($key))
    {
        $query = "UPDATE retail_pricing SET pricing = $value  WHERE id = $key ";
        mysqli_query($connection, $query);
    }  
}

edited Dec 22, 2017 at 20:18

Sébastien

12.1k12 gold badges60 silver badges83 bronze badges

answered Sep 25, 2013 at 16:07

Jenson M John

5,7195 gold badges32 silver badges48 bronze badges

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

3 Comments

Sébastien Over a year ago

It will most likely work, but this will also accept any numeric field name even if it is not a valid ID in your DB, which may cause failures.

TheLettuceMaster Over a year ago

@Sébastien Ok thanks, good to know. This is avery, very simply project so I'll stick with it since it's not broke, but going forward I would be inclined to use yours; so I upvoted as well.

Sébastien Over a year ago

As long as you are aware of it :)

Sébastien · Accepted Answer · 2013-09-25 16:09:19Z

2

Add a hidden input in your HTML that will collect all IDs:

<tr>
    <td><?php echo $row['model']; ?></td>
    <td>
    <input type="text" name="<?php echo $row['id']; ?>" value="<?php echo $row['cost']; ?>" />
    <input type="hidden" name="ids[]" value="<?php echo $row['id']; ?>" />
    </td>
</tr>

then when you submit the form you can loop through all those IDs and update you DB:

foreach( $_POST['ids'] as $id )
{
    $price = $_POST[$id];
     $query = "
        UPDATE retail_pricing
        SET pricing = {$price}
                    WHERE id = {$id} ";
    mysqli_query($connection, $query);
}

answered Sep 25, 2013 at 16:09

Sébastien

12.1k12 gold badges60 silver badges83 bronze badges

1 Comment

Springie Over a year ago

I prefer this method as it will prevent any issues with the query if you add further form fields in the future.

Collectives™ on Stack Overflow

Updating Multiple HTML Fields into MySQL Database with PHP

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related