Jump to content


Photo

Getting every xth row's id where column=0


  • Please log in to reply
8 replies to this topic

#1 chreez

chreez
  • Members
  • Pip
  • Newbie
  • 4 posts
  • Locationm4rf

Posted 01 October 2003 - 03:40 AM

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?

#2 Kriek

Kriek
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFlorida

Posted 01 October 2003 - 12:19 PM

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;

Jon Kriek · PHP Freaks
Sometimes the need to mess with their heads ...
... outweighs the millstone of humiliation.


#3 chreez

chreez
  • Members
  • Pip
  • Newbie
  • 4 posts
  • Locationm4rf

Posted 01 October 2003 - 01:43 PM

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

??¿?

#4 Kriek

Kriek
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFlorida

Posted 01 October 2003 - 02:47 PM

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;

Jon Kriek · PHP Freaks
Sometimes the need to mess with their heads ...
... outweighs the millstone of humiliation.


#5 chreez

chreez
  • Members
  • Pip
  • Newbie
  • 4 posts
  • Locationm4rf

Posted 01 October 2003 - 03:20 PM

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?

#6 Kriek

Kriek
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFlorida

Posted 01 October 2003 - 03:39 PM

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

Jon Kriek · PHP Freaks
Sometimes the need to mess with their heads ...
... outweighs the millstone of humiliation.


#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 01 October 2003 - 06:53 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 Kriek

Kriek
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFlorida

Posted 01 October 2003 - 09:54 PM

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.


Jon Kriek · PHP Freaks
Sometimes the need to mess with their heads ...
... outweighs the millstone of humiliation.


#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 02 October 2003 - 03:38 AM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users