php update certain fields to database

Question

I'm trying to work on an inventory system where users may view their inventory and update quantity with the value that input by user only and rest remains the same from database. But its not working please help me find where I did wrong. It will echo the success message but the database isn't updated.

<form name="form" method="post">
<table width="70%" border="5" align="center"><tr>
<th scope="row">SKU</th>
<th scope="row">Item Description</th>
<th scope="row">Current Qunatity</th>
<th scope="row">Update Quantity</th>
<th scope="row">Unit Price</th>
</tr>
<tr>
<th scope="row">
<?php
include('connect.php');
$result = mysqli_query("SELECT * FROM products") 
        or die(mysqli_error());

while($row = mysqli_fetch_array( $result )) {
    echo "<tr>";
    echo '<td><a name="sku[]">'.$row['sku_id'].'</a></td>';
    echo '<td>'.$row['description'].'</td>';
    echo '<td>'.$row['quantity'].'</td>';
    echo '<td><input name="qty[]" /></td>';
    echo '<td>'.$row['unit_price'].'</td>';
    echo "</tr>"; 
    }
?>
</table>
<input style="float:right" name="update" type="submit" id="update" value="Update"/>
</form>
<?php

if(isset($_POST['update']))
{
    $qty = $_POST['qty'];
    $sku = $_POST['sku'];
    foreach($qty as $key => $value) 
    {
        if(empty($value))
        {
            continue;
        }
        else
        {
            $sql = "UPDATE products SET quantity ='".$value."' WHERE sku_id = '".$sku[$key]."'";
            mysql_query($sql);
        }
    }   
    $retval = mysqli_query($sql);
    if(! $retval)
    {
        die('Could not update data: '. mysql_error());
    }
    echo 'Update data successfully!';
}
?>

things like this are quite easy to avoid with a simple error output — baao, Nov 04 '14 at 12:52
@Joseph Not related to your question, but why are you not using PDO - no sql injection, recommended. mysql_query is deprecated — adnan kamili, Nov 04 '14 at 13:29
Thanks for the recommendation! I'll find out how exactly does PDO works! — Joseph, Nov 04 '14 at 13:51

Jonathon · Answer 1 · 2014-11-04T12:57:12.393

2

You are using mysql_query here:

$sql = "UPDATE products SET quantity ='".$value."' WHERE sku_id = '".$sku[$key]."'";
mysql_query($sql);

Instead of mysqli_query:

$sql = "UPDATE products SET quantity ='".$value."' WHERE sku_id = '".$sku[$key]."'";
mysqli_query($sql);

In addition, you're using mysql_error here as well:

die('Could not update data: '. mysql_error());

P.S. Don't forget to escape any user input you are using in a database query! Though ideally you should use something like PDO or MySQLi prepared statements

edited Nov 04 '14 at 12:57

answered Nov 04 '14 at 12:52

Jonathon

15,873
11
73
92

hmmm well its still not working. I actually did have this kind of typo before and the page won't even show out and having 500 error message. It probably is not the main issue maybe? – Joseph Nov 04 '14 at 12:59

score 0 · Answer 2 · answered Nov 04 '14 at 12:56

0

If you do a var_dump($_POST); you will see that your inputs have no values.

You need to specify the value on your form.

I would prefer to do this instead though:

echo '<input name="sku['.$row['sku_id'].']" value="'.$row['quantity'].'" />';

Then you can cycle through $_POST['sku'] and use the Key as the sku_id and the Value as the new value (quantity), to be updated

answered Nov 04 '14 at 12:56

user3202484

16
2

so the way to modify my code is change to `echo '';` instead of `echo '';` and remove the a tag from `echo ''.$row['sku_id'].'';` right? just making sure – Joseph Nov 04 '14 at 13:06
yes. If you do a var_dump of $_POST you will see the data from the form and better understand whats needed. e.g. $_POST['sku']['prod12']=12 This way also defaults to the current quantity, otherwise all your quanitites will be 0, wiping out you inventory. – user3202484 Nov 04 '14 at 13:24
just another quick question. Do I also have to modify the php that action after update? I only remove the `$qty = $_POST['qty'];` and try out but it seems like still not getting values, so I'm wondering. Sorry if I'm asking stupid question but I'm still learning. Thanks!! – Joseph Nov 04 '14 at 13:33

user3202484 · Accepted Answer · 2014-11-04T14:02:33.953

This should be a full answer for you (with mysqli update):

<form name="form" method="post">
<table width="70%" border="5" align="center">
<tr>
<th scope="row">SKU</th>
<th scope="row">Item Description</th>
<th scope="row">Quantity</th>
<th scope="row">Unit Price</th>
</tr>
<?php
include('connect.php');
$result = mysqli_query("SELECT * FROM products") 
        or die(mysqli_error());

while($row = mysqli_fetch_array( $result )) {
    echo "<tr>";
    echo '<td>'.htmlspecialchars($row['sku_id']).'</td>';
    echo '<td>'.htmlspecialchars($row['description']).'</td>';
    echo '<td><input name="qty['.htmlspecialchars($row['sku_id']).']" value="'.htmlspecialchars($row['quantity']).'"/></td>';
    echo '<td><input name="price['.htmlspecialchars($row['sku_id']).']" value="'.htmlspecialchars($row['unit_price']).'"/></td>';
    echo "</tr>"; 
    }
?>
</table>
<input style="float:right" name="update" type="submit" id="update" value="Update"/>
</form>
<?php

if(isset($_POST['update']))
{
    $qty = $_POST['qty'];
    $price = $_POST['price'];
    $stmt =  $mysqli->stmt_init(); // <- mysqli class way of doing this
    $stmt->prepare("UPDATE products SET quantity = ?, unit_price = ? WHERE sku_id = ?");
    foreach($qty as $key => $value) 
    {
        $data = array($qty[$key], $price[$key], $key);
        $stmt->execute($sql, $data);
    }
    echo 'Update data successfully!';
}
?>

For testing purposes the processing of post can be changed to:

if(isset($_POST['update']))
{
    $qty = $_POST['qty'];
    $price = $_POST['price'];
    //$stmt =  $mysqli->stmt_init(); // <- mysqli class way of doing this
    //$stmt->prepare("UPDATE products SET quantity = ?, unit_price = ? WHERE sku_id = ?");
    foreach($qty as $key => $value) 
    {
        echo "UPDATE products SET quantity = ".$qty[$key].", unit_price = ".$price[$key]." WHERE sku_id = " . $key . "<br/>\n";
        //$data = array($qty[$key], $price[$key], $key);
        //$stmt->execute($sql, $data);
    }
    echo 'Update data successfully!';
}

I just put it on server but it show 500 internal error.. Does it has something to do with `$sql = "UPDATE products SET quantity = ?, unit_price = ? WHERE sku_id = ?";` ? — Joseph, Nov 04 '14 at 13:49
I added in htmlspecialchars() to avoid code injection. What if one of your products had the description: also check out http://stackoverflow.com/questions/14011899/mysqli-real-escape-string-should-i-use-it — user3202484, Nov 04 '14 at 13:52
this may have something to do with the new mysqli code i've amended — user3202484, Nov 04 '14 at 13:54
ok the 500 error part is been fix but after I click update it shows an internal server error... — Joseph, Nov 04 '14 at 14:04
ok... I've tried the test and it actually returns the exact query that I wanted. That's weird. That me try if I can fix it or not — Joseph, Nov 04 '14 at 14:22
Just one more point. The update should be above the form. Otherwise you get presented with the data BEFORE it's been updated. — user3202484, Nov 17 '14 at 10:04

php update certain fields to database

3 Answers3