insert multiple rows via a php array into mysql

insert multiple rows into mysql with php using foreach arrays
php mysql insert multiple rows at once
how to insert multiple rows in mysql using php form
php using a for loop to do a multi row insert into mysql db
multiple insert php
mysql insert multiple rows from select
multiple inline insert into mysql using ajax jquery in php
how to insert multiple values in a single column in mysql using php

I'm passing a large dataset into a MySQL table via PHP using insert commands and I'm wondering if its possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of a mile long string and then executing it. I am using the CodeIgniter framework so its functions are also available to me.

Assembling one INSERT statement with multiple rows is much faster in MySQL than one INSERT statement per row.

That said, it sounds like you might be running into string-handling problems in PHP, which is really an algorithm problem, not a language one. Basically, when working with large strings, you want to minimize unnecessary copying. Primarily, this means you want to avoid concatenation. The fastest and most memory efficient way to build a large string, such as for inserting hundreds of rows at one, is to take advantage of the implode() function and array assignment.

$sql = array(); 
foreach( $data as $row ) {
    $sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_query('INSERT INTO table (text, category) VALUES '.implode(',', $sql));

The advantage of this approach is that you don't copy and re-copy the SQL statement you've so far assembled with each concatenation; instead, PHP does this once in the implode() statement. This is a big win.

If you have lots of columns to put together, and one or more are very long, you could also build an inner loop to do the same thing and use implode() to assign the values clause to the outer array.

Insert multiple rows via a PHP array into MYSQL, Sometime you want to pass a large dataset into a mysql table using PHP. Assembling one INSERT statement with multiple rows is much faster  Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. insert multiple rows via a php array into mysql. 0

Multiple insert/ batch insert is now supported by codeigniter. I had same problem. Though it is very late for answering question, it will help somebody. That's why answering this question.

$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name' ,
      'date' => 'My date'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name' ,
      'date' => 'Another date'
   )
);

$this->db->insert_batch('mytable', $data);

// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')

PHP MySQL Insert Multiple Records, Insert Multiple Records Into MySQL Using MySQLi and PDO. Multiple SQL statements must be executed with the mysqli_multi_query() function. The following  Sometime you want to pass a large dataset into a mysql table using PHP. Assembling one INSERT statement with multiple rows is much faster in MySQL than one INSERT statement per row. This sounds like you might be running into string-handling problems in PHP, if fact it is an algorithm problem.

You could prepare the query for inserting one row using the mysqli_stmt class, and then iterate over the array of data. Something like:

$stmt =  $db->stmt_init();
$stmt->prepare("INSERT INTO mytbl (fld1, fld2, fld3, fld4) VALUES(?, ?, ?, ?)");
foreach($myarray as $row)
{
    $stmt->bind_param('idsb', $row['fld1'], $row['fld2'], $row['fld3'], $row['fld4']);
    $stmt->execute();
}
$stmt->close();

Where 'idsb' are the types of the data you're binding (int, double, string, blob).

Php mysql insert multiple rows from array - PHP, Hello forums, I'm trying to copy a table data from one database into another. (​vbulletin Php mysql insert multiple rows from array · PHP leelong, you forgot extremely important thing - escaping values using mysql_real_escape_string() Assembling one INSERT statement with multiple rows is much faster in MySQL than one INSERT statement per row. That said, it sounds like you might be running into string-handling problems in PHP, which is really an algorithm problem, not a language one. Basically, when working with large strings, you want to minimize unnecessary copying.

I know this is an old query, but I was just reading and thought I'd add what I found elsewhere:

mysqli in PHP 5 is an ojbect with some good functions that will allow you to speed up the insertion time for the answer above:

$mysqli->autocommit(FALSE);
$mysqli->multi_query($sqlCombined);
$mysqli->autocommit(TRUE);

Turning off autocommit when inserting many rows greatly speeds up insertion, so turn it off, then execute as mentioned above, or just make a string (sqlCombined) which is many insert statements separated by semi-colons and multi-query will handle them fine.

Hope this helps someone save time (searching and inserting!)

R

