0

I am trying to update a row in mySql database. However I only want to update specific parameters, which are not null:

db->prepare("UPDATE table SET userFirstName = $userFirstName, userLastName = $userLastName WHERE xx = $xx");

this is what I do for now, but it may be that userFirstName does not need updating, or userLastName,... Since I have may values I need a way to say something like:

if userLastName is not "null" then update even that...

Alessandro
  • 4,000
  • 12
  • 63
  • 131
  • **Building SQL statements with outside variables makes your code vulnerable to SQL injection attacks.** Also, any input data with single quotes in it, like "O'Malley", will blow up your query. Learn about parametrized queries, preferably with the PDO module, to protect your web app. [This question](http://stackoverflow.com/questions/60174) has many detailed examples. See also http://bobby-tables.com/php for alternatives & explanation of the danger. Running SQL code built with outside data is like eating soup made from ingredients found on your doorstep. – Andy Lester Feb 11 '15 at 20:03

1 Answers1

2

MySQL is smart enough to determine whether to update the row or not. If it sees that the value to be updated with matches the value currently in the column it will skip rewriting it, and even if it didn't it's not really something you should care about anyway. I've had quite a bunch of these optimization obsessions myself and I can tell you from experience, they don't bring you anything good.

There is a greater problem with your code. Your use of prepare is senseless. Your SQL is still vulnerable. Search for prepared statements and mysql injection.

However to answer the question as is - you should use an object as a database mapper. They make your life a whole lot easier. Basically the idea is that you have an object that represents a row of your table, then it keeps track of which properties have been modified and when you call $object->save() it knows exactly which fields to update.

php_nub_qq
  • 15,199
  • 21
  • 74
  • 144
  • thanks for your reply, however this code is run in a method and the method call should specify the update parameters. When I call the method I may just want to update the username, so I set the others to null. Thats the only way... – Alessandro Feb 12 '15 at 00:13
  • @Alessandro so what is the problem? – php_nub_qq Feb 12 '15 at 10:17