CODEWITHSUNDEEP
phpmysql
Iyama Yu
Iyama Yu

Reputation: 11

Cannot insert into mysql database

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?

Upvotes: 1

Views: 334

Answers (3)

samayo
samayo

Reputation: 16495

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");

Upvotes: 3

Daniel Morgan
Daniel Morgan

Reputation: 123

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());
}

Upvotes: 0

beiller
beiller

Reputation: 3135

 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");

Upvotes: 0

Related Questions