SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Member
    Join Date
    Jul 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select every 3 rows from a table.

    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

  2. #2
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The easiest way is to use 3 queries:
    Code:
    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.
    MySQL v5.1.58
    PHP v5.3.6

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the easiest way is to use one query with ORDER BY, and detect the breaks with php

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But if you have a very large dataset, wouldn't that be incredibly inefficient? Cycling through 1000 rows to find 9?
    MySQL v5.1.58
    PHP v5.3.6

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    incredibly inefficient? actually, no, it would not, it would incredibly efficient

    which 9 are you talking about?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2007
    Location
    Bridgend, UK
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT * FROM JuniorMatchResults WHERE `position` = 1 OR `position` = 2 OR `position` = 3

    ?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RichardMJohn View Post
    WHERE `position` = 1 OR `position` = 2 OR `position` = 3
    that pretty much exhausts all possibilities, doesn't it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2007
    Location
    Bridgend, UK
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    really? the way i understood it, he just wants to print 3 rows at a time in html tables

    martin88, where are you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Jul 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re.

    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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    use ORDER BY clause to get them in the correct sequence, and then test in php for control breaks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Jul 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re.

    How would i test for control breaks?

    Martin

  13. #13
    SitePoint Addict
    Join Date
    Sep 2005
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Use a counter

    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>";
    PHP Shopping Cart Software Easy Ecommerce Shopping Cart Script.
    PHP Super Cart is 100% template driven.

  14. #14
    SitePoint Member
    Join Date
    Jul 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your quick replies, will try these over the next couple of days.

    Martin

  15. #15
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.
    MySQL v5.1.58
    PHP v5.3.6

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    MySQL v5.1.58
    PHP v5.3.6


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •