PHP/MySQL only updates first row borkness

Question

I'm a complete newb at PHP/MySQL, but have decided to make some sort of interface to deal with my shop's stock. I've learned loads but am now stuck and in despair.

This form displays a group of a certain type of stock and the quantity:

<form method="post" action="submit.php">
<?php
while ($row = mysql_fetch_assoc($rs)) { 
echo"<input type=\"hidden\" name=\"ovid[]\" value=" . $row['option_value_id'] . " />";
echo $row['name'];
echo"<input type=\"text\" name=\"quant[]\" value=" . $row['quantity'] . " />";
echo"<input type=\"submit\" /><br />";
} ?>
</form>

And this is the submit.php:

$_POST['ovida'] = implode(",",$_POST['ovid']);
$_POST['quanta'] = implode(",",$_POST['quant']);

print $_POST["ovida"];
print $_POST["quanta"];

$ovidyay = $_POST["ovida"];
$quantyay = $_POST["quanta"];

$query = "UPDATE product_option_value SET quantity ='$quantyay' WHERE option_value_id = '$ovidyay'";
$rs = mysql_query( $query )
or die( mysql_error() );

There's no error when I press submit (there have been many), but the only entry I can update is the first one on the list. Any help would be gratefully accepted.

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — ceejayoz, Feb 13 '13 at 21:55
Thanks muchly. This is certainly turning into a learning experience ... — ceejayemm, Feb 13 '13 at 22:03

score 1 · Accepted Answer · edited May 23 '17 at 11:44

You could iterate and update each of the items separately:

for ($i = 0; $i < COUNT($_POST['ovid']); $i++)
{
    $ovidyay = $_POST['ovid'][$i];
    $quantyay = $_POST["quanta"][$i];

    $query = "UPDATE product_option_value SET quantity ='$quantyay' WHERE option_value_id = '$ovidyay'";
    $rs = mysql_query($query);
}

Additionally, the code is vulnerable to SQL Injection attacks and you are using deprecated MySQL php functions. If you do want to make your code better an less vulnerable, take a look at the following links:

Why shouldn't I use mysql_* functions in PHP?

What could i use instead of mysql_ functions?

Prepated Statements

Prepared Statements with MySQLi

Thank you, this worked lovely. I'll be taking a look at those links as well. — ceejayemm, Feb 13 '13 at 23:29

score 0 · Answer 2 · edited Jan 23 '16 at 08:52

First of all do not use mysql_* functions try using either PDO or MySQLi and read up on prepared statements.

If you still want to use mysql_ functions at least escape them using mysql_real_escape_string but i would not recommended using mysql_ functions since they have been deprecated.

Here is what you can do:

You can loop through each post data and update it one by one or
build a long query and execute it once.

PHP/MySQL only updates first row borkness

2 Answers2