[RESOLVED] LIMIT question

matthewod

I have a database of about 54,000 that I send out a weekly newsletter to. I wanted to break up the one db query and only run about 5,400 at a time. So here is what I did:

<?php

//get total # of subscribers
$sql = "SELECT * FROM userdb WHERE mail_list = 'alerts' ";
$result = @mysql_query($sql, $connection) or die(mysql_error());
$num_rows = @mysql_num_rows($result);

$i = 10;

$limits = floor($num_rows / $i); //5,500

//number of times to execute send script:

for ($a = 1; $a < 11; $a++) {

$stop=($a * $limits);

$start=(($a - 1) * $limits);

print("Limits$a: $start $stop<br>");

include_once('db_connect.php');

//execute emails
$sql = "SELECT * FROM userdb WHERE mail_list = 'alerts' order by email LIMIT  $start, $stop";
$result  = @mysql_query($sql, $connection) or die(mysql_error());
$num_rows = @mysql_num_rows($result);

print("Rows$a $num_rows<br><hr>");

}

?>

The results look like this:

Limits1: 0 5416
Rows1 5416
Limits2: 5416 10832
Rows2 10832
Limits3: 10832 16248
Rows3 16248
Limits4: 16248 21664
Rows4 21664
Limits5: 21664 27080
Rows5 27080
Limits6: 27080 32496
Rows6 27081
Limits7: 32496 37912
Rows7 21665
Limits8: 37912 43328
Rows8 16249
Limits9: 43328 48744
Rows9 10833
Limits10: 48744 54160
Rows10 5417

Does it look like on Rows2 that I am sending out 10832 emails? Or am I sending out 5417?

This is only my test script and not my real newsletter script. I just want to make sure I am doing this right. I don't get too many 'live' tests. Wouldn't that suck if the recipients got 10 of the same newsletter!

Thanks for the head up!

MattD

bradgrafelman

Take a look at the MySQL manual for the LIMIT syntax, because it doesn't do what you think it does.

LIMIT doesn't take start and stop parameters - it takes offset and row_count parameters. "LIMIT 0, 5" means return the next 5 rows, starting from the beginning. "LIMIT 5, 10" means return 10 rows after row #5 (so rows 6-16).

matthewod

bradgrafelman,

Ah ha! It makes sense now! So going by my original code I should be able set how many emails should go out at a time by using my product from the division:

$i = 10;

$limits = floor($num_rows / $i); //5,500 

$sql = "SELECT * FROM userdb WHERE mail_list = 'alerts' order by email LIMIT  $start, $limits";

Now my results look like this:

Limits1: 0 5416
Rows1 5416
Limits2: 5416 10832
Rows2 5416
Limits3: 10832 16248
Rows3 5416
Limits4: 16248 21664
Rows4 5416
Limits5: 21664 27080
Rows5 5416
Limits6: 27080 32496
Rows6 5416
Limits7: 32496 37912
Rows7 5416
Limits8: 37912 43328
Rows8 5416
Limits9: 43328 48744
Rows9 5416
Limits10: 48744 54160
Rows10 5416

Thanks for the quick response and helping my understand this.

MattD