Insert 10000 records in MYSQL using php

[tapuwa2002]

Hi guys

I was hoping you could help I would like to add 10000 records in mysql db.All once with after running a single php script. in one of the fields should generate 10000 rand generated numbers. Any suggests would really help. I was thinking of a for loop.

<?php


for ($i=1; $i<=10000; $i++)
  {
$code=rand(10000,999999);
$name=Peter.rand(1000,9999);
  mysql_query("INSERT INTO Persons (id,FirstName, LastName, Age)
VALUES (NULL,'$name', 'Griffin', '35','$code')");
  }
?>

Please help

[Danny159]

Does what you have there not work... thats how I would do it but it might crash the server?

[Danny159]

This line should be

$name = "Peter" . rand(1000,9999);

Use mt_rand() instead of rand() - It's 4 times quicker according to the php manual. Could prevent a timeout?

Would that make your script execute 8 times as quickly. I'm not sure.

[mschroeder]

Stop doing the insert in the loop. This is easily solved by using the loop to generate the insert query and then execute 1 query instead of 10000.

PDO Prepared Statements
Executes 10000 statements
Execution Completed: 10.082677841187 seconds

$db = new PDO( 
    'mysql:host=127.0.0.1;dbname=examples', 
    'username', 
    'password', 
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") 
); 

$sth = $db->prepare('INSERT INTO persons (id, FirstName, LastName, Age, Code) VALUES (NULL, :FirstName, :LastName, :Age, :Code )');

for( $i=1; $i<=10000; $i++ ){
	
	$code = rand(10000,999999);
	$name = 'Peter'.rand(1000,9999);
	
	$sth->bindValue( ':FirstName', $name );
	$sth->bindValue( ':LastName', 'Griffin' );
	$sth->bindValue( ':Age', '35' );
	$sth->bindValue( ':Code', $code );
	$sth->execute();
}

PDO Single Query
Executes 1 query
Execution Completed: 0.28580498695374 seconds

$db = new PDO( 
    'mysql:host=127.0.0.1;dbname=examples', 
    'username', 
    'password', 
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") 
); 

$inserts = array();
$queryFormat = '(NULL,"%s", "Griffin", "35", "%d")';

for ($i=1; $i<=10000; $i++)
{
	$code = rand(10000,999999);
	$name = 'Peter'.rand(1000,9999);	
	$inserts[] = sprintf( $queryFormat, $name, $code );	
}

$query = implode( ",", $inserts );

$db->query( 'INSERT INTO persons (id,FirstName, LastName, Age, Code) VALUES '.$query );

I made a few changes. The name "Peter" was not quoted, I also added a column for "Code' to my query as I think it was forgotten since the insert has a value for it.

Concept applies to all database drivers, but with PDO you'll have the most flexibility. If these values are coming from the end user, prepared statements will provide a much more secure database interaction than using the values directly in the query without first sanitizing/validating them.

[Awais_2]

Hello Dear,
I am not a good php developer, but nowadays a got a problem relating to above.
I have to fetch the 10000 records from excel file, then save them to mysql database using php.

Now using pdf excel libraries i can fetch the data from excel file, but due to too much records the browser gives error, (time out)

I want to know how to manage that scenario.
Regards.

[cereal]

@Awais_2 hello, please: follow the community rules, open a new thread and share the code that is giving you the issue.