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@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@users.sourceforge.net> wrote:
Quoting John Fiala <jcfiala@gmail.com>:
On Mon, Mar 31, 2008 at 6:43 AM, Lluís <enboig@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/