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

Recommended Answers

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

Jump to Post

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 …
Jump to Post

All 6 Replies

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

This line should be

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

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.

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.

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.