PHP - using update with variables

Question

I am using php to update a mysql DB within a function. I am able to echo my variable names and was able to return the variables on the php page. This proves to me that my variables are working correctly.

Now when I use the update command, my DB does not respond. Yes, I have connected to the DB and it all works.

This is what I am using to update:

mysql_query("UPDATE `table_name`
            SET `int_field` = '$int_value'
            WHERE `username` = $username");

After query, you should check the result, from the error, you will find the wrong part easily. For you case, you missed the quotes for `$username` — xdazz, Sep 30 '12 at 04:56
Swap the string-delimeters from the int to string perhaps?.. mysql_query("UPDATE table_name SET int_field = $int_value WHERE username = '$username'"); — enhzflep, Sep 30 '12 at 04:58

score 3 · Accepted Answer · edited May 23 '17 at 12:08

3

The value for $username should be wrap with single quotes.

mysql_query(" UPDATE table_name 
                 SET int_field = '$int_value' 
               WHERE username = '$username'");

SideNote: your code is vulnerable with SQL Injection. Please read the article below to know how to secure your code,

Best way to prevent SQL injection in PHP?

edited May 23 '17 at 12:08

Community

1
1

answered Sep 30 '12 at 04:55

John Woo

258,903
69
498
492

Thanks, that had worked using quotes around $username. And thanks for the sql injection link. I am using a sanitize function on all field variables as well. – Slap Shot Sep 30 '12 at 05:04
@SlapShot you are welcome :D and Welcome to StackOverFlow Community! – John Woo Sep 30 '12 at 05:05

score 1 · Answer 2 · answered Sep 30 '12 at 05:27

You need to quote all of your input to the query. This prevents SQL injection, but also simple syntax errors that would occur if your user innocently inputs a special character that would break your query.

mysql_query('UPDATE table_name '.
            'SET int_field = "'.mysql_real_escape_string($int_value).'" '.
            'WHERE username = "'.mysql_real_escape_string($username).'"');

score 1 · Answer 3 · edited Jan 03 '16 at 05:58

1

This is the correct STRUCTURE of how to update with a variable in php

mysql_query("UPDATE tablename SET password='". $NPass ."' WHERE custID='$num'");

edited Jan 03 '16 at 05:58

Yu Hao

119,891
44
235
294

answered Jan 03 '16 at 05:29

Kingsley Mitchell

2,412
2
18
25

score 0 · Answer 4 · answered Sep 30 '12 at 05:00

0

Try this :

         mysql_query("UPDATE `table_name`
         SET `int_field` = '$int_value'
        WHERE `username` = '$username'");

answered Sep 30 '12 at 05:00

score 0 · Answer 5 · answered Jan 28 '14 at 07:43

0

I had the same problem and found that the SET value doesn't need single quotes and that the WHERE clause does ...

mysql_query("UPDATE table_name SET int_field=$int_value
WHERE username='$username'");

.. seemed to work for me.

answered Jan 28 '14 at 07:43

ballsDeep

1

PHP - using update with variables

5 Answers5