php - mysql querying in batches -

php - mysql querying in batches -

i have on 1 1000000 rows in table have query through , send emails users based on query in cron job. have code like:

$start = 0; $limit = 1000; while ( true) { $query_results = db_result("select * table limit $start, $limit"); if (count($query_result) == 0) { break; } foreach ( $query_result $result) { $email = $result ['email']; send_email($email); } $start += $limit; }

is there improve more efficient way query through millions of rows in batches? , if script crashes avoid sending duplicate emails people? if script crashes , gets re-run again, send emails remaining people without me having db lookup in code above check if each person got email?

the email gets sent every day. might sent of same people every day depending on whether each person fulfilled requirements or not.

you'll need store state in db.

you need table columns batch (mailing) id , id of email in main table.

then insert line table each email sent, , can replace select with:

select * table id not in (select id email_sent batch=?) limit ?

alternatively, can add together column states whether email has been sent (or when sent). first add together emails batch table:

insert batch_email (batch_id, email_id) select ?,email.id email ...

and pick emails send there:

select * table bring together batch_email on table.id=batch_email.id not sent limit ?

and finally, set sent 1 time e-mail has been sent:

update batch_email set sent=true id=?

php mysql sql