Quoting through a large dataset in PHP

Question

I have an array with personal information of 100,000 users (ID, name, email address, etc.). I need to go through each row of the array and insert the mysql record into the table based on the row data. My problem is that my memory runs out after about 70,000 lines.

My code is:

if(!empty($users)){ $c = 0; foreach($users as $user){ $message = // Some code to create custom email queue_mail_to_send($user->user_email, $subject, $message, $db_options, $mail_options, $mail_queue); } } 

Background:

I am creating a mail system that sends an email to users of my site. The above code iterates over the user array and executes the function 'queue_mail_to_send', which inserts the mysql string into the email queue table. (I use the PEAR library to mark up email sending)

Question:

I know that I'm just exhausting my memory here, trying to do too much in one performance. So does anyone know a better approach to this, rather than trying to do everything in one big loop?

thanks

Answer 1

I think script payload reduction will be cumbersome and not give you a satisfactory result. If you have such an opportunity, I would advise you to register which lines you have already processed, and the script to run the following lines x. If you can use cronjob, you can create mail, and let cronjob add messages to the queue every 5 minutes until all users are processed.

The easiest way is to store somewhere, the highest user id that you processed. I would not advise you to keep the number of users, because between parties the user can be added or deleted, as a result, users will not receive an email. But if you order by user ID (provided that you use an auto-incrementing column for the identifier!), You can be sure that each user will be processed.

So, your user request will look something like this:

 SELECT * FROM users WHERE user_id > [highest_processed_user_id] ORDER BY user_id LIMIT 1000 

Then process your loop and save the last user id:

 if(!empty($users)) { $last_processed_id = null; foreach($users as $user) { $message = // Message creation magic queue_mail_to_send( /** parameters **/ ); $last_processed_id = $user->id; } // batch done! store processed user id $query = 'UPDATE mail_table SET last_processed_user_id = '. $last_processed_id; // please use parameterized statements here // execute the query } 

And the next time you do it again, until all users receive the mail.

Answer 2

I have the same problem with you. Anyway, the answer from @giorgio is the best solutions.

But, like java or python, we have "yield" in php. @see [here] ( http://php.net/manual/en/language.generators.syntax.php )

Here is my sample code, my case is 50,000 entries. and I also successfully tested 370,000 records. But it takes time.

 $items = CustomerService::findAll(); foreach ($items AS $item) { yield (new self())->loadFromResource($item); } 

Answer 3

You can divide this operation into several operations, divided by time. For example, allow your program to process 40 letters per minute, or perhaps use an array of arrays to create “pages” of records (use the LIMIT sql function). And set the array arrays to zero and cancel it when you no longer need this information.

Answer 4

I think you can use the MySQL IN clause and then do foreach for each user.

Like user_ids = array (1,2,3,4); // Do something WHERE user_id IN ($ user_ids);

and sending emails, you can use the PHPMailer class by providing comma-separated email addresses in $ to.

Answer 5

USE only one query:

 INSERT INTO table_name (COL1, Col2,...) SELECT COL1, COL2 FROM other_table;