CODEWITHSUNDEEP
phpmysqlpdo
BEDI
BEDI

Reputation: 41

insert multiple rows in database using pdo's

insert into test (sometext) values ("?"),("?")
$a= array("weird' text","sdfa");

I want to insert text into the table test in column sometext using bind parameter and I do not want the execute statement in a loop. I cannot implode the array in ("?"),("?") form as the query might crash coz the text can be composed of quotes.

So is there a way to achieve this using PDO in one(1) execute statement?

Upvotes: 0

Views: 77

Answers (2)

kamlesh
kamlesh

Reputation: 38

$stmt = $conn->prepare("INSERT INTO test (field1, field2, field3) VALUES (?, ?, ?)");

$stmt->bind_param("sss", $field1, $field2, $field3);

// set parameters and execute

$field1 = "test";

$field2 = "test2";

$field3 = "[email protected]"; $stmt->execute();

Upvotes: 0

fusion3k
fusion3k

Reputation: 11689

I cannot implode the array in ("?"),("?") form as the query might crash coz the text can be composed of quotes.

The prepared statements are there to solve quoting/escaping problems.

This syntax is wrong1:

insert into test (sometext) values ("?"),("?")

You don't have to wrap parameters by quotes, you have to write query in this form:

INSERT INTO test (sometext) VALUES (?),(?)

Then, you can use implode() without worrying about quotes:

$a     = array( "weird' text", "sdfa" );
$query = "INSERT INTO test (sometext) VALUES (" . implode( "),(", array_fill( 0, count( $a ), "?" ) ) . ")";
$stmt  = $db->prepare( $query );
$stmt->execute( $a );

As alternative, you can use substr and str_repeat instead of implode:

$query = "INSERT INTO test (sometext) VALUES " . substr( str_repeat( "(?),", count( $a ) ), 0, -1 );

1 Using insert into test (sometext) values ("?"),("?") you insert in your fields literally two question marks.

Upvotes: 1

Related Questions