[development] Problem with db_affected_rows() in MySQL

[sivaji j.g]

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);
        }

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.


-- 
Sivaji
+91 9941 571 690
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20100922/5767a283/attachment-0001.html