update query that Works directly in MYSQL but not in PHP

Question

I'm using mysqli on PHP to run a query that sums a value to an existing value as follows. I've simplified the query for clarity, normally I use variables for the different values. Obviously the OpenDBConnection is a function that I wrote myself and works perfectly elsewhere in the same script.

$sql="update TBL_Stock set STOCK_Cost = STOCK_Cost + 9 WHERE PO_ID='PO.16.030128'";
$con = OpenDBConnection();
$con->query($sql);

Now, when I execute the query directly in MySQL (using MySQL Workbench) it works perfectly, and 9 is added to the current value of STOCK_Cost. However, on PHP nothing happens, and 9 isn't added to STOCK_Cost.

The strange thing is that the following php bit DOES work:

$sql="update TBL_Stock set STOCK_Cost = 9 WHERE PO_ID='PO.16.030128'";
$con = OpenDBConnection();
$con->query($sql);

Obviously it doesn't do what I want the code to do, but in the last script the value of STOCK_Cost is set to 9.

So just adding changing the last bit to STOCK_Cost = Stock_Cost + 9 ... and it stops working. Any idea what's going on?

STOCK_Cost = Stock_Cost + 9 you are not putting correct column name. it should be STOCK_Cost = STOCK_Cost + 9 and your final query will be :- $sql="update TBL_Stock set STOCK_Cost = STOCK_Cost + 9 WHERE PO_ID='PO.16.030128'"; — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Mar 18, 2016 at 21:09
Is $con->query return true or `false? Have you checked for errors, php.net/manual/en/mysqli.error.php? — chris85
– chris85, Commented Mar 18, 2016 at 21:10
Very strange.Are you sure the transaction is being committed? Is "auto_commit" disabled ? Are you sure that PHP is connecting to the same database ? How are you sure that assignment is working ? Have you tried setting it to a different value, like 420 ? (All of the other things I can thing of, the datatype of STOCK_Cost column, BEFORE UPDATE triggers, etc. would all affect MySQL Workbench. Kudos for running tests from a different client. If it's not something goofy, like connecting to a different database, it has me stumped. (There's nothing "special" about a plus sign in a php string.) — spencer7593
– spencer7593, Commented Mar 18, 2016 at 21:30
At this point, I'm mostly suspicious of the report that SET STOCK_Cost = 9 query from PHP is actually working, and that the 9 you're seeing isn't from the test run in MySQL Workbench. I recommend you run a test that sets the value to something unique, and then check the results. (We're assuming that the OpenDBConnection function is performing the necessary checks to verify that the connection is valid.) — spencer7593
– spencer7593, Commented Mar 18, 2016 at 21:38

Marcel van der Werf · Accepted Answer · 2016-03-19 10:26:42Z

1

I've found the problem ... for security reasons I'd given the user very restrictive permissions on fields in the table ... the following is the grant for the table:

GRANT SELECT (PO_ID, STOCK_ProductID), UPDATE (STOCK_Cost) ON  etc.

It should of course be the following for the query to work.

GRANT SELECT (PO_ID, STOCK_ProductID, STOCK_Cost), UPDATE (STOCK_Cost) ON  etc.

The user on MySQL Workbench was a different one from the one I'm using in the PHP script, so that's why it did work there.

Silly me ... I was going crazy about this ... thanks to all of you that tried to help me out.

answered Mar 19, 2016 at 10:26

Marcel van der Werf

313 bronze badges

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

1 Comment

alexis Over a year ago

Marcel, you should "accept" your own answer (by clicking the big checkmark). Although your problem was particular to your circumstances, it could happen to others in the future.

John Weisz · Accepted Answer · 2016-03-19 10:35:55Z

0

Can it be that the PHP call is case sensitive? Are you using STOCK_Cost and writing "STOCK_Cost = STOCK_Cost + 9" exactly as the column is declared in the table schema?

edited Mar 19, 2016 at 10:35

John Weisz

32.2k14 gold badges97 silver badges138 bronze badges

answered Mar 18, 2016 at 21:11

Giovanni S. Fois

794 bronze badges

2 Comments

Marcel van der Werf Over a year ago

No it's not that. In the last paragraph of my question I typed the case wrong by mistake. But if you look at the actual code the case is correct (STOCK_Cost). As I've already mentioned, the query runs correctly directly in MYSQL, which is also case sensitive. I don't type the query but copy - paste it directly from the PHP editor I'm using.

Giovanni S. Fois Over a year ago

What is the definition for the STOCK_Cost column, int(10) varchar(255) ?

Collectives™ on Stack Overflow

update query that Works directly in MYSQL but not in PHP

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related