SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot MediaOhgr's Avatar
    Join Date
    Jul 2001
    Location
    Calgary, Alberta, Canada
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL DB Set Up Questions

    Hey There! I'm looking for some advice on a database I'm setting up. What I've done is set up a MySQL db on my hard drive using PHPTriad then I've installed MyODBC so I can administer with MS Access.

    The database is a hockey pool that I've started but I'm having some problems. Here's what the db looks like thus far:

    Table 1 - entryInfo:
    - entryID
    - lastName
    - firstName
    - password
    - email

    Table 2 - playerInfo:
    - playerID
    - lastName
    - firstName

    Table 3 - goalieInfo:
    - goalieID
    - lastName
    - firstName

    Table 4 - playerStats:
    - playerID
    - goals
    - assists

    Table 5 - goalieStats:
    - goalieID
    - wins
    - shutOuts

    Table 6 - playerTeams:
    - entryID
    - playerID

    Table 7 - goalieTeams:
    - entryID
    - goalieID

    Using relationships in Access, I've been able to generate some pretty intense queries to pull the info out that I need. No problem there. My problem is conceptualizing how I'm going to add the player and goalie points up, then input that to the database so I can have queries that look at the teams, and then sort the standings decending in order so I can have a stats column on the page.

    Make sense? Right now I don't have a totalPoints column in the player and goalieStats tables. I think I'll need one though to sort. Question being, how do I get that to update when I update the goals/assists and wins/shutouts? Any advice will be greatly appreciated.
    MediaOhgr - But then again, maybe it's just me ...

  2. #2
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using relationships in Access, I've been able to generate some pretty intense queries to pull the info out that I need.
    The SQL queries that you develop in MS Access will not necessarily work with MySQL -- anything involving nested select statements, for instance.

    Question being, how do I get that to update when I update the goals/assists and wins/shutouts?
    MySQL does not support triggers. You'd need to use explicit SQL calls in your PHP(?) source code to update this information.

    Will you have multiple playerStats entries for each playerID and/or multiple goalieStats entries for each goalieID? If not, then there's no point in having separate tables for this data. You really only need 5 tables:

    Table 1 - entryInfo:
    - entryID
    - lastName
    - firstName
    - password
    - email

    Table 2 - playerInfo:
    - playerID
    - lastName
    - firstName
    - goals
    - assists

    Table 3 - goalieInfo:
    - goalieID
    - lastName
    - firstName
    - wins
    - shutOuts

    Table 4 - playerTeams:
    - entryID
    - playerID

    Table 5 - goalieTeams:
    - entryID
    - goalieID

    You don't necessarily need fields for storing totals values either. You have the individual values -- goals, assists, wins, shutOuts -- you can use the SQL SUM() function to add these values for any particular user when you need them.

    - Marshall

  3. #3
    SitePoint Zealot MediaOhgr's Avatar
    Join Date
    Jul 2001
    Location
    Calgary, Alberta, Canada
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Marshall! Not only did you answer my question, but you added some extra info!

    Yeah, I know that the SQL statements may not work with MySQL. I test them all locally before putting them on the web. To this point what I've need done is working fine. So far so good!

    Thanks!
    MediaOhgr - But then again, maybe it's just me ...


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
  •