[development] correct way to update/insert a value in a table

[Larry Garfield]

In Drupal 5/6, we use update-or-insert.  That's because we're able to set the MySQL and Postgres drivers to return the number of rows *matched*, rather than the number of rows *changed*.  That does mean that, technically, db_affected_rows() is misnamed, but it maps to the underlying function name in PHP (which can toggle modes).

This has come up recently in the Drupal 7 database API development, because PDO does not let you return the number of rows matched, only the number changed.  That means the update-or-insert logic breaks if we try to set a record to the value it already has (which is not uncommon).  What we really need here is a merge query, but the syntax for that is wildly different on every database.  At the moment we are planning to introduce a new "merge" query builder that will replace the update-or-insert pattern with a single, database-optimized query (such as INSERT ... ON DUPLICATE KEY UPDATE ... on MySQL).  See the issue for more details: http://drupal.org/node/225450

So for now, yes, the logic shown below is what you should use.  

--Larry Garfield

On Mon, 31 Mar 2008 11:34:07 -0700, "mark burdett" <mfburdett at gmail.com> wrote:
> I happened to notice last night that db_affected_rows() is the number of
> matched rows in the WHERE clause, not the number of rows that were
> actually
> changed.  So (maybe only in drupal) if the data is the same these count as
> "affected" rows.
> 
> If you grep core for db_affected_rows you see that the logic is
>   db_query("UPDATE {variable} SET value = '%s' WHERE name = '%s'",
>   if (!db_affected_rows()) {
>     @db_query("INSERT INTO {variable} (name, value) VALUES ('%s', '%s')",
>   }
> 
> 
> --mark
> 
> On Mon, Mar 31, 2008 at 9:13 AM, Earnie Boyd
> <earnie at users.sourceforge.net>
> wrote:
> 
>> Quoting John Fiala <jcfiala at gmail.com>:
>>
>> > On Mon, Mar 31, 2008 at 6:43 AM, Lluís <enboig at gmail.com> wrote:
>> >> I need to save a value inside a table; I think the best/fast way of
>> >> doing so is:
>> >>
>> >
>> > Personally I like to keep track internally if a row's been saved to
>> > the db or not - but I've seen some folks try the UPDATE statement,
>> > call db_affected_rows to see if any rows were changed, and if none
>> > were, to do the insert.
>>
>> Doesn't work if data is the same; no rows will be updated and
>> db_affected_rows will return 0.  The method the OP used is correct
>> insert else update.
>>
>> Earnie -- http://for-my-kids.com/
>> -- http://give-me-an-offer.com/
>>
>>
> 
>