JamesB JamesB - 2 years ago 73
MySQL Question

Loop query result, and do something after every 100 records with overlap

$result contains a non specific amount of records.

I need to loop through $result, and after every 100 records, do something, and then continue the loop, but include 5 records from the previous loop (overlap).

Like this:

  1. Get records 0-100

  2. Do something with these records

  3. Get records 95-195

  4. Do something with these records

  5. Get records 190-290

Anyone have a suggestion how this is done in the most efficient way?

Answer Source

first of all I agree with the commentators. We are here to help with problems you've ran in, but not to write your script.

Neverthelesse I wrote a perlish solution of your problem:

$results; #your results

for($i = 0; $i <= @{$results}, $i++){ #loop through all results
    my @tempList;
    push(@tempList, @{$results}[$i]); #push every result in a temporary list
    if (@{$results} == 100 || $i == @{$results}) { #do sth, if the tempList hast 100 entries or you're at the last round of your loop
        #do sth with the 100 records
        $i -= 5; #decrement i to get the 5 entries overlap


You can see that it's a simple loop through your results. Every 100th round the if will evaluate to true and you can do sth with your data. The overlap is realised by decreasing the counter every 100th round. Be careful in the last round when finishing your loop! You will get an infinite loop if you don't escape the $i -= 5; in the last round!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download