correct way to update/insert a value in a table
I need to save a value inside a table; I think the best/fast way of doing so is: if (!db_query("INSERT INTO {wu_options} (vid, nid, name, value) VALUES (%d, %d, '%s', '%s')", $node->vid, $node->nid, $key, $value)) db_query("UPDATE {wu_options} SET value = '%s' WHERE vid = %d AND nid = %d AND name LIKE '%s'", $value, $node->vid, $node->nid, $key); but despite it works, it display an error. Is there any way of hidding a database error (similar to "@any_function(...) in PHP)? Or any other way to make drupal try to update a value, and if it don't exists, create it? thanks -- *Envellim quan els records superen les esperances. *Als llocs desconeguts només s'hi arriba per camins desconeguts.
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. -- John Fiala
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/
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/
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/
Quoting Lluís <enboig@gmail.com>:
I need to save a value inside a table; I think the best/fast way of doing so is:
if (!db_query("INSERT INTO {wu_options} (vid, nid, name, value) VALUES (%d, %d, '%s', '%s')", $node->vid, $node->nid, $key, $value)) db_query("UPDATE {wu_options} SET value = '%s' WHERE vid = %d AND nid = %d AND name LIKE '%s'", $value, $node->vid, $node->nid, $key);
but despite it works, it display an error. Is there any way of hidding a database error (similar to "@any_function(...) in PHP)? Or any other way to make drupal try to update a value, and if it don't exists, create it?
It's not PHP it's Drupal's db_query [1][2][3]. There appears to be a variable 'error_level' that can be set [3] or you can empty the ``$_SESSION['messages']'' variable[4]. [1] http://api.drupal.org/api/function/db_query [2] http://api.drupal.org/api/function/_db_query [3] http://api.drupal.org/api/function/drupal_error_handler [4] http://api.drupal.org/api/function/drupal_set_message Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
participants (5)
-
Earnie Boyd -
John Fiala -
Larry Garfield -
Lluís -
mark burdett