PHP Multiple Value String in Single Query

Question

I've got a HTML Textarea with 100-1000 of User:Password pairs each line and I want to insert them now in my database using PDO. Since I am really new to PDO I need your help with it and maybe you know some optimaziations for a faster insert or easier code.

This could be the content of my textarea:

User1:Pass1
User2:Pass2
User3:Pass3

And this is what I tried with:

$query = "INSERT INTO Refs (username,password,targetLevel,idOrder) VALUES :accounts";
$ps_insert = $db->prepare($query);

//this iy my textarea from the form
$accounts = $_POST['accounts']; 

// Convert Accountaray to user:password tuples
// Write the SQL Statement of the VALUE pairs into
// an string array in this style '(user,password)'  
$msg = explode("\n", $accounts);
for($i = 0; $i < sizeof($msg); $i++) 
{ 
        list($user, $pass) = explode(":", $msg[$i]);
        if(!empty($user)){
            $insert_parts[$i]="('" . $user . "','" . $pass . "',10,0)";
        }
}

// Content of this string is: (user1,pass1,10,0), (user2,pass2,10,0), (user3,pass3,10,0)
$accountInserts = implode(',', $insert_parts);
$ps_insert->bindParam(':accounts', $insert_parts);
$ps_insert->execute();

Earlier I used the "well known" MySQL Queries, but I want to use PDO because I will use it for other things as well with common prepared statements. Thanks for the help!

Problem: The MySQL Insert doesn't work. How should I solve this? Any suggestions for (speed/code) optimizations?

possible duplicate of [PDO Prepared Inserts multiple rows in single query](http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) — newfurniturey, Aug 09 '14 at 02:52

spencer7593 · Answer 1 · 2014-08-11T15:14:11.340

The bind argument specifies a single scalar value. In your INSERT statement, the bind placeholder :account represents the value assigned to one column. You can only supply data values through a bind variable, you can't include parens and commas to be interpreted as SQL text.

If you supply a value for a bind placeholder, such as:

"('foo','bar',7,1)"

That will be interpreted as a single value, that won't be "seen" as SQL text, but just a character string, the value to be assigned to a single column.

You'd need a SQL statement that looks something like this:

INSERT INTO Refs (username,password,targetLevel,idOrder) VALUES (:v1, :v2, :v3, :v4)

And you'd need to supply a value for each bind placeholder.

But then its not a single query with multiple values. When I add 10k Queries with this I need to wait 2-3 Seconds until this query is finished. Or did I missunderstood your solution ? — kentor, Aug 09 '14 at 11:52

PHP Multiple Value String in Single Query

1 Answers1