2

I'm trying to use a MySQL UPDATE query to update two items in a database. However, it only seems to be updating half of what is asked in the query.

The code I am using is as follows:

$db->query("UPDATE User_files SET Uploads=" . $rows['Uploads']-=1 . ", Files='" . $newfilesescaped . "' WHERE user=" . $escapeduser . "");

It updates half of the query, that being setting 'Uploads' to the correct number. However, the Files Column stays exactly the same as it was before.

I have both echoed out $newfilesescaped and also var_dumped it, both have returned the product I would like to be updated.

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
Minifrij
  • 33
  • 4

3 Answers3

1

This should work just fine:

$db->query("UPDATE User_files SET Uploads='". --$rows['Uploads'] ."', Files='". $newfilesescaped ."' WHERE user='" . $escapeduser ."'");

However I'd like to point out that like this you're probebly wide open to SQL injection. I'd suggest you take a look at prepared statements using either mysqli() or PDO().

icecub
  • 8,615
  • 6
  • 41
  • 70
  • No, this will update all rows in the database because the `-=` operator has a lower precedence than the `.` operator. – jeroen Nov 05 '14 at 17:21
  • This appears to have fixed my problem, so thank you. However, I am unsure how using this method opens me to SQL injection? Is prepared statements something that I have to look into? – Minifrij Nov 05 '14 at 17:29
  • @Minifrij Try reading here: http://stackoverflow.com/questions/2099425/when-we-use-preparedstatement-instead-of-statement Although it's about Java, it gives a good explanation why you should use Prepared Statements. I'm more then happy to give you an example of this, but you'll have to contact me on email (it's on my profile page) about that as it's not fit for a comment. – icecub Nov 05 '14 at 17:33
1

Your sql statement is wrong: The -= operator has a lower precedence than the . operator.

You should do your math outside of the sql statement or use something like:

"UPDATE User_files SET Uploads=" . --$rows['Uploads'] . ", Files='" . $newfilesescaped . "' WHERE user=" . $escapeduser

Now you will be concatenating strings on both sides of your -= before that operation is applied and your sql statement will not be what you expect it to be.

See here an example of both sql strings and the difference between the resulting sql statements. Note that what you have now will update the Uploads column of all rows in your table.

jeroen
  • 91,079
  • 21
  • 114
  • 132
  • 1
    I honestly did not know this. Thanks for explaining. Will correct my answer accordingly :) – icecub Nov 05 '14 at 17:21
  • 1
    Thank you for helping, I really didn't know what the -= would not work as it should, so that saved me a headache! – Minifrij Nov 05 '14 at 17:27
0

You have some syntax errors (you need to turn on error reporting)

$db->query("UPDATE `User_files` SET `Uploads`=" . $rows['Uploads']-=1 . ", `Files`='" . $newfilesescaped . "' WHERE user='" . $escapeduser . "' ");

You alse need to read Prevent SQL Injection!

Community
  • 1
  • 1
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119