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
– baao, Commented Nov 4, 2014 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
– adnan kamili, Commented Nov 4, 2014 at 13:29
Thanks for the recommendation! I'll find out how exactly does PDO works! — Joseph
– Joseph, Commented Nov 4, 2014 at 13:51

Jonathon · Accepted Answer · 2014-11-04 12:57:12Z

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 4, 2014 at 12:57

answered Nov 4, 2014 at 12:52

Jonathon

16.4k12 gold badges78 silver badges98 bronze badges

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

1 Comment

Joseph Over a year ago

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?

user3202484 · Accepted Answer · 2014-11-04 14:02:33Z

0

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!';
}

edited Nov 4, 2014 at 14:02

answered Nov 4, 2014 at 13:37

user3202484

162 bronze badges

6 Comments

Joseph Over a year ago

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 = ?"; ?

user3202484 Over a year ago

I added in htmlspecialchars() to avoid code injection. What if one of your products had the description: <script>alert('hacked');</script> also check out stackoverflow.com/questions/14011899/…

user3202484 Over a year ago

this may have something to do with the new mysqli code i've amended

Joseph Over a year ago

ok the 500 error part is been fix but after I click update it shows an internal server error...

Joseph Over a year ago

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

|

user3202484 · Accepted Answer · 2014-11-04 12:56:16Z

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 4, 2014 at 12:56

user3202484

162 bronze badges

3 Comments

Joseph Over a year ago

so the way to modify my code is change to echo '<td><input name="sku['.$row['sku_id'].']" value="'.$row['quantity'].'" /></td>'; instead of echo '<td><input name="qty[]" /></td>'; and remove the a tag from echo '<td><a name="sku[]">'.$row['sku_id'].'</a></td>'; right? just making sure

user3202484 Over a year ago

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.

Joseph Over a year ago

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!!

Collectives™ on Stack Overflow

php update certain fields to database

3 Answers 3

1 Comment

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related