CODEWITHSUNDEEP
mysqldrupal
Jim Miller
Jim Miller

Reputation: 3329

Drupal 6 db insert: Strings are getting escaped

Getting driven crazy by this one...

I'm trying to insert a number of rows into a D6 database with a single db_query call. I've collected the rows as a set of strings, and have then collected them into one big string, something like so:

$theData = "(1, 2, 'a'), (3, 4, 'b'), (5, 6, 'c')";
db_query("insert into {table} (int1, int2, str) values %s", $theData);

($theData isn't typed like that in my code; it's the result of the code I've written -- a big string containing sets of values wrapped up in parens.)

When this runs, I get an error like:

You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 'insert into 
table (int1, int2, str) values (1,2,\'a\' at line 1 query: insert into 
table (int1, int2, str) values (1,2,\'a\'),(3,4,\'n\'),(5,6,\'c\')...

So, db_query or somebody else is escaping the strings before passing the values of to mysql. How do I keep this from happening? I could do individual queries for each set of data, but that's wrong/expensive for all the obvious reasons. Thanks!

Upvotes: 0

Views: 2271

Answers (1)

Nikit
Nikit

Reputation: 5128

$theDatas = array("(1, 2, 'a')", "(3, 4, 'b')", "(5, 6, 'c')");

foreach($theDatas as $data) {
  db_query("insert into {table} (int1, int2, str) values %s", $data);
}

But it's not recommend to do that, instead of this you should:

$theDatas = array(array(1, 2, 'a'), array(3, 4, 'b'), array(5, 6, 'c'));

foreach($theDatas as $data) {
  db_query("insert into {table} (int1, int2, str) values (%d, %d, '%s')", $data[0], $data[1], $data[2]);
}

Or you can serialize($theData) and put it "text" format field as one value, then use unserialize() for restoring array - this way is recommend if you want only store data (no searching, indexing etc).

Upvotes: 1

Related Questions