Help for db request in drupal - if else insert update exists

Question

I am trying to run a query in drupal that will update records if they already exist, or insert a new record if it does not work. The code is as follows:

db_query("IF EXISTS (SELECT %d FROM {uc_posten_packages.pid}) UPDATE {uc_posten_packages} SET title = '%s', label = '%s', cost = '%d', length = '%d', width ='%d', height = '%d', weight = '%d' WHERE pid = %d ELSE INSERT INTO {uc_posten_packages} VALUES ('%d', '%s', '%s', '%d', '%d', '%d', '%d', '%d')",$id, $title, $label, $rate, $length, $width, $height, $weight, $id, $id, $title, $label, $rate, $length, $width, $height, $weight); 

I do not understand why this request is causing an error. All numbers in the error are correct.

 ...near 'IF EXISTS (SELECT 1 FROM uc_posten_packages.pid) UPDATE uc_posten_packages ' at line 1 query: IF EXISTS (SELECT 1 FROM uc_posten_packages.pid) UPDATE uc_posten_packages SET title = 'vfbv', label = 'bbv', cost = '22', length = '232', width ='22', height = '22', weight = '22' WHERE pid = 1 ELSE INSERT INTO uc_posten_packages VALUES ('1', 'vfbv', 'bbv', '22', '232', '22', '22', '22') 

Should this request work and / or is there some better way to handle this in drupal?

Answer 1

What you are looking for is called "upsert" (update otherwise), and there is a drupal: db_merge function for this. Good entry here: http://drupal.org/node/310085

drupal_write_record () does not automatically execute upsert. It always either inserts or updates based only on what you are transmitting, but not both.

Answer 2

[UPDATE January 2013]

This answer applies to the old version of Drupal than to the current stable version. Please edit this answer by adding an update section similar to this one with more up-to-date information, as I no longer work with Drupal or lag behind its API changes.

-semperos

[/ UPDATE January 2013]

You have a couple of options, namely drupal_write_record or running a sample query and testing the results. This should not be done in a single SQL query, as in your question.

drupal_write_record (preferred method)

The drupal_write_record function allows drupal_write_record to specify the table you want to deal with and the object (or associated array) that contains data for each column / field of this table. If you already have a primary key for the row you are trying to update, you include it as the third parameter in the function, and drupal_write_record will automatically use the SQL UPDATE command. Otherwise, the default value is INSERT . For example:

 drupal_write_record('uc_posten_packages', array('title' => "Foobar", 'label' => "foobar", 'cost' => 10, 'length' => 100,)); 

This will be an INSERT new entry with this information. If you included the third argument to array($pid) , where $pid was the name of the field that acts as the primary key for the table, it would update instead.

This function will only work if the table you are working with has been defined using hook_schema , which should be true for any properly designed Drupal module that has database tables. Since this function uses a schema to ensure that it is written correctly to the database, you should use this function whenever possible (or when there are no other more specific functions, such as node_save for node objects).

Test request

You can simply run the sample query using db_result(db_query("SELECT...")) . It returns an empty string if no results are found, which is false in PHP, so your code might look like this:

 if (db_result(db_query("SELECT * FROM {uc_posten_packages} WHERE pid = %d", $pid))) { // UPDATE } else { // INSERT } 

Answer 3

According to drupal_write_record, the operation was UPDATE or INSERT , you need to know whether the record existed or not before the transaction. This is not what drupal_write_record () does. This function simply checks its arguments and corrects the update or insert accordingly. Thus, there is no way to tell whether an INSERT or UPDATE function was UPDATE .

The solution uses the db_merge () query, this is a relatively new ANSI SQL standard that does exactly what I need (and in the case of MySQL, it does this in an atomic transaction).

 $return_value = db_merge('mytable') ->key(array('id' => $key)) ->fields($fields) ->updateFields(array( 'updatedon' => $now, )) ->execute(); 

The return value is an integer that returns 1 for inserts and 2 for updates.

Answer 4

This is a stupid example of replacement syntax.

 mysql> create table test ( -> id int, -> a int, -> b int, -> unique (id) -> ) engine = myisam; Query OK, 0 rows affected (0.01 sec) mysql> replace into test (id,a,b) values (1,4,2); Query OK, 1 row affected (0.00 sec) mysql> replace into test (id,a,b) values (2,10,3); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +------+------+------+ | id | a | b | +------+------+------+ | 1 | 4 | 2 | | 2 | 10 | 3 | +------+------+------+ 2 rows in set (0.00 sec) mysql> replace into test (id,a,b) values (1,5,5); Query OK, 2 rows affected (0.00 sec) mysql> select * from test; +------+------+------+ | id | a | b | +------+------+------+ | 1 | 5 | 5 | | 2 | 10 | 3 | +------+------+------+ 2 rows in set (0.00 sec) 

Hope this helps you.