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!

[php]$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.’

’));
$i++;
}
/* The problem is that it always shows multiple instances of the first result only
where start = 1000 and end = 2000
*/
[/php]
Any help would be much appreciated!!

[phphelp]

You are incrementing $i outside the while loop, which make no sense. Looks like you placed closing } by mistake:
[php]
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";
}

} // <------- here you’re closing the loop
echo nl2br(trim($rows.’

’));
$i++; // <------------- and here you’re incrementing loop counter
[/php]

[Oziam]

actually it is in the first loop which handles the $i variable, the second loop handles the
query condition. Moving $i++ inside the second loop doesn’t work either!

[phphelp]

I didn’t notice the first loop (that’s why proper formatting of php code is desired). See, if you are querying the database outside the first loop, the recordset does not reset to initial position, and your inner while loop will work only 1 time. I suggest that you re-organize your loops.

[Oziam]

I got it sorted, I needed to query the database conditionally with each pass,
and now it works 100%, my revised code is below!
[php]$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.’

’));
$i++;
}[/php]Thanks!