PDO multirow insert with a multidimensional array

[Bldyhll]

Hello all
 
First of all nice to meet you since this is my first post. I have been reading already for a longer time but so far I managed to always fix my issues.
Currently I am having the other way around so I registrated and decided to ask for help.
 
The goal is to push multiple insert into the database on the quickest possible way, Originally I did a loop insert but performance dies rather hard that way.
I got the code to the point that it is working without errors however it only inserts the latest row and not all of them.
Basicly that's the reason for my raging (hence the name) and my question do you guys. Should I take the hammer for my own head or my server ?

 

Thanks in advance

 

Bldyhll

 

 

$db->beginTransaction();
                    $stmt = $db->prepare("INSERT INTO scripts (val1,val2, val3,val4) VALUES ('',:val2,:val3,:val4) ");
                         while($j < count($fullarray)){
                          $stmt->bindParam(":val2", $fullarray[$j][0]);
                          $stmt->bindParam(":val3", $fullarray[$j][1]);
                          $stmt->bindParam(":val4", $fullarray[$j][2]);
                          $j++;
                       }

$stmt->execute();

$db->commit();

 

[mac_gyver]

The goal is to push multiple insert into the database on the quickest possible way,

 

 

the quickest possible way is to use a multi-value insert query. running a prepared query inside of a loop only provides minimal speed improvement (a fraction of second improvement for thousands of rows that take multiple seconds to insert) over running a non-prepared query inside a loop since most of the time is taken up by the handshaking and transmission of information between php and the database server to send each query/set of data for the prepared query. i did a benchmark some time-ago and the only significant way to speed up inserting/updating large amounts of data is to use the multi-value form of an insert query.

 

---

 

for your current code, the bindParam() statements need to be outside of and before the while() loop. the variable you supply as the second parameter to the bindParam() must be a reference to where the actual data is at when the ->execute() method is called. the clearest way would be to use three distinct variables in the bindParam() statements, i.e. $a, $b, $c, then assign $a = $fullarray[$j][0]; $b = $fullarray[$j][1] ; $c=.$fullarray[$j][2] ; inside the while(){} loop code.

 

next, $stmt->execute(); needs to be inside the while(){} loop code.

 

lastly, you should also put the count($fullarray) statement outside of and before the while() loop so that it is not re-evaluated each time the while() condition is evaluated and you will probably want to initialize $j to zero before the start of the while() loop.

[Bldyhll]

Well I am going to be honest, you kind of lost me.. if I start googling about multi-value insert query then it is different from my current code so I would have to rewrite it.

So what I did now is the following :

 

 

   

// add data with a string

$fullsql.= "('','".$val1."','$val2','$val3'),"; //this is done in a loop in order to make one giant sql statement

// transform to one sql statement

$fullsql =  rtrim($

$fullsql = "INSERT INTO table (id,val1, val2,val3) VALUES $fullsql ";

//execute

$stmt = $db->prepare($fullsql);
$stmt->execute();

 

So if I understood it right this is supposed to be the fastest way in order to do it?

[mac_gyver]

that would result in the fastest method, except, by putting the data values directly into the sql query statement, there's no point in using a prepared query followed by calling the ->execute() method (which takes two 'round trip' communications with the database server.) you might as well just run the query using the ->query() method and save some time communicating with the database server.

 

also, by putting the data values directly into the sql query statement, you must now escape string data and validate/cast numerical data to prevent sql injection and prevent query errors when string data contains sql special characters or numerical data isn't what you expect it to be.

 

edit: also since your starting data is already in an array, it would typically be faster to use php array functions (array_map() to build the sql syntax for each row and implode() to combine all rows) to build the multi-value portion of the query. you can end up with code that contains no actual loop statement, with no need to trim any commas from the end.

[Bldyhll]

Mmm some of my values contain single quotes but real_escape en pdo don't seem to work together. Imploding the data won't help with that correct?

[Bldyhll]

Update, I managed to get the code running and working fast (or so I believe) . The following code is the end result, while not perfect it seems to do the job relative okay.

 

 

  //this code below is done in a loop to make the full string

  $fullsql.= "('','".mysql_escape_string($val1)."','$val2','$val3'),";    

//begin the execution
    $db->beginTransaction();
    $fullsql =  rtrim($fullsql,",");
    $fullsql = "INSERT INTO table (id,val1, val2,val3) VALUES $fullsql ";
    $stmt = $db->prepare($fullsql);
    $stmt->execute();
    $db->commit();

 

In order to give an idea, executing the script and putting 24 rows in the table is done witht he following time 1.67 real         0.07 user         0.02 sys
 

[mac_gyver]

your last code, using mysql_escape_string(), has a number of problems -

 

1) you should not mix mysql_ functions with PDO functions. this also implies that you have made a second database connection using  mysql_connect(), resulting in two database connections. you should be using the PDO ->quote() method on string data.

 

