Working on mokocharlie.com is great. I get to exercise as much PHP muscle as I can on the project and get away with it because it’s considered progress and innovation (at least within the scope of the project). I find myself doing a lot of database work and hence updates and inserts and that sort of thing.

Now I have been doing PHP for a while now and for it’s shortcomings as compared to say python, it is quite convenient as a web scripting language. In other words you can get a lot of things done quickly without planning too much.

I have come to discover something while working on this project about certain nuances with the mysql wrapper in PHP which i think i should point out, now before any comments I would like to say that I know this is my opinion and I do understand why it’s implemented this way in the first place. I believe that when an UPDATE query is run within PHP just as other queries should return true or false based on success or failure respectively, I believe updates should return false as well if no rows are affected. I haven’t yet thought of the can or worms that that might open but it will save a lot of people a lot of embarrassment. Or perhaps it will allow a lot of PHP developers to write worse code. However you chose to look at it here’s is an example of updating a users password in a scenario where they have forgotten it or have requested to change it.

When an update is run it always returns true, unless there was a syntax or naming error somewhere in the query, so that

/*abridged for brevity */
   2:  $sql = “UPDATE `mytable` SET `mypassword` to ‘$newencryptedpassword’
WHERE `id` = `myid` and `email` = ‘$myemail’”;
   3:  if(mysql_query($sql)){
   4:  // means query was successful????
   5:  }else{
   6:  // there was a mysql_error()???
   7:  }

Now this, provided those variables and parameters are correct would return true all the time I have realised however that this might not always be the most desirable effect, as you might end up with a type 2 error for those of you savvy to the little trickeries of statistics.

to avoid this you will need to actually alter this little snippet to read like this.

   1:  $sql = “UPDATE `mytable` SET `mypassword` to ‘$newencryptedpassword’  
WHERE `id` = `myid` and `email` = ‘$myemail’”;
   2:  if(mysql_query($sql) && mysql_affected_rows() > 0){
   3:  // now this should really indicate that it was successful
   4:  }else{
   5:  //query failed
   6:  }

That should give you a means of being sure that the query actually did something and just doesn’t return true.

Tags: