SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast lhatkins's Avatar
    Join Date
    Dec 2002
    Location
    Exeter, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Display Fixture results

    HI there,
    I have been putting together a results table for a local darts team, and need some advice on how I can get fixtures to display.

    What I'd like it to look like is (sorry this isn't formatting right on the forum but hope you get the idea)

    Date Club 1 Club2
    01-01-03 Team 1 Team 2
    Team 2 Team 3
    Team 4 Team 5

    01-02-03 Team 2 Team 5
    Team 4 Team 9

    And so on.

    The mysql table that contains the data, has fields.
    Date, Club1, Club2.

    I was thining I could use the DISTICT SQL statment, but this seemed to loose the link between the teams, and puts the wrong team fixture the wrong date.

    Is there a simple sql statment to product a list while only printing 1 date, I'm thinking this should be simple but I've hit a wall here.

    TIA

    Lee
    Regards

    ---
    Lee

  2. #2
    SitePoint Enthusiast lhatkins's Avatar
    Join Date
    Dec 2002
    Location
    Exeter, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I might be going the completly wrong but here is the sql I have so far is
    PHP Code:
    $resultdate mysql_query("SELECT DISTINCT DATE_FORMAT(date,'%d-%m-%Y') AS realdate, id, league, team1, team2, team1score, team2score, team_gender, venue FROM fixtures"); 
    Then I use two while loops, one to list the date, then another to list the team details.

    This is just giving me the problem I describled above, anyone got a better idea on how I could do it?

    Thanks.

    Lee
    Regards

    ---
    Lee

  3. #3
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    No, I don't think you can get what you want with MySQL SQL only. Oracle has a feature which can surpress repeated column values.

    Instead try it with PHP:

    PHP Code:
    <pre>
    <?php
    $resultdate 
    mysql_query("SELECT DATE_FORMAT(date,'%d-%m-%Y') AS realdate, id, league, team1, team2, team1score, team2score, team_gender, venue FROM fixtures"); 

    $compare '';
    while (
    $data mysql_fetch_array($resultdate) {
       if (
    $compare != $data['realdate']) {
          echo 
    "\n\n"$data['realdate'], "\n";
          
    $compare $data['realdate'];
       }
       echo 
    'Team1: '$data['team1'], ' Team2: '$data['team2'], "\n";
    }
    ?>
    </pre>
    This should print the data as

    2003-5-13
    Team1 - Team2
    Team3 - Team4
    Team5 - Team6

    2003-6-10
    Team1 - Team2
    Team3 - Team4
    Team5 - Team6

    Hope it helps,
    Christian

  4. #4
    SitePoint Enthusiast lhatkins's Avatar
    Join Date
    Dec 2002
    Location
    Exeter, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Christian
    Thanks for your reply.
    Hum, I put your code in place, but it didn't seem to group the data together as I hoped, I still have duplicate dates being displayed.
    Regards

    ---
    Lee

  5. #5
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    I didn't use an order by clause which could be the reason why dates are displayed more than once:

    PHP Code:
    <pre>
    <?php
    $resultdate 
    mysql_query("SELECT DATE_FORMAT(date,'%d-%m-%Y') AS realdate, id, league, team1, team2, team1score, team2score, team_gender, venue FROM fixtures ORDER BY realdate");

    $compare '';
    while (
    $data mysql_fetch_array($resultdate) {
       if (
    $compare != $data['realdate']) {
          echo 
    "\n\n"$data['realdate'], "\n";
          
    $compare $data['realdate'];
       }
       echo 
    'Team1: '$data['team1'], ' Team2: '$data['team2'], "\n";
    }
    ?>
    </pre>

  6. #6
    SitePoint Enthusiast lhatkins's Avatar
    Join Date
    Dec 2002
    Location
    Exeter, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Arr yes, why didn't I spot that (also you missed a 2nd ")" to close the while array), but yes this does do what I need, and with a few formatting tweaks I got it looking about right so thanks for that.
    Regards

    ---
    Lee


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
  •