Home » PHP/MySQL, Programming Techniques » Multiple Insert in Single Query – PHP/MySQL

Often, we come across situations where we need to INSERT multiple rows in to a database table from an Array, File or even from another Database Table. I am explaining various methods for inserting multiple rows using a single query using MySQL and PHP.

MySQL documentation says you can use the following methods to speed up inserts:

  • If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.
  • When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements. [This method is described below]

It is always better to multiple insert in a single query than calling multiple insert queries. It reduces the resource usage and increases the speed of execution.

Method 1 – Copy rows from one table to another table:

This method can be used when you want  to fill-up a certain MySQL table with data off other tables. You really would not need to copy all the fields from one to another. You can restrict the fields by specifying it in your SELECT statement. See a sample code below.

//
query("INSERT INTO orders (pid, quantity, price) SELECT productid, qty, price FROM cart WHERE sid=19;");

Here, we are moving records from the Cart table to Orders table. The ID column, which is a PRIMARY KEY and Auto Increment field, will be updated automatically.

Method 2 – Insert multiple rows in to a table from an Array

This is simple example showing how to insert data from an array in to multiple rows in a table. This is the structure of array we have.

Array
(
    [0] => Array
        (
            [0] => First
            [1] => Content
        )
    [1] => Array
        (
            [0] => Second
            [1] => Something
        )
    [2] => Array
        (
            [0] => Third
            [1] => Some Text
        )
)

This is an array of arrays, where each array contains multiple values. What we do normally is loop through the array and run one INSERT query for each element of the array by joining the values. A better idea would be to join and form a single query statement and run it as a single Query to insert all the records together.

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. The values list for each row must be enclosed within parentheses. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Now, let’s form a query using out data array and insert the values from the array using just a single query.

<?php
$multi = array(
				array("First", "Content"), //Array for a row of fields
				array("Second", "Something"),
				array("Third", "Some Text"),
				array("Another", "Another Content")
			);
$new = array();
foreach($multi as $key=>$value) {
	$new[] = "'".implode("', '", $value)."'";
}
$query = "(".implode("), (", $new).")";
mysql_query("INSERT INTO duplicate (title, body) VALUES ".$query.";");
echo "Inserted successfully";
die;
?>

Method 3 – Insert rows from a text file in to a table using "LOAD DATA INFILE"

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.

