Jump to content

Archived

This topic is now archived and is closed to further replies.

chreez

Getting every xth row's id where column=0

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

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

 

??¿?

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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\';

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.