CODEWITHSUNDEEP
phpmysql
Jayr
Jayr

Reputation: 608

MYSQL SELECT WITH PAUSE

We are making an php emailer which works perfect.

Selecting all the users from a database and send them emails are good to go.

But, since were have a huge amount of emails that has to be send, we would like start and pause the transactions of emails with [ 1000 ] to not overload the server.

Example:

SELECT: 1000;

PAUSE MYSQL

SELECT ANOTHER 1000;

PAUSE MYSQL

ETC.

I read about the START TRANSACTION, COMMIT & ROLLBACK functions, and I think I implemented this right..

Can someone help me to include a pause of 100 seconds before ROLLBACK the transaction?

I don't know what to do..

What i got until now [prefixed code]..

$max=1000;

$send=0;

$rollback=false;

mysql_query('START TRANSACTION;');

$query = mysql_query("SELECT DISTINCT mail_id, customers_email_address newsletters WHERE ORDER BY mail_id ASC");

while($result=mysql_fetch_array($query){

if( $rollback == true ){

$rollback = false;

mysql_query("ROLLBACK;");

}

[------script to send the emails-----]

$send++;

if( $max == $send ){

mysql_query("COMMIT;");

$rollback = true;

}

}

Cheers Jay

Upvotes: 1

Views: 2506

Answers (3)

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

Few optios like below:

1) You can implement Cronjob.

2) There is a opensource small application of php as PHPList which can be integrated in few seconds. (i already use this one, so)

3) 3rd option, you can use sleep function of php. (i am not sure about this)

Upvotes: 0

Colin M
Colin M

Reputation: 13348

There is no need for transactions here at all - you're not updating anything. In fact, the overhead of transactions is entirely pointless here, so I'd advise you take that out.

You could simply (in theory, you can write the code for this)

  • Select the first 1000 rows from the database: SELECT ... LIMIT 0, 1000
  • Increment your offset by 1000
  • Select the next 1000 rows: SELECT ... LIMIT 1000, 1000
  • Rinse and repeat, until you get less than 1000 rows back from your query.

Please note that in order for that method to work, you'll want to ORDER BY the primary key in ASC order or something, to be sure you don't get the same row twice.

Upvotes: 3

macino
macino

Reputation: 457

all you need is to schedule your sender script with cron for example and sending some amount of emails. (in sql use LIMIT).

it will send than N emails every M minutes and server will be happy ;)

Upvotes: 1

Related Questions