updating mysql table using php gives no change

Question

I'm trying to update a mySQL table using php. Basically I pass an ordernumber (int) and use it to find both isbn and quantityordered which I then use to update another table.

$orderID=$_POST["order_ent_number"];

$query="select ISBN from Orders where orderNumber='$orderID'";

$isbn = mysql_query($query) or die(mysql_error());


$query="select quantityOrdered from Orders where orderNumber='$orderID'";

$quantityordered = mysql_query($query) or die(mysql_error());



$query="UPDATE Books SET inStock='$quantityordered' WHERE inStock='0' AND isbn='$isbn'";

        $result = mysql_query($query) or die(mysql_error());

So using the MySQL bench, the query works (if I replace all variables with numbers) and changes it. The problem is when I use the variables in PHP, the code does not work. I tried the mysql_escape_string but that didnt work either. I checked the results of both variables $isbn and $quantityordered and they are right. I get no errors when I run it on the server but there is no change to inStock in the database. After searching around, someone said my variables need to be turned into integers? Not sure if this is correct or not but that is all I came up with. Any suggestions?

use mysqli instead of mysql... – Suhel Meman Apr 08 '13 at 06:12 — Suhel Meman, Apr 08 '13 at 06:12

score 1 · Answer 1 · edited May 23 '17 at 12:03

Actually you can do it in a single UPDATE statement by joining the tables,

UPDATE  Books a
        INNER JOIN Orders b
            ON a.ISBN = b.ISBN
SET     a.instock = a.quantityOrdered
WHERE   a.instock = 0 AND
        b.OrderNUmber = '$orderID' AND
        a.ISBN = '$isbn'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

score -1 · Answer 2 · answered Apr 08 '13 at 06:13

maybe you can try

$query="select ISBN from Orders where orderNumber='".$orderID."'";
$query="select quantityOrdered from Orders where orderNumber='".$orderID."'";
$query="UPDATE Books SET inStock='".$quantityordered."' WHERE inStock='0' AND isbn='".$isbn."'";

updating mysql table using php gives no change

2 Answers2