Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 4 of 4

Thread: Schedule Help

  1. #1
    Member
    Join Date
    Nov 2002
    Location
    Elmira, NY
    Posts
    41
    Member #
    261
    I am working on a fan site for a local minor league hockey team. On my main page I want a section that says next game and will automatically select the next game from the database depending on the current days date. http://jackalshockey.net is the site. you can see an example at the top of the right column. I used html to create that but would rather have it do it dynamically.

    this is my db setup

    id smallint(5)

    away varchar(100) <---away team

    home varchar(100) <---home team

    date date (0000-00-00 <---format)

    time varchar(255) <---game time)

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Your query:
    Code:
    SELECT id, away, home, date, time
    FROM table_name
    WHERE date >= NOW()
    ORDER BY date ASC
    LIMIT (1, 1);
    That said, maybe you should make your table(s) something more like this:
    Code:
    CREATE TABLE cities (
        id int NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    CREATE TABLE teams (
        id int NOT NULL AUTO_INCREMENT,
        city_id int NOT NULL REFERENCES cities (id),
        name varchar(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    CREATE TABLE locations (
        id int NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        PRIMARY KEY (id)
    );
    
    CREATE TABLE games (
        id int NOT NULL AUTO_INCREMENT,
        team_id int NOT NULL REFERENCES teams (id),
        location_id int NOT NULL REFERENCES locations (id),
        game_date datetime NOT NULL,
        PRIMARY KEY (id)
    );
    
    INSERT INTO cities ('Miami');
    INSERT INTO cities ('Vancouver');
    
    INSERT INTO teams (1, 'Heat');
    INSERT INTO teams (2, 'Canouks');
    
    INSERT INTO locations ('Home');
    INSERT INTO locations ('Away');
    
    INSERT INTO games (1, 2, '10/20/2003 12:00:00');
    INSERT INTO games (2, 1, '10/27/2003 13:00:00');
    Then, you could query your upcoming games like so:
    Code:
    SELECT
        t.name AS team,
        l.name AS location,
        g.game_date,
        CASE WHEN l.name = 'Away' THEN 'AT ' || c.name ELSE 'Home City Name' END AS city
    FROM
        games AS g
        INNER JOIN teams AS t ON g.team_id = t.id
        INNER JOIN cities AS c ON t.city_id = c.id
        INNER JOIN locations AS l ON g.location_id = l.id
    WHERE g.game_date >= NOW()
    ORDER BY g.game_date ASC
    LIMIT (1,1);
    I know everything becomes more complex, but your data is normalized and easier to reference. It also offers more complex scheduling abilities, including 'AT City Name' references for away games, etc.
    [/code]

  4. #3
    Member
    Join Date
    Nov 2002
    Location
    Elmira, NY
    Posts
    41
    Member #
    261
    well i already have a schedule built with the full season. and i wasnt even thinking of this when building the schedule and i would rather keep what i got if it can be done. it will tkae a while to redo if i dont. is there a way to do it with what i have? if not ill leave it like it is. i have 2 much work to do and the season starts 2morrow.

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Of course you CAN use what you have... the "your query" part of my post above shows you how to do it with what yo already have. The rest of the post just shows you a better way of doing it that you won't likely want to change 4 months down the road when you learn a bit more about database design. Building the database won't take any time as I've already done it for you above. Importing the data should be a breeze if you use SQL to do it.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 11:47 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com