Is using mysql_num_rows (rowCount in PDO) necessary in update or insert query?

Question

Should I be using mysql_num_rows (rowCount in PDO) in update or insert query? Currently, my code looks likes this,

public function update_username(){
    $q = "UPDATE usertable SET username = '$user_name' WHERE id = '$user_id' LIMIT 1";
    $r = $db->query($q);
    if($r){
        $message = "Updated successfully";
        return $message;
    }else{
        return false;
    }
}

Should I change it to like this?

public function update_username(){
    $q = "UPDATE usertable SET username = '$user_name' WHERE id = '$user_id' LIMIT 1";
    $r = $db->query($q);
    if($r){
        $num = $r->rowCount();
        if($num == 1){
            $message = "Updated successfully";
            return $message;
        }else{
            $message = "An error occurred";
            return $message;
        }
    }else{
        return false;
    }
}

Normally, query goes through without any error, so I shouldn't worry about it too much, but which one would be a better coding practice? Or do you suggest something else?

Thanks so much in advance!

Zagorax · Accepted Answer · 2012-09-11T10:48:16.563

Actually the two codes do something different.

The first one will print "Update success" if the query was successfully executed. But a query can be successfully executed also without affecting any row, i.e. you have a WHERE statamenet that does not match. The second code will not print "Update success" if no rows were affected.

Of course, if you're sure that your WHERE statement has to match, you can use both codes without any difference and using the second one could help you to spot any potential bug, i.e. it doesn't match and so something went wrong (probably the id was different from the one you expected).

Generally, to answer your question, mysql_num_rows is needed only if you want to know how many lines were affected. It's not mandatory at all to use it.

So, it depends on what you want. Both are good, but they are different.

Mihai Iorga · Answer 2 · 2012-09-11T10:50:43.383

1

If you are 100% sure the variables are created by you and not someone else you can do it like that, but you can minimize the code more:

public function update_username(){
    $q = "UPDATE usertable SET username = '$user_name' WHERE id = '$user_id'";
    if($db->query($q)){
        return "Updated successfully";
    }
    return false;
}

edited Sep 11 '12 at 10:50

answered Sep 11 '12 at 10:40

Mihai Iorga

39,330
16
106
107

score 1 · Answer 3 · answered Sep 11 '12 at 10:54

First, because a query is executed successfully, doesn't necessarily mean that anything has been updated. So if you need to distinct the difference between a queries validity or the update change, then yes, rowCount would be a good practice.

Second, a prepared statement would be more wise to use when assigning variables (SQL injection, etc).

public function update_username(){
  $q = "UPDATE usertable SET username = :user_name WHERE id = :user_id LIMIT 1";
  $r = $db->prepare($q);
  $r->bindValue(':user_name', $user_name);
  $r->bindValue(':user_id', $user_id);
  if($r->execute()){
    $message = "Updated successfully: updated ".$r->rowCount();
    return $message;
  }else{
     return false;
  }
}

To avoid code duplication, maybe you should consider avoiding writing the same execution code for a query, and move that to a method/function which does that all for you, e.g

public function validateStmt($r) {
  // validate query
  if($r->execute()) {
    // check result set
    if($r->rowCount() > 0) {
      return $r;
    }
    else {
      // if a result set IS expected, then you might consider to handle this as
      // a warning or an error
    }
  }
  else {
    // query invalid
  }
}

score 1 · Answer 4 · answered Sep 11 '12 at 10:55

Depending on the situation, you will have to choose which part you should use. mysql_num_rows() is used to check how many rows have been affected from your query you have executed. So, it's up to you to decide whether it is really necessary to add the mysql_num_rows() function in to your code or not.

Is using mysql_num_rows (rowCount in PDO) necessary in update or insert query?

4 Answers4