I have a query like UPDATE user SET creator = 'creator'". The "user" table has not the "creator" column, so db_affected_rows() shoud return −1, not 1.


Update: mysql_affected_rows() returns -1 for this query. I created a "creator" column (unique); after I update the user table, I get the error "User warning: Duplicate entry," but db_affected_rows() still returns 1.

  • just fyi: db_affected_rows() calls mysql_affected_rows() and not mysql_num_rows() - both both should return -1 when the query fails
    – Alex Weber
    Commented Apr 23, 2011 at 13:15

2 Answers 2


Edit: db_affected_rows() returns an unexpected value because watchdog is turned on. I watched to see if there was another query after the one I ran and before my db_affected_rows() call, and sure enough:

INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', '%message in %file on line %line.', 'a:4:{s:6:\"%error\";s:12:\"user warning\";s:8:\"%message\";s:104:\"Unknown column 'name2' in 'field list'\nquery: UPDATE users SET name2 = 'a'\"; [...]

So, db_affected_rows() is returning 1 because watchdog is logging an error. Thus, the proper way to see if a query had an error or not -- and count the rows affected if it was successful -- is:

$result = db_query("UPDATE users SET name2 = 'a'"); 
if ($result) {
  printf("Records affected: %d\n", db_affected_rows());
else {
  print "Query not successful";

Hope this helps!

Original answer for posterity:

I'd have to see the code around your query. Try running this:

function db_affected_rows() {
  global $link;
  return mysql_affected_rows($link);

$link = mysql_connect('localhost', 'root', 'root');
if (!$link) {
    die('Could not connect: ' . mysql_error());

/* this should return the correct numbers of deleted records */
mysql_query("UPDATE user SET creator = 'creator'");
printf("Records affected: %d\n", mysql_affected_rows());

printf("Records affected: %d\n", db_affected_rows());

I did and it worked alright:

$ php -f test.php

Records affected: -1

Records affected: -1

  • after 2 days i see edited reply and one post in your blog :) .thank u so much Entendu
    – user780
    Commented Apr 27, 2011 at 9:32

Try to reproduce that outside of Drupal with just mysql_query(), mysql_affected_rows() and so on.

This might be an issue with your PHP version or something like that. Drupal just passes the call forward, it doesn't do anything ony it's own.

  • thank u. please check this code it return a error and "1" <?php db_query("UPDATE users SET name2 = 'a'"); printf("Records affected: %d\n", db_affected_rows());
    – user780
    Commented Apr 24, 2011 at 4:44
  • Wow, that does return "1". I'm digging in and will update my answer.
    – Entendu
    Commented Apr 25, 2011 at 2:33
  • K, figured it out.
    – Entendu
    Commented Apr 25, 2011 at 2:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.