Select every 3 rows from a table.

[martin88]

Hey guys,

I have a bit of a problem, Iam doing a match results page with data from mysql.

My table Name: JuniorMatchResults

Data Colums: id, date, venue, position (1,2 or 3), weight.

What i wanto to do is output this information in my website such as you would have the 1st 3 positions outputted in a table, the the next set of 3 in a table and so on.

Is there any way to do this, i take it that there should be with PHP/Mysql.

Thanks
Martin

[BrandonK]

The easiest way is to use 3 queries:

SELECT *
  FROM JuniorMatchResults
 WHERE JuniorMatchResults.position = 1
 LIMIT 3
----
SELECT *
  FROM JuniorMatchResults
 WHERE JuniorMatchResults.position = 2
 LIMIT 3
----
SELECT *
  FROM JuniorMatchResults
 WHERE JuniorMatchResults.position = 3
 LIMIT 3

then fetch and echo using PHP.

[r937]

the easiest way is to use one query with ORDER BY, and detect the breaks with php

[BrandonK]

But if you have a very large dataset, wouldn't that be incredibly inefficient? Cycling through 1000 rows to find 9?

[r937]

incredibly inefficient? actually, no, it would not, it would incredibly efficient

which 9 are you talking about?

[RichardMJohn]

SELECT * FROM JuniorMatchResults WHERE `position` = 1 OR `position` = 2 OR `position` = 3

?

[r937]

WHERE `position` = 1 OR `position` = 2 OR `position` = 3

that pretty much exhausts all possibilities, doesn't it

[RichardMJohn]

that pretty much exhausts all possibilities, doesn't it

As far as I can tell, the positions go higher than 3 - he just wants the first 3 from each race outputted.

[r937]

really? the way i understood it, he just wants to print 3 rows at a time in html tables

martin88, where are you?

[martin88]

Hi Guys,

Yeah, i do want to just output 3 rows per HTML table

1
2
3

1
2
3

1
2
3

etc.

Thanks for the replies guys

Martin88

[r937]

use ORDER BY clause to get them in the correct sequence, and then test in php for control breaks

[martin88]

How would i test for control breaks?

Martin

[jondolar]

I use a counter variable.

$x = 1;
while ($row = mysql_fetch_object($result)){
if ($x == 1) // we're the first row so start a new table
echo "<table>";
echo "<tr><td>".$row->data."</td></tr>";
if ($x == 3)
echo "</table>";
$x++;
if ($x == 4)
$x = 1;
}
// close table if there weren't enough records
if ($x != 1)
echo "</table>";

[martin88]

Thanks for your quick replies, will try these over the next couple of days.

Martin

[BrandonK]

incredibly inefficient? actually, no, it would not, it would incredibly efficient

I want to understand this more. You are saying that it's more efficient to select all of the rows in SQL and then move to PHP too count, order and limit the results? If there are 1000 rows and for some odd reason the first 900 are position 1, you have to loop through all of the results to get the 9 rows (3 for pos. 1, 3 for pos. 2, and 3 for pos. 3).

If you did 3 queries, you have everything sorted exactly how you wanted and there is no extra data (unfortunately at the cost of extra queries).

I take everything you say very seriously (I've learned a ton from you), so I just wanted to understand what you mean.

[r937]

brandon, i hope i did not suggest doing counting, sequencing, and limiting in php

if it sounded like i said that, then i'm sorry, but what you think i said is not what i meant

[BrandonK]

Good. I was just really confused because I guess I read in to it all wrong. I just couldn't see how that was a good idea at all