Something's wrong in UPDATE query

Question

I have an edit form. I'm fetching the data for the row like this -

if (isset($_GET["id"])) {
    $pur_id = intval($_GET["id"]);
} else {
    echo "id not set";
}

Then I'm trying to execute the following UPDATE query.

$qry1 = mysqli_query($con,"UPDATE `purchase_info` SET `remarks` ='$remarks' WHERE `pur_info_id` = '$pur_id'");

This query does work but it doesn't update the records in my database. But it updates the record when I simply pass numeric value instead of the variable that I'm putting my row ID in. So if I pass 'pur_info_id' = 1 then it updates my record.

It's very odd. I'm converting the value using int value but still no luck. Please advice. Thanks!!

Have you tried to remove the quotes around `'$pur_id'` so just only `$pur_id` becasue like you said `$pur_id` is an `int` not a `string` and by adding quotes you say it is a string — SuperDJ, Apr 03 '16 at 09:33
@Reversal Yes it should but in the end he is making a string of a int which isn't the propor way of doing it — SuperDJ, Apr 03 '16 at 09:36
@SuperDJ I've tried removing the quotes but it doesn't work. — Khairul Alam, Apr 03 '16 at 09:46
If it works with a hard coded constant value, then something is wrong with your `$pur_id` or `$_GET["id"]`. `echo` the the query, to see what's in it. — Paul Spiegel, Apr 03 '16 at 10:07
@PaulSpiegel That is the confusion because when I var_dump $pur_id, I get the value. — Khairul Alam, Apr 03 '16 at 10:28

Tim Biegeleisen · Answer 1 · 2016-04-03T09:44:56.250

3

Your query is non-sensical, because when it actually does an UPDATE it replaces the pur_info_id column with the value it already had. Suppose $pur_id has the value 1, then your query can be written as:

UPDATE `purchase_info`
SET `pur_info_id` = 1,
    `remarks` = '$remarks'
WHERE `pur_info_id` = 1

Well this will not change the pur_info_id column, but remarks might get updated to something new. You should let us know what business logic you intend to carry out. In any case, the following query is logically equivalent to the original one you had:

UPDATE `purchase_info`
SET `remarks` = '$remarks'
WHERE `pur_info_id` = '$pur_id'

edited Apr 03 '16 at 09:44

answered Apr 03 '16 at 09:36

Tim Biegeleisen

502,043
27
286
360

my business logic is same as you've explained. I need to update a few columns where pur_info_id = 1 (assuming). I don't definitely need to SET the pur_info_id column here. I've tried removing that but the query still doesn't work. – Khairul Alam Apr 03 '16 at 09:53
1

`echo` out what the actual query string is, then run it in standalone MySQL. Does the query actually behave the way it should? – Tim Biegeleisen Apr 03 '16 at 10:29
I get the expected result when I echo $_SERVER["QUERY_STRING"] and when I run the query in standalone MySQL, the query works too. – Khairul Alam Apr 03 '16 at 10:38
I had in mind defining a query string in your PHP code, rather than inlining the query, then echoing it out and running that directly from MySQL. – Tim Biegeleisen Apr 03 '16 at 10:42
Sorry Tim, but I'm not yet much experienced and I clearly don't know how to do that. – Khairul Alam Apr 03 '16 at 10:58
You need to see exactly which query is being fed into `mysqli_query()`. If this query runs on raw MySQL without any problem, but does not in your PHP code, it might imply a configuration problem. – Tim Biegeleisen Apr 03 '16 at 11:00

score 0 · Accepted Answer · answered Apr 04 '16 at 05:02

Thanks everyone for sharing your valuable insights and knowledge. I've found the solution to my problem. It's a very silly problem, new for me though. I fetched the row id from table using _GET request, and then tried to use the id in my _POST request which actually caused the problem.

We cannot use the values of _GET in _POST request in short. So the solution is to store the value of the _GET in a variable, keep that in a hidden input field. And when the _POST request is made, we simply take the value from the hidden field. Thanks once again!!

Something's wrong in UPDATE query

2 Answers2

Linked