CODEWITHSUNDEEP
phpmysqlloopswhile-loopsplit
ipel
ipel

Reputation: 1348

How to execute many mysql queries with PHP

Let's say I have a mysql table called "users", and that often I have to insert a record in another mysql table for each one of my user.

For example:

<?php
$result = mysqli_query( $c,"SELECT user_id FROM users WHERE '$search'" );
while( $array = mysqli_fetch_array( $result ) ) {
  // execute a query for each users
  mysqli_query( $c,"INSERT INTO example ( id, user_id, record1, record2, date ) VALUES ( NULL, '$array[user_id]', '$record1', '$record2', '$date' )" );
  $count++;
}
echo "$count rows added";
?>

If I have 1'000 or 10'000 users everything works great.

But I was wondering, if I had 100,000 users (or even more) how should I do to avoid server errors / memory limit?

There is a way to split my script?

Upvotes: 1

Views: 596

Answers (3)

Yaroslav Admin
Yaroslav Admin

Reputation: 14535

Consider using INSERT ... SELECT command.

<?php
    $result = mysqli_query( $c,"INSERT INTO example ( id, user_id, record1, record2, date ) SELECT NULL, user_id, '$record1', '$record2', '$date' FROM users WHERE '$search'" );
?>

Upvotes: 3

fortune
fortune

Reputation: 3372

Use mysql INSERT .... SELECT 

mysqli_query($c,"INSERT INTO example (id, user_id, record1, record2, date)
SELECT NULL, user_id, '$record1', '$record2', '$date' FROM 
users WHERE '$search'");

Upvotes: 3

KyleK
KyleK

Reputation: 5056

Never execute a query in a big loop, 1000 is already a very bad idea:

<?php
$result = mysqli_query( $c,"SELECT user_id FROM users WHERE '$search'" );
$insert = array();
while( $array = mysqli_fetch_array( $result ) ) {
  // execute a query for each users
  $insert[] = "( NULL, '$array[user_id]', '$record1', '$record2', '$date' )";
}
mysqli_query( $c,"INSERT INTO example ( id, user_id, record1, record2, date ) VALUES " . implode(', ', $insert) );
echo count($insert) . " rows added";
?>

Upvotes: 0

Related Questions