Looping through Select statement?

[duckduckgoose]

Let's say I have a table consisting of 100 rows. I want to return at least 60 rows where I retrieve every 3rd row. Is there a way to say if you reach the last record before returning 60 rows, then loop around?

For example:
$sql = "SELECT id FROM myTable WHERE id % 3 = 0";

This will give me:

id

3
6
9
12
...
99

Then, because it reached the last divisible number before returning the number of records I want, it would then loop around and return:

2
5
8
11

etc. Is this possible?

[Scriptmaker]

hmm..maybe have it return all records and then within your while($row = fetch array) loop, do the math with a $counter variable?

This mysql function looks interesting:
http://www.php.net/function.mysql-data-seek

I learn something new about mysql every day, so I'll keep looking around.

Good luck!

[bradgrafelman]

Well, you could create a MySQL procedure to do this... or use a PHP solution such as:

$sql = "SELECT id FROM myTable WHERE id % 3 = 0 LIMIT 60";
$exec = mysql_query($sql);
$rows = mysql_num_rows($exec);

$id_list = array();

if($rows > 0) {
	while($data = mysql_fetch_assoc($exec))
		$id_list[] = $data['id'];

for($i=0,$num=$rows; $num<60; $num++) {
	if(!isset($id_list[$i]))
		$i = 0;

	$id_list[] = $id_list[$i];
	$i++;
}
}

[Rodney_H_]

Another approach that might work would be to do the math first and to slightly alter the query syntax:

$totalNeeded = 60;  // total results needed/wanted

$divisor = 3; // modulus divisor

$range = range(0, ($totalNeeded * $divisor)); // numbers used in query before filtering

$counter = 1;

// filter the numbers in your array:
foreach($range as $key=>$value)
{
	if($counter > $totalNeeded) unset($range[$key]);
	elseif($value % $divisor != 0) unset($range[$key]);
	else $counter++;
}

// use the "IN" clause for your query:
$query = "SELECT id 
		  FROM myTable
		  WHERE program_id IN(" . implode(',', $range) . ")";

Of course, this may become very unwieldy for very large results needed, but with pagination, and keeping result sets less than 100, it should be ok... me thinks.

The reason why I like this, is that you will not select anything from the DB that you will not end up displaying on the page.

[duckduckgoose]

Thanks for the replies.

The table itself will contain anywhere from 800K to 1MM records. I thought a sql statement would make things faster, but is it faster to use php code to handle the looping?

[bradgrafelman]

duckduckgoose wrote:

but is it faster to use php code to handle the looping?

I personally don't know, so my only answer is: benchmark. Try different methods numerous times and calculate the average time for each method.

Depending upon the version of your SQL server, you may be able to create a stored procedure that does this looping for you.