Getting every xth row's id where column=0

[chreez]

I have a small database with a bunch of entries. Some entries have a value of 0 for a column \'level\', others have different values. Every row has a unique id. I need to access the id to every 25th row with a level value of 0. I\'m confused as how to accomplish this.

 

SELECT * FROM table WHERE level=0

 

then a loop to go through each one, storing the id of every 25th one in an array, but that seems a little excessive, is there an easier way to do this?

[Kriek]

If you have an ID column with no gaps in the sequence, then this should work, it will give you all records where the ID is evenly divisible by 25. Numerals ending with 00, 25, 50, or 75 represent numbers divisible by 25.

 

SELECT * FROM table WHERE MOD(ID,25)=0;

[chreez]

meh...there are no gaps in the id, however, I don\'t want to get entries from the list where the level value is not 0. Is there no way using PHP/MySQL to just access the 25th returned row from the query:

 

SELECT * FROM table WHERE level=0

 

??¿?

[Kriek]

With my previous example, I thought it was painfully obvious.

 

SELECT * FROM table WHERE MOD(ID,25)=\'0\' AND level=\'0\';

 

So you only want the 25th row, as in singular?

 

SELECT * FROM table WHERE level=\'0\' LIMIT 25,1;

 

Additionally if you only need the ID for each row, get rid of the asterisk; it is truly evil.

 

SELECT ID FROM table WHERE level=\'0\' LIMIT 25,1;

[chreez]

thanks, I thought you could do that, but I\'m new to MySQL and wording questions can be hard (especially when looking through help files). Well, I want every 25th row, so at the end of those queries, LIMIT 25, * or LIMIT 25, 100?

[Kriek]

No, you are misunderstanding the purpose of the LIMIT clause; constructed as offset, row_count.

 

This query is intended to start at row 25 and only pull one row.

 

SELECT * FROM table WHERE level=\'0\' LIMIT 25,1;

 

This query is intended to pulls rows 25, 50, 100 ect.

 

SELECT * FROM table WHERE MOD(ID,25)=\'0\' AND level=\'0\';

[Barand]

Doesn\'t that assume that record id\'s 0, 25, 50 all have level=0 ?

 

Shouldn\'t criticise without offering a solution ...

 

[php:1:aebe4d3f2d]<?php

function filter25($v) {

return $v % 25 == 0;

return;

 

}

$res = mysql_query (\"SELECT id FROM tablename WHERE level = 0\");

$id_array = array();

while ($row = mysql_fetch_row($res)) {

$id_array = $row[0];

}

$id_array = array_flip($id_array);

$array25 = array_filter($id_array , \'filter25\');

foreach($array25 as $k=>$v) {

echo \"$k <br />\"; // these are the ids you want

}

?>[/php:1:aebe4d3f2d]

 

hth

[Kriek]

Doesn\'t that assume that record id\'s 0, 25, 50 all have level=0 ?

Yes absolutely, that IS what he wants; read his annotations.

 

I don\'t want to get entries from the list where the level value is not 0.

I need to access the id to every 25th row with a level value of 0.

[Barand]

Alternatively

 

CREATE TABLE temp (id integer);

 

INSERT INTO temp SELECT id FROM mytable WHERE level = 0

 

SELECT * FROM temp WHERE MOD(ID,25)=\'0\'

 

Edit : Won\'t work - may not be any recs in temp where id % 25 = 0, back to counting records, and using id where count % 25 = 0