insert datatable records into MYSQL database using PHP

Question

Possible Duplicate:
insert multiple rows via a php array into mysql

Apologize if i am wrong, My question is I have a PHP datatable with 50 rows i want to insert these 50 rows data into MYSQL database.Is there any query to insert multiple rows at a time. i am using codeigniter MVC framework

In short, i want to insert a PHP datatable into database

Thanks Inadvance

@Juhana Today there are 50 records,that my be increase to 100,1000 or 10000 so every time firing insert query inside loop is not good — Rajneel Joshi, Feb 20 '12 at 10:39
Well, you were asking *specifically* about 50 rows. If you're looking for an answer that scales up, you should mention that in the question. The answer can be completely different depending on the amount of data. — JJJ, Feb 20 '12 at 10:43
@Juhana:Apologize Juhana,i just gave an example,any solution will be appreciable. — Rajneel Joshi, Feb 20 '12 at 10:45

score 1 · Accepted Answer · answered Feb 21 '12 at 03:04

You will probably have an array with all your 50 data.

$data = array(
   array(
      'id' => '1' ,
      'data' => 'data-1'
   ),
   array(
      'id' => '2' ,
      'data' => 'data-2'
   )
    ...
    ...
    ...
   array(
      'id' => '50' ,
      'data' => 'data-50'
   )
);

In your codeigniter model class, you will probably have a function to insertBulk, for example like the following. That is you pass the above array to the function.

public function insertBulk($array) {
   $this->db->insert_batch('datatable', $array);
}

// which produces: INSERT INTO datatable (id, data) VALUES ('1', 'data-1'), ('2', 'data-2'), ...('50', 'data-50');

I have not tested for large array, but I do think you might need to check out the memory usage if the array goes too large.

@RajneelJoshi thank you for verifying. – Jasonw Feb 21 '12 at 07:16 — Jasonw, Feb 21 '12 at 07:16

score 0 · Answer 2 · answered Feb 20 '12 at 10:46

Mysql Dump Is what you want.

Below Link will helpo you out in understanding what you need to do..

MYSQL DUMP

AND Through PHP It can go like this

<?php

$db = mysql_connect ( 'localhost', 'username', 'password' );
mysql_select_db( 'database', $db );

$FP = fopen ( 'backup.sql', 'r' );
$READ = fread ( $FP, filesize ( 'backup.sql') );

$READ = explode ( ";\n", $READ );

foreach ( $READ AS $RED )
{
    mysql_query ( $RED );
}

echo 'Done';
?>

Replace the backup.sql with your file name. I used this to execute a phpBB mySQL dump into the database.

score 0 · Answer 3 · answered Feb 20 '12 at 11:03

You can batch inserts to MySQL from PHP, you still have to be careful about the batch size though. This is what they describe in the thread I linked to in my comment above. Per the manual, the format is:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
What would be even better is if the 2 databases are on the same physical machine, if that's the case you can do a cross-database INSERT INTO SELECT statement which will annihilate anything that large INSERT statement or a batch approach with a SQL dump because the data will never leave MySQL. (This should work cross-machine as well, but if that's the case it's probably best to use PHP or an SQL dump as previously suggested).

insert datatable records into MYSQL database using PHP

3 Answers3