2

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>
Improve this question

3 Answers 3

Reset to default
4

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

So far this is what I need to see. I have put this into the code and it works for columns 1 and 2, but it seems to not populate anything when I make it say MOD 3 = 3 I understand it's diving the () by 3 and the remainder (=1) is what is supposed to show, I guess I'm just not able to wrap my head around it
Nevermind, I just realized that it should say MOD 3 = 0 because 3/3=1.0 (.0 being what MOD is looking for). This answered my question perfectly. THANK YOU!
@ntgCleaner... DOH! That was my bad; I obviously wasn't thinking clearly. Of course, as you figured out, it needs to be ` MOD 3 = 0` to get every third row starting at the third row. DOH!
2

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?

Improve this answer

3 Comments

I tried something like this unsuccessfully. I would love to see an example of this. If you follow the link above, you can see the original question and there is a link in there with an example of what I'm trying to do. I tried to do an incremental counter, then while the counter == 1, put it in column 1, while counter == 2, put it in column 2, etc.
This is most likely the way to go. If you need to fetch all the rows anyway, just handle your "every third row" logic in the application.
Added my attempt at splitting the results
0

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 } }
Improve this answer

5 Comments

I think this is missing a step. You have query named $results but there is no query with the name $qry_questions which is in the while statement. I am currently trying these options
oh, that's just a typo sorry. $qry_questions should be $questions
Thank you - I have implemented this but the results are not the desired results. Doing this actually creates new columns which are stacked next to all of the other columns which creates the same effect as if I floated everything left. It's almost as if I have to APPEND all new queries into an already existing column
well that's a visual thing that you need to fix. as long as the question is answered you should be able to go from there.
The answer given here is a good thought, though the original question is how to do a sql query that selects every Nth row starting at N. Plus, The answer given here is also something that I have tried with out success mentioned earlier

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.