CODEWITHSUNDEEP
phpmysql
ntgCleaner
ntgCleaner

Reputation: 5985

How to select every nth row in mySQL starting at n

I have created a question Here That I think is a little bloated. To skim down this question here is my requirements:

I need to create 3 mySQL queries that grab all information in that row so that I can format the information into one of HTML tables.

Here is the query that I have which selects everything:

$qry_questions = mysql_query("SELECT * FROM mbr_qa_questions ORDER BY q_votes DESC);
while($row = mysql_fetch_array($qry_questions){
    //Grab all information
}

Now, I need to make three queries that act like the one above, but I need it to do something like this:

pull everything from every third row in a table starting at row #1
pull everything from every third row in a table starting at row #2
pull everything from every third row in a table starting at row #3

Then I would put each of those queries into one of the 3 columns.

I can not do this by unique ID. Yes, it is an auto incrementing ID, but it will be probable that I might have to move whole rows into another table.

EDIT: I've added my attempt to count each row and place the queried result into the right "bin"

//GIVE EACH ENTRY A COLUMN NUMBER
$count++;
if($count >= 4){
    $count = 1; 
}?> 

<div class="col" id="col-1">
    <?PHP
    while($count == 1){
        include("pieces/answers/newAnswerLayout.php"); 
    }
    ?>
</div>
<div class="col" id="col-2">
    <?PHP
    while($count == 2){
        include("pieces/answers/newAnswerLayout.php"); 
    }
    ?>
</div>
<div class="col" id="col-3">
    <?PHP
    while($count == 3){
        include("pieces/answers/newAnswerLayout.php"); 
    }
    ?>
</div>

Upvotes: 2

Views: 5125

Answers (3)

kennypu
kennypu

Reputation: 6051

you can probably just use a counter to do it in a much simpler fashion, but I would do it like this: 

$questions = array();
$results = mysql_query("SELECT * FROM mbr_qa_questions ORDER BY q_votes DESC");

while($row = mysql_fetch_array($results)){
    $questions[] = $row;
}

for($i = 0; $i < count($questions); $i++) {
  if($i + 1 % 3 == 0) {
     $questions[$i];//this is a 3rd, so do something with it
  }
}

Upvotes: 0

spencer7593
spencer7593

Reputation: 108380

Here's one approach, to get the resultset returned by MySQL. (But it might be easier to just return all the rows, and get every third row within the app). But it can be done in MySQL pretty easily. Note that your original query is wrapped in parens (as an inline view) aliased as r.

SELECT r.*
  FROM ( 
         SELECT *
           FROM mbr_qa_questions
          ORDER BY q_votes DESC
       ) r
 CROSS
  JOIN ( SELECT @i := 0 ) s
HAVING ( @i := @i + 1) MOD 3 = 1

That will return every third row, starting with the first row. To get every third row starting with the 2nd and 3rd row, replace the literal = 1 in the HAVING clause with = 2 or = 3 (respectively).

Upvotes: 4

palako
palako

Reputation: 3470

Why do you need three different queries? Can you not, instead, keep the single query you have, have a counter that is incremented in every iteration of the loop, and on %3 does whatever you would do for cases #1, #2 or #3?

Upvotes: 2

Related Questions