db_affected_rows() return "1" when the query is wrong

Question

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.

Why?

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.

Answer 1

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:

<?php
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());
}
mysql_select_db('d6.devl');

/* 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

Answer 2

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.