2) mysql_escape_string() doesn't take into account the character set being used and can allow sql injection.

 

3) all the mysql_ functions are depreciated and should not be used in new code.

 

4) as stated, there's no point in using ->prepare() and ->execute() when you are putting values directly into the sql query statement. just use the ->query() method.

 

5) you must validate/cast/escape all three values being put into each row. what exactly are the data types of val1, val2, and val3?

 

6) i didn't bother to mention this before, but you should not put the id into the list of fields since it is an auto-increment column. this will eliminate the need to supply a value for that column, which will further speed up the process since the amount of data/length of the sql query statement is reduced.

 

edit: my reference to using array functions, means to take any loop out of your code and use php's array functions to operate on each set of data. you can use array map (typically in a class) to cast/validate/escape all three pieces of data and to build the ('v1','v2','v3') string for each element in your data array. you can then simply implode those resulting values to produce the  ('v1a','v2a','v3a'), ('v1b','v2b','v3b'),... portion of the query.

[Psycho]

@Bidyhll,

 

A couple quick critiques:

 

1) You will do yourself a great favor by making your variable/field names meaningful values. Using 'val1', 'val2', 'val3', etc. may make sense to you now as you are writing the code. But, if you have to come back to the code after a week or more you will waste time trying to figure out what they mean.

 

2) When iterating through an array use a foreach() loop instead of a for() loop where you try to manipulate a variable to equal the index of each item in the array.

 

In response to mac_gyver's first post here, this is how your original code should have looked more like this:

//Start transaction
$db->beginTransaction();
 
//Create prepared statement
$stmt = $db->prepare("INSERT INTO scripts (val2, val3,val4) VALUES (:val2, :val3, :val4)");
 
//Bind the parameters outside the loop using unique variable names
$stmt->bindParam(":val2", $val2);
$stmt->bindParam(":val3", $val3);
$stmt->bindParam(":val4", $val4);
foreach($fullarray as $record)
{
    //Set the variables used in the bind statements
    $val2 = $record[0];
    $val2 = $record[1];
    $val2 = $record[2];
    //Run prepared statement for current record
    $stmt->execute();
}
 
//Commit the changes
$db->commit();

Also, it is possible to create a prepared statement for a variable amount of insert records. That way you don't have to manually escape values based upon the variable type. Example

//Start transaction
$db->beginTransaction();
 
//Create query string for prepared statement for variable record count
$PLACEHOLDERS = array_fill(0, count($fullarray), "(?, ?, ?)");
$query = "INSERT INTO scripts (val2, val3,val4) VALUES " . implode(", ", $PLACEHOLDERS);
 
//Create the prepared statement
$stmt = $db->prepare($query);
 
//Put values into single-dimensional array
$VALUES = array();
foreach($fullarray as $value)
{
    $VALUES = array_merge($VALUES, $value);
}
 
//Execute the preparesed statement for ALL the values
$stmt->execute($VALUES);
 
//Commit the changes
$db->commit();

Edited July 29, 2014 by Psycho

[Bldyhll]

As first note, thanks for the information. It is painfull that my coding got this bad but I am gratefull for it.

 

@mac_gyver

 

I am trying the pdo->quote methode but it doesn't seem to work because the string has a lot of single and double quotes. I known the mysql_escape_string doesn't account the charset but the data is not user inputted. It was the only way I found though to get the script working without setting up an additional db connection (no need for the mysql_connect with the simple function). The mysql functions are how I started with php but I will note that I can erase that out of my mind .

val1 is a string which will almost always contain html code, val2 and val3 are hashes from these strings (I know one hash is enough but I really do not wish to allow a chance for a collision). The tip about the autoincrement was also nice there I am adjusting all my code atm to scrap that out of them.

 

@psycho

No worries they do have right names here just prefer to be carefull because I know my code is bad and don't want somebody to google my website and suddenly find code scraps. I might try your implode function in order to fix my html like string from value1, it is the main issue