$file = $_SERVER['DOCUMENT_ROOT']."\\data.txt";
query("LOAD DATA INFILE '".addslashes($file)."'
	  	INTO TABLE tblname
		FIELDS TERMINATED BY ','
		LINES TERMINATED BY '\r\n'
		(title, body) SET id = NULL;");

Here, the data file is located in the document root of your server. The file has multiple rows of data delimited by a carriage return which is ‘\r\n’. Fields in a row are delimited using comma ‘,’. The ‘id’ is set to Null because it is an Auto

Increment field and value will be inserted automatically. Below is the content of my data file.

//File: data.txt
title1, body1
title2, body2
title3, body3

Please let me know if you have any queries.

19 Comments

  1. charlie says:

    Hi Aneeska,

    Great post, I have one question. When inserting an array how do you append a customer_id onto the INSERT query? I’m working on inserting one customers bookmarks onto another where I need to be able to use the customer_id so that they are added to the correct member.

    Thanks,

    Charlie

  2. Aneeska says:

    Hello Charlie,

    When you insert multiple rows using a single query in MySQL, it doesn’t allow you to SET values explicitly. In this case you will have to hardcode the customer_id in the inner arrays.

    If you are copying from a table to another you can try this method.

    query(“INSERT INTO duplicate (customer_id, title, body) SELECT 3, title, body FROM messages;”);

    Please let me know if you still have any questions.

  3. Raghupathi says:

    hi ……
    thxs ….

  4. Pau says:

    Thanks a lot! The query to insert a select query worked perfect!

  5. Thanks a lot, very useful article!

  6. Ragavan says:

    i need multiple upload file and save file path saved in same id

    example

    id description filepath
    1 upload upload/a.zip,upload/b.zip,upload/c.tar,upload/rose.jpg

    I need source can u help me……

  7. Aneeska says:

    Hi Ragavan,

    I assume you would be storing your filenames in an array like this.

    $files = array(‘upload/a.zip’, ‘upload/b.zip’, ‘upload/c.zip’);

    Now you can run this query:

    mysql_query(“INSERT INTO table_name VALUES(NULL, ‘$description’, ‘”.join(“, “, $files).”‘)”);

    Hope this helps. Feel free to contact me if you have any questions.

    Thanks,
    Anees

  8. Ujang says:

    hi there,

    I have some problem to creating sript to upload multiple image on my mSQL.
    i asumsing like this.

    $stfile = “../file/data.txt”;

    and the data on that txt shown like this
    a b c d e and blablabla
    my uploaded image will be in same folder with extentions .png or jpeg.

    now i need to open this file txt to read the file and give them extentions .png

    now the table row is (‘id’ , ‘image’)
    value id will be from “data.txt” exp : a b c d e
    and value image will be the extentions from data.txt value exp : a.png b.png c.png

    i think there’s no problem to add value id from data.txt
    but i cant made a simple script to add value image
    i have to explode it one by one.

    please kindly help is there any one know how to made some increment for exp: $insert[0], $insert[1] and blablabla so that i just creating a simple increment like $xxx+= or $xxx++
    it should use a new statement switch or somethink….?

    thanks

  9. Aneeska says:

    Ujang,

    I am sorry to say that I didn’t understand you. Just explain me your requirement. I will sure help you out.

    Thanks,
    Anees

  10. Aneeska says:

    Send me your sample code and I will better understand it.

    Do you want to write a text file out of your database table? What is the relation between the image files, the text file and the database table?

  11. Ujang says:

    thanks aneska
    take a look bellow,

    data on file.txt :
    ++++++++++++++++++++++++++++++
    a
    b
    c
    c
    +++++++++++++++++++++++++++++
    here you are the sample codes
    ***************************************************************
    $file = “file.txt”;
    $fsize=@filesize($file);
    $instr = fopen($file,”r”);
    $str=@fread($instr,$fsize); // first read file.txt and put it to ‘id’ rows
    // how it will be like this?
    $input = (“flag/”.$str.”.png”);// and than give extentions to read image file to put in on ‘image’ rows.

    $instr = fopen($input,”rb”);
    $image = addslashes(fread($instr,filesize($input)));

    $putdata =”INSERT INTO flag (`id`, `img`) VALUES (‘$str’, ‘$image’)”;
    mysql_query($putdata, $koneksi)
    or die (“error”);
    echo (“OK File InsertedThanks”);
    ***********************************************************************

    or it will be simple like this
    i will chage data on file.txt to
    (‘a’, file/’a.png’)
    (‘b’, ‘file/b.png’)
    (‘c’, ‘file/e.png’)
    (‘d’, ‘file/d.png’)
    how to explode the code so it can be read image file, in simple word make an array on it.

    thanks once again

  12. Ujang says:

    by the way there is something in this page.

    take alook :
    normal : HELLO WORLD
    bold : HELLO WORLD
    H1 :HELLO WORLD

    do you accepting tags code here

  13. Aneeska says:

    Create your text file in this format.

    {“a”:”file/a.png”, “b”:”file/b.png”, “c”:”file/c.png”}

    Now use the below code to read and process the content.

    < ?php
    $data = file_get_contents('json.txt');
    $array = json_decode($data, true);
    print_r($array);
    ?>

    This code now reads the file and converts the content of the file in to a PHP array. You can now loop through the array to process the values. I hope it helps. If I am still not clear, please feel free to ask.

    Regards,
    Anees

  14. blasteralfred says:

    Hi,
    I have a form that is submitted to a php file which insert into sql table. I need to add multiple entries with the same data into sql table in a single instance, with the help of a field entry (to determine the number of times the data have to be inserted) in the form itself. In short, I need to lessen the work done by multiple form submissions with the same data.
    Thank You.
    Alfred Santa Molison

  15. Philippe says:

    Hi, first thank’s for these script,

    Is there any way to list a photo folder and add all the files name into mysql database ?

    an old beginger ’45 old ‘ in php coding

    Phil

  16. corey says:

    I am trying to accomplish your method 2 example. I am new to this and I’m hoping that if you have the time and I explain this well enough, you can provide assistance. The disconnect for me is structuring an html form correctly to send the values to method 2. Here is my form structure.

    Entry 1:
    Item Bracket ID (same for all entries)
    Item Region (same for all entries)
    Item name
    Item id (this is an auto incrementing number in this structure – t_01)
    Item description
    Item Image

    Entry 2:
    Item Bracket ID (same for all entries)
    Item Region (same for all entries)
    Item name
    Item id (this is an auto incrementing number in this structure – t_02)
    Item description
    Item Image

    and so on….

    So, I’m not certain at how to set the values in the form fields to create the arrays I need to post the multiple inserts.

    cw

    }

  17. Harish says:

    Hi Aneesh…

    Thanks….but i have one doubt…
    i have to insert multiple rows values into table and have to generate the only one unique id for each submit…(not for the multiple rows…)how can i do that..?

Leave a Reply

Page optimized by WP Minify WordPress Plugin