Insert multiple rows mysql php pdo
Multiple Values Insert with PDO Prepared Statements Show Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts.
more data values or you probably have a loop that populates data. With prepared inserts you need to know the fields you're inserting to, and the number of fields to create the ? placeholders to bind your parameters.
That is basically how we want the insert statement to look like. Now, the code:
Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.
Dharman♦ 27.9k21 gold badges75 silver badges127 bronze badges answered Jan 20, 2010 at 2:30
Herbert BalagtasHerbert Balagtas 1,8771 gold badge12 silver badges6 bronze badges 9 Same answer as Mr. Balagtas, slightly clearer... Recent versions MySQL and PHP PDO do support multi-row SQL OverviewThe SQL will look something like this, assuming a 3-column table you'd like to
PHP OverviewYour PHP code will follow the usual
In the above example, it should contain 9 elements; PDO will use every set of 3 as a single row of values. (Inserting 3 rows of 3 columns each = 9 element array.) ImplementationBelow code is
written for clarity, not efficiency. Work with the PHP Assuming:
Sample Code
Dharman♦ 27.9k21 gold badges75 silver badges127 bronze badges answered Dec 30, 2010 at 1:24
jamesvljamesvl 1,60912 silver badges8 bronze badges 4 For what it is worth, I have seen a lot of users recommend iterating through INSERT statements instead of building out as a single string query as the selected answer did. I decided to run a simple test with just two fields and a very basic insert statement:
While the overall query itself took milliseconds or less, the latter (single string) query was consistently 8 times faster or more. If this was built out to say reflect an import of thousands of rows on many more columns, the difference could be enormous. answered Jan 31, 2012 at 23:28
JM4JM4 6,62218 gold badges75 silver badges123 bronze badges 4 The Accepted Answer by Herbert Balagtas works well when the $data array is small. With larger $data arrays the array_merge function becomes prohibitively slow. My test file to create the $data array has 28 cols and is about 80,000 lines. The final script took 41s to complete. Using array_push() to create $insert_values instead of array_merge() resulted in a 100X speed up with execution time of 0.41s. The problematic array_merge():
To eliminate the need for array_merge(), you can build the following two arrays instead:
These arrays can then be used as follows:
Dharman♦ 27.9k21 gold badges75 silver badges127 bronze badges answered May 19, 2012 at 21:18
Chris M.Chris M. 5034 silver badges4 bronze badges 6
Two possible approaches:
Or:
If the data for all the rows are in a single array, I would use the second solution. answered Jul 24, 2009 at 9:28
ZyxZyx 4753 silver badges8 bronze badges 3 That's simply not the way you use prepared statements. It is perfectly okay to insert one row per query because you can execute one prepared statement multiple times with different parameters. In fact that is one of the greatest advantages as it allows you to insert you a great number of rows in an efficient, secure and comfortable manner. So it maybe possible to implement the scheme you proposing, at least for a fixed number of rows, but it is almost guaranteed that this is not really what you want. answered Jul 24, 2009 at 9:04
sebasgosebasgo 3,83122 silver badges28 bronze badges 5 A shorter answer: flatten the array of data ordered by columns then
When inserting a 1,000 or so records you don't want to have to loop through every record to insert them when all you need is a count of the values. answered Dec 9, 2011 at 21:09
fyryefyrye 2693 silver badges3 bronze badges Here is my simple approach.
answered Mar 28, 2017 at 18:19 2 Here's a class I wrote do multiple inserts with purge option:
answered Jun 28, 2012 at 3:31
1 Based on my experiments I found out that mysql insert statement with multiple value rows in single transaction is the fastest one. However, if the data is too much then
mysql's
The most successful one in insert huge data scenario is Here's my research
The results for 100,000 entries for a table containing only two columns is as below
answered Aug 11, 2019 at 17:55
theBuzzyCodertheBuzzyCoder 2,4442 gold badges27 silver badges26 bronze badges Here is another (slim) solution for this issue: At first you need to count the data of the source array (here: $aData) with count(). Then you use array_fill() and generate a new array wich as many entries as the source array has, each with the value "(?,?)" (the number of placeholders depends on the fields you use; here: 2). Then the generated array needs to be imploded and as glue a comma is used. Within the foreach loop, you need to generate another index regarding on the number of placeholders you use (number of placeholders * current array index + 1). You need to add 1 to the generated index after each binded value.
answered Feb 7, 2020 at 22:18
BernhardBernhard 1,82710 silver badges18 bronze badges This is how I did it: First define the column names you'll use, or leave it blank and pdo will assume you want to use all the columns on the table - in which case you'll need to inform the row values in the exact order they appear on the table.
Now, suppose you have a two dimensional array already prepared. Iterate it, and construct a string with your row values, as such:
Now, what you just did was check if $rows was already defined, and if not, create it and store row values and the necessary SQL syntax so it will be a valid statement. Note that strings should go inside double quotes and single quotes, so they will be promptly recognized as such. All it's left to do is prepare the statement and execute, as such:
Tested with up to 2000 rows so far, and the execution time is dismal. Will run some more tests and will get back here in case I have something further to contribute. Regards. answered Mar 17, 2015 at 19:26
Théo T. CarranzaThéo T. Carranza 6,9961 gold badge20 silver badges14 bronze badges Since it has not been suggested yet, I'm pretty sure LOAD DATA INFILE is still the fastest way to load data as it disables indexing, inserts all data, and then re-enables the indexes - all in a single request. Saving the data as a csv should be fairly trivial keeping in mind fputcsv. MyISAM is fastest, but you still get big performance in InnoDB. There are other disadvantages, though so I would go this route if you are inserting a lot of data, and not bother with under 100 rows. answered Jan 26, 2017 at 2:11
Although an old question all the contributions helped me a lot
so here's my solution, which works within my own If you use a pattern that uses models this fits in nicely when passed model data as an array, say from a
answered Feb 19, 2017 at 9:50
LeeLee 3,83811 gold badges44 silver badges64 bronze badges 11 You can insert multiple rows in a single query with this function:
$row is an array of arrays of values. In your case you would call the function with
This has the benefit that you use prepared statements, while inserting multiple rows with a single query. Security! answered Mar 7, 2014 at 9:58
Here is my solution: https://github.com/sasha-ch/Aura.Sql based on auraphp/Aura.Sql library. Usage example:
Bugreports are welcome. answered Feb 7, 2015 at 20:30
1 My real world example to insert all german postcodes into an empty table (to add town names later):
As you can see its fully flexible. You don't need to check the amount of columns or check on which position your column is. You only need to set the insert data:
I'm proud of some of the query string constructors as they work without heavy array-functions like array_merge. Especially vsprintf() was a good find. Finally I needed to add 2x while() to avoid exceeding the memory limit. This depends on your memory limit but at all its a good general solution to avoid problems (and having 10 queries is still much better than 10.000). answered Mar 5, 2015 at 22:27
mguttmgutt 5,5072 gold badges49 silver badges74 bronze badges This worked for me
ROOT 11k5 gold badges30 silver badges43 bronze badges answered Oct 28, 2014 at 19:05
Most of the solutions given here to create the prepared query are more complex that they need to be. Using PHP's built in functions you can easily creare the SQL statement without significant overhead. Given
answered Sep 30, 2015 at 13:15
4 I had the same problem and this is how i accomplish for myself, and i made a function for myself for it ( and you can use it if that helps you). Example: INSERT INTO countries (country, city) VALUES (Germany, Berlin), (France, Paris);
If insertMultipleData($table, $multi_params) returns TRUE, your data has been inserted to your database. answered Sep 12, 2018 at 14:03
DardanDardan 5357 silver badges14 bronze badges 1 what about something like this:
The idea behind this is to cycle through your array values, adding "id numbers" to each loop for your prepared statement placeholders while at the same time, you add the values to your array for the binding parameters. If you don't like using the "key" index from the array, you could add $i=0, and $i++ inside the loop. Either works in this example, even if you have associative arrays with named keys, it would still work providing the keys were unique. With a little work it would be fine for nested arrays too.. **Note that substr strips the $sql variables last space and comma, if you don't have a space you'd need to change this to -1 rather than -2. answered May 26, 2020 at 3:38
How to insert multiple rows using PDO php?INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),.... $tblName - the string name of the table to INSERT to.. $colNames - 1-dimensional array of the column names of the table These column names must be valid MySQL column identifiers; escape them with backticks (``) if they are not.. How can I insert multiple rows in HTML table in MySQL using PHP?Inserting Multiple Rows into a Table. One can also insert multiple rows into a table with a single insert query at once. To do this, include multiple lists of column values within the INSERT INTO statement, where column values for each row must be enclosed within parentheses and separated by a comma.
How fetch data from database in PHP and display PDO?Fetch data from a result set by calling one of the following fetch methods: To return a single row from a result set as an array or object, call the PDOStatement::fetch method. To return all of the rows from the result set as an array of arrays or objects, call the PDOStatement::fetchAll method.
|