Cannot insert into mysql database

Question

I'm trying to insert data to database. There is no error, but the database is still not being updated. In the database, the product table contains: productid, categoryid, productname, productimage, stock, price and the detailsales table contains: transactionid, productid, and quantity

The update one is working, but the insert one is not working at all.

Here is my code:

            <form action="doShop.php" method="post">
            <table border="1">
                <tr>
                    <td>Product ID</td>
                    <td>Product Name</td>
                    <td>Product Image</td>
                    <td>Price</td>
                    <td>Product Stock</td>
                    <td> Quantity</td>
                </tr>

                <?php
                    $query = mysql_query("SELECT * FROM Product");
                    while($row = mysql_fetch_array($query)){
                ?>
                <tr>
                    <td><input type="text" value="<?=$row['ProductID']?>" name="productid"></td>
                    <td><?=$row['ProductName']?></td>
                    <td><img src="image/<?=$row['ProductImage']?>"></td>
                    <td><?=$row['Price']?></td>
                    <td><input type="text" value="<?=$row['Stock']?>" name="stock"></td>
                    <td><input type="text" name="quantity"></td>
                </tr>

                <?php
                    }
                    print mysql_error();
                ?>
            </table>
            <table>
            <tr><input type="submit" value="Submit"></tr>
            </table>
            </form>

Here is the doShop code:

                <?php
            include("connect.php");

            $id=$_REQUEST['productid'];
            $quantity=$_REQUEST['quantity'];
            $stock = $_REQUEST['stock'];

            mysql_query("insert into DetailSales(ProductID, Quantity)values('".$id."','".$quantity."')");
            mysql_query("update product set stock = $stock - $quantity where detailsales.productid = product.productid");
            header("location:shopcart.php");
            ?>

Can anyone help me?

How do you know there is no error? You're not checking mysql_error(). — andrewsi
– andrewsi, Commented May 17, 2013 at 15:03
I see this type of question all the time, you should read common database debugging for PHP and MySQL. — Jason McCreary
– Jason McCreary, Commented May 17, 2013 at 15:06
Lovely SQL injection attack vulnerabilities. Enjoy having your server pwn3d. — Marc B
– Marc B, Commented May 17, 2013 at 15:08
if you want to be sure a query is working go to your mysqld.log, fetch the exact query mysql is receiving and paste it on your phpmyadmin query console. — Naryl
– Naryl, Commented May 17, 2013 at 15:08

samayo · Accepted Answer · 2013-05-17 15:13:34Z

3

You were trying to carry out calculations inside your query, instead I created a separate variable called $total to handle that for, you.

Like this:

 $total = $stock - $quantity;

Instead of this:

SET stock = $stock - $quantity

So, change the doshop code to this:

  <?php
      include("connect.php");
      $id = $_REQUEST['productid'];
      $quantity = $_REQUEST['quantity'];
      $stock = $_REQUEST['stock'];
      $total = $stock - $quantity;

      mysql_query("INSERT INTO DetailSales(ProductID, Quantity)
                   VALUES ('".$id."','".$quantity."')") or die(mysql_error());

      mysql_query("UPDATE product SET stock = '$total'
                   WHERE detailsales.productid = product.productid") 
                   or die(mysql_error());

     header("Location: shopcart.php");

edited May 17, 2013 at 15:13

answered May 17, 2013 at 15:07

samayo

16.5k13 gold badges95 silver badges115 bronze badges

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

1 Comment

Naryl Over a year ago

exactly what I was about to write

beiller · Accepted Answer · 2013-05-17 15:08:18Z

0

 mysql_query("update product set stock = $stock - $quantity where detailsales.productid = product.productid");

I believe this line is wrong and should be

 mysql_query("update product set stock = ".($stock - $quantity)." where detailsales.productid = product.productid");

answered May 17, 2013 at 15:08

beiller

3,1351 gold badge14 silver badges20 bronze badges

1 Comment

Marc B Over a year ago

sql can do math. update foo set field=10-4 will set field to 6.

Daniel Morgan · Accepted Answer · 2013-05-17 15:10:32Z

0

Try this way instead;

$sql = ("insert into DetailSales(ProductID, Quantity)values('".$id."','".$quantity."')");
$res = mysql_query($sql);

if($res) {
    mysql_insert_id();
} else {
    die(mysql_error());
}

answered May 17, 2013 at 15:10

Daniel Morgan

1231 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

Cannot insert into mysql database

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related