How to execute many mysql queries with PHP

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?

Use something like INSERT INTO example SELECT id FROM users to reduce all this to a single query.... see manual for details — Mark Baker
– Mark Baker, Commented Jan 24, 2015 at 15:51
One query, insert into someTable Select some records from some other table, No need to bring anything into php in your example. — Tony Hopkinson
– Tony Hopkinson, Commented Jan 24, 2015 at 15:52

Yaroslav Admin · Accepted Answer · 2015-01-24 16:00:01Z

3

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

edited Jan 24, 2015 at 16:00

answered Jan 24, 2015 at 15:52

Yaroslav Admin

14.7k6 gold badges65 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ipel Over a year ago

thanks, do you know how to count the affected rows too with insert..select ?

Yaroslav Admin Over a year ago

I think usual mysqli_affected_rows should do the trick. Check it I don't have mysql on current machine to give it a try.

fortune · Accepted Answer · 2015-01-24 15:57:12Z

3

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

answered Jan 24, 2015 at 15:57

fortune

3,3721 gold badge23 silver badges30 bronze badges

Comments

KyleK · Accepted Answer · 2015-01-24 15:53:09Z

0

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

answered Jan 24, 2015 at 15:53

KyleK

5,16920 silver badges35 bronze badges

2 Comments

Tony Hopkinson Over a year ago

No need to do this either, it's massively inefficient.

Tony Hopkinson Over a year ago

No pulling the data from the database server to the web server so you can do something in php is inefficient. If you can do it all on the database server, you should. Not a php thing, if you did the same thing with a c# client program and sql server, it would be just as bad.

Collectives™ on Stack Overflow

How to execute many mysql queries with PHP

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related