Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jul 2009
    Posts
    72
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Print once if chained event!?!?

    I have this script which loops through all golf matches reported by a user, and it works fine, but... Some of the matches are chained together, and for those matches I only want to show 1 records instead of lets say 2 if 2 matches are chained together...

    Table looks like this:
    id, clubid, matchstart, reported, season, chainid, title

    5 records from table:
    1, 1, 2009-04-05 07:20:00, 1, 1, ,Single1
    2, 1, 2009-04-09 07:20:00, 1, 1, ,Single2
    3, 1, 2009-04-12 08:00:00, 1, 1,5645 ,Multi1
    4, 1, 2009-04-12 13:00:00, 1, 1,5645,Multi2
    5, 1, 2009-04-05 07:20:00, 1, 1, ,Single3


    This I prints like this:
    PHP Code:
    $sql="SELECT * FROM ".$prefix."_matches WHERE clubid='$clubid' AND reported=1 AND season='$season' GROUP BY chainid ORDER BY matchstart DESC"
    $result mysql_query($sql); 
    while(
    $row mysql_fetch_array($result)){          
         echo 
    'Match: '.$row['title'].' ('.$row['id'].')<br>';      

    This prints out 5 records like this:
    Single1 (1)
    Single2 (2)
    Multi1 (3)
    Multi2 (4)
    Single3 (5)


    What it should look like is this:
    Single1 (1)
    Single2 (2)
    Multi1 (3)
    Single3 (5)


    Hope this makes sense...

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I don't see how that data, run through that script, would produce those 5 rows. I'd have to see it to believe it, because you are grouping by chainid, of which there are only two values (5645 and "") so you should only be getting two rows back.

  • #3
    New Coder
    Join Date
    Jul 2009
    Posts
    72
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    I don't see how that data, run through that script, would produce those 5 rows. I'd have to see it to believe it, because you are grouping by chainid, of which there are only two values (5645 and "") so you should only be getting two rows back.
    Sorry... Thats correct.. This is something I have tested but with no luck. As you say... It only produces 2 rows... The script I was referring to is this:
    PHP Code:
    $sql="SELECT * FROM ".$prefix."_matches WHERE clubid='$clubid' AND reported=1 AND season='$season' ORDER BY matchstart DESC";  
    $result mysql_query($sql);  
    while(
    $row mysql_fetch_array($result)){           
         echo 
    'Match: '.$row['title'].' ('.$row['id'].')<br>';       

    The problem is still the same...

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Alright... well your first query is very close to your answer. You want to group by that chainid, but only when there is a value. OK... what you need is a column value that is unique when chainid = '', and is the actual chainid value when chainid > ''. You can then group by that column. We can use the MySQL if() function for this.

    For example:

    Code:
    select *, if(chainid = '', id, chainid) as chaingroup from t_codes
    WHERE clubid='1' AND reported=1 AND season='1'
    GROUP BY chaingroup
    ORDER BY matchstart

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Forgot to mention-- be aware that when you do a wildcard select with a GROUP BY, you have no way of determining what data will be in the columns not included in your GROUP BY. This is because your results are a combination of all rows with the same chainid, so whichever row MySQL decides to use for the other values, it'll use.


  •  

    Posting Permissions

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