By igrcic on

Hiya to all!

I got one question about a webform module. Well it's not about a module but about query inside module:

this is the following query:

  db_query("INSERT INTO {webform_submitted_data} (nid, sid, name, data) ".
           "VALUES ".implode(', ', $sqlstring), $values);

where $sqlstring and $values are set before like arrays:

...
    $sqlstring[] = " (%d, %d, '%s', '%s') ";
    $values[] = $node->nid;
    $values[] = $sid;
    $values[] = $key;
    $values[] = $value;
  }
...

So the actuall query would look something like this:

 INSERT INTO webform_submitted_data (nid, sid, name, data) VALUES  
(84, 1, 'Name', 'name') ;  
(84, 1, 'Last Name', 'lastname') ; 
 (84, 1, 'I have my own bag/tent to sleep in?', 'Nope, you''ll have to find me one') ;  
(84, 1, 'comments', 'where are we going from?') ;  
(84, 1, '__userid', '3') ;  
(84, 1, '__timestamp', '1119198796') ;  
(84, 1, '__remotehost', '193.198.8.211') ;  
(84, 1, '__useragent', 'Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.7) Gecko/20050414 Firefox/1.0.3')

That works fine for mysql, i guess, but for Postgresql I have to write INSERT INTO every time i want to insert something. Is there a way, a function like db_query but for multiple queries? Didn't find any in documentation.

I can just write a loop for 8 queries, but than I'd had to inolve transactions maybe?? Can anyone tell me how to solve this problem quick and ellegant, that would work fine for PG and MYSQL?

Tnx very much

Comments

diroussel’s picture

diroussel commented

You can do this using PostgreSQLs COPY statement.

See http://www.postgresql.org/docs/8.0/interactive/sql-copy.html

basically it reads data from a file or stdin. I think you can use the same data format as INSERT in 7.4, or if you have 8.0+ you can used CSV format.

Cvbge’s picture

Cvbge commented

I think it's also possibile to several sql statesments in one db_quer(). Something like:
db_query("INSERT INTO ... VALUES (...); INSERT INTO ... VALUES (...); etc")
If one query fails then all queries fail (IIRC, would need to be checked).