query with loop problem

[Oziam]

What I am trying to do is query a database using a loop conditional

and return the data but my code only repeats the first instance multiple times!

 

My code is below, I have tried to use comments to explain what should be happeing!

 

$rows = null;
$q1 = "SELECT * FROM table";
$r1 = mysql_query($q1) or die(mysql_error());

$i = 1;
while($i <= 5){

$start = (1000 * $i);
$end = ($start + 1000);

while($a1 = mysql_fetch_array($r1, MYSQL_ASSOC)){
/* query the database where 'start' is  >= 1000
and where 'end' is <= 2000, this should loop so the next time is
where 'start' >= 1000 * $i should be 1000 then 2000, 3000 etc...
and where 'end' should be 2000, 3000, 4000 etc...
*/
if($a1['start'] >= $start && $a1['end'] <= $end){
$rows .= $a1['col1'].':'.$a1['col2']."\n";
}

}
echo nl2br(trim($rows.'<p>'));
$i++;
}

/* The problem is that it always shows multiple instances of the first result only 
where start = 1000 and end = 2000
*/

Any help would be much appreciated!! 

[AbraCadaver]

Because in your first for loop the while runs through all of the query results and the next time around it is at the end.  You could use mysql_data_seek() to reset after the while, but you would do better implementing this in the query.  Use the WHERE clause for start and end and the LIMIT clause to limit the rows returned.

[Oziam]

Thanks for the hint! I have revised my code as you suggested and now it works 100%

 

$i = 1;
while($i <= 10){

$start = (1000 * $i);
$end = ($start + 1000);

$rows = null;

$q1 = "SELECT * FROM table WHERE start >= '$start' AND end <= '$end' LIMIT 0,100";
$r1 = mysql_query($q1) or die(mysql_error());

while($a1 = mysql_fetch_array($r1, MYSQL_ASSOC)){
if(mysql_num_rows($r1) > 0){
$rows .= $a1['col1'].':'.$a1['col2']."\n";}
}
echo nl2br(trim($rows.'<p>'));
$i++;
}