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
INSERTstatements with multipleVALUESlists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-rowINSERTstatements. - When loading a table from a text file, use
LOAD DATA INFILE. This is usually 20 times faster than usingINSERTstatements. [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.
ASK a Question! |
|
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
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.
hi ……
thxs ….
Thanks a lot! The query to insert a select query worked perfect!
Welcome guys!
Thanks a lot, very useful article!
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……
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
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
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
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?
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
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
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