Bulk Insert Update into MySQL using PHP, Bulk Insert Update data (multiple rows) into MySQL using PHP. Learn how to Insert large dataset into a MySQL table using INSERT command. Lets use array containing key value pair and implode statement to generate  Option 1: PHP Insert Array into MySQL Using Repetitive Insert Command. Here we will iterate on each row of data and execute separate insert command for each row. PHP. if(is_array($records)){ foreach ($records as $row) { $fieldVal1 = mysql_real_escape_string($records[$row][0]); $fieldVal2 = mysql_real_escape_string($records[$row][1]); $fieldVal3 = mysql_real_escape_string($records[$row][2]); $query ="INSERT INTO programming_lang (field1, field2, field3) VALUES

You could always use mysql's LOAD DATA:

LOAD DATA LOCAL INFILE '/full/path/to/file/foo.csv' INTO TABLE `footable` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' 

to do bulk inserts rather than using a bunch of INSERT statements.

Insert Multiple Rows Into MySQL The Right Way With PHP, Quite often when coding with PHP, there is a need to insert many items into a a an array in yousr code, a form submitting multiple values or many other sources. the query before INSERTing the data and using a single query to INSERT,  Insert Multiple Records Into MySQL Using MySQLi and PDO. Multiple SQL statements must be executed with the mysqli_multi_query() function. The following examples add three new records to the "MyGuests" table:

How to Enter PHP Array within MySQL Database, Hi, I have a form that asks the applicant for previous involvement. They have the option of entering up to three rows and after they press submit  How to insert array of data into mysql using php. Ask Question. Asked 6 years, 9 months ago. Active 1 year, 6 months ago. Viewed 101k times. 21. Currently I have an Array that looks like the following when output thru print_r (); Array ( [0] => Array ( [R_ID] => 32 [email] => a@a.com [name] => Bob ) [1] => Array ( [R_ID] => 32 [email] => b@b.com [name] => Dan ) [2] => Array ( [R_ID] => 32 [email] => c@c.com [name] => Paul ) [3] => Array ( [R_ID] => 35 [email] => d@d.com [name] => Mike ) )

Insert Multiple Rows Into A Mysql Table Using Php Array, Algorithms, Algorithms Quiz, Analysis, AngularJS, Aptitude, Arrays, Articles, Articles INSERT INTO statement is used to insert new rows in a database table. So to create a SQL query using the INSERT INTO statement with appropriate values, To do this, include multiple lists of column values within the INSERT INTO  This lets you insert data from a PHP array into your MySQL database table. It will insert multiple rows into the table, where each row will take values from one array element. Before inserting, you must prepare your data into an array consisting of records that you want to insert into the database.

PHP, The INSERT INTO .. SELECT statement can insert as many rows as you want. MySQL INSERT multiple rows example. Let's take an example of using the  Change your query to execute once, not once per row. It will be more efficient, and you can also have MySQL deal with counting number of inserts rather than doing it yourself.

Comments
  • I have given answer according to your question for Codeigniter's multiple row insert.
  • @SomnathMuluk Thank you, however it's been a while since I've needed to answer this question :)...
  • I would recommend using CodeIgniter's insert_batch function. If you use a library, always try to leverage it's strengths and coding standards.
  • I believe insert batch is the best way to do that see the link stackoverflow.com/questions/27206178/codeigniter-insert-batch
  • Thanks for that! Btw your missing a closing bracket on the end of the function if anyone is planning on copying it. mysql_real_query('INSERT INTO table VALUES (text, category) '.implode(','. $sql));
  • Thanks! Fixed. (I often do that...)
  • and the query should really be 'INSERT INTO table (text, category) VALUES '.implode(','. $sql)' sigh 4am coding leads to terrible debugging :(
  • I believe this code will create a solution for my latest project. My question here is, is this safe from SQL injection? My plans are to switch out mysql_real_escape_string with mysqli_real_escape_string and mysql_query with mysqli_queryas I'm using MySQLi and these have been deprecated as of PHP5. Many thanks!
  • mysql_* has been removed from PHP, so be sure to use the mysqli_* interface.
  • I think this is the most recommended way of doing multi row insert rather using mysql_query. Because when we use a framework it is a good practice to always use built-in features of the framework.
  • I recently ran some benchmarks comparing bulk insert and prepared insert statements as mentioned here. For around 500 inserts, the prepared inserts method completed in between 2.6-4.4 secs, and the bulk insert method in 0.12-0.35 secs. I would have thought mysql would have "bulked" these prepared statements together and perform just as well as the bulk inserts, but in a default set-up, the performance difference is huge apparently. (Btw all benchmarked queries were running inside a single transaction for each test, as to prevent auto-committing)
  • This is the error I got using your idea: "Fatal error: Call to a member function autocommit() on null in /homepages/25/d402746174/htdocs/MoneyMachine/saveQuotes.php on line 30"