如何用PHP执行许多mysql查询

Question

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?

Answer 1

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'" );
?>