[development] Problem with db_affected_rows() in MySQL

[Bob Hutchinson]

On Wednesday 22 September 2010, sivaji j.g wrote:
> Hi,
> 
> I am using support module for my current projects. This module often runs
> UPDATE SQL statement and checks db_affected_rows() before issuing INSERT
> statements like
> 
>         db_query("UPDATE {support_ticket} SET message_id = '%s', state =
> %d, priority = %d, client = %d, assigned = %d WHERE nid = %d",
> isset($node->message_id) ? $node->message_id : '', $node->state,
> $node->priority, $node->client, $node->assigned, $node->nid);
>         if (!db_affected_rows()) {
>           db_query("INSERT INTO {support_ticket} (nid, message_id, state,
> priority, client, assigned) VALUES(%d, '%s', %d, %d, %d, %d)", $node->nid,
> isset($node->message_id) ? $node->message_id : '', $node->state,
> $node->priority, $node->client, $node->assigned);
>         }

Better to do a SELECT first to check if a record with the nid concerned 
exists, if it does do UPDATE, if not do INSERT. 

db_affected_rows() will only return > 0 if an update results in changed data.

This assumes that nid needs to be unique in the support_ticket table.

> 
> I assume this is with an intention of avoiding duplicate records inserts.
> However in my case db_affected_rows() mostly returns 0 though a matching
> row is found in the table.
> 
> I guess it is because the values being updated is same as values already
> available in the row. As a result drupal throws user warning like.
> 
> user warning: Duplicate entry '292' for key 'PRIMARY' query: INSERT INTO
> support_ticket (nid, message_id, state, priority, client, assigned)
> VALUES(292, '', 1, 2, 7, 11) in /sites/all/modules/support/support.module
> on line 650.
> 
> I am planning to rewrite all the UPDATE query with SELECT COUNT(*) to fix
> this. Is there any other workaround available for this ?
> 
> I am using Drupal 6.19, PHP 5.3 and MySQL Server version:
> 5.1.41-3ubuntu12.6 (Ubuntu).
> 
> Thanks in advance.


-- 
-----------------
Bob Hutchinson
Midwales dot com
-----------------