Sports scores pages, need advice

[smk17]

I'm quite new to PHP and mysql but I am studying my butt off.

 

I run a website dedicated to boys high school basketball in the New York area. Right now it is 100% static.

 

The area I have questions on are the schedule pages.

 

There are 70 teams that I cover. I want each team to have their own schedule page for the upcoming season. Right now those schedules are in tables on the html page, each on their own page and I manually create them each season and also manually enter the game scores after the game takes place. Since two teams play each other, I have to open team A's page and team B's page and manually type in these scores.

 

What I'd like to happen.

 

I've created a database that, for example, has a table in it called "candor_schedule". It has 7 columns of DAY OF WEEK, DATE, TIME, H/A, OPPONENT, VARSITY_SCORE, JV_SCORE.

 

I have been able to figure out how to create a form where I can enter the schedule, hit submit and have it populate the database and then I have a web page set up where the database info feeds into that page. That I have figured out. Of course I do not enter the game scores yet because they haven't been played yet.

 

And since I have to fill the schedule table out before the season starts, I need to go back and enter the game scores weeks later into the same table? But this is not the correct way to do this I know.

 

I'd like to have a form where I can enter the game (both teams, both scores, and then that info gets fed into each teams schedule, so I don't have to open up both teams pages, I can do it once from one place.

 

Can someone guide me in the right direction and try to explain the structure that might work? Do I need to make another table for the scores? And do I need a table for each team?

 

Here is the static page of one of the schedule pages: http://section4hoops.com/pop/iac_schedules_08/candor_s.htm

 

And here is the dynamic page test: http://section4hoops.com/php_test/index.php

 

Thanks for any help

Steve

[eatfishy]

From my understanding, it sounds like you just want to update the table with the scores after when 2 teams play against each other without manually typing it in the HTML editing form?

 

Since the table is setup and you added records into it, but you want to go back and edit the record (put the actual score in). You need to do an update statement in MySQL.

 

mySQL statement ex:

 

Update TableName

Set ColumnName='70-20'

WHERE Game='Bears Vs Tiger' --or use GameID='25'

 

Something similar to the query above. Get it to work in MySQL first and then PHP.

 

I can be found at www.blogoberry.com if you need more help

[DavidAM]

I would not have a seapate table for each team's schedule.  But I would build a structure with a couple of tables.  I get the impression that the Varsity & JV schedules are the same, so I'll keep those together.  Here's how I would setup the database (off the top of my head, this may need work):

 

Table: Teams

TeamIDintegerAutoIncrement PrimaryKey

Schoolvarchar(50)not null

TeamNamevarchar(50)not null

 

Table: Schedule

SchedIDintegerAutoIncrement PrimaryKey

HomeTeamIDintegerForeign Key to Teams

VisitTeamIDintegerForeign Key to Teams

GameDatedatetimeNOT NULL

VarsityScoreintegerDEAULT NULL

JrVarsityScoreintegerDEFAULT NULL

 

You could easily get any team's schedule (with scores) using a (fairly) simple query:

SELECT DATE_FORMAT('%a %b %e', s.GameDate) AS GDate, 
	DATE_FORMAT('%r', s.GameDate) AS GTime,
	h.TeamName AS Home, v.TeamName as Visitors, 
	h.VarsityScore AS HVScore, vVarsityScore AS VVScore,
	h.JrVarsityScore AS HJScore, vJrVarsityScore AS VJScore
FROM Schedule s JOIN Teams h ON s.HomeTeamID = h.TeamID 
JOIN Teams v ON s.VisitTeamID = v.TeamID
WHERE $GetSchedForID IN (s.HomeTeamID, s.VisitTeamID)
ORDER BY s.GameDate

(I'm not 100% sure about the JOIN statements there, I'm not where I can test it.)

But this would let you use a Team's ID from PHP ($GetSchedForID) to get their entire schedule.  Scores would be returned and would be empty for future dates.  The date and time are combined in one field, and we extract the Day of Week from the date rather than store it separately.

 

For updating the scores, I think I would use a form that presents all games for a selected date (or date range), then enter all of the scores for that date (range) on one page, and have the PHP post script issue updates to the database.  The advantage of this design, is you only have to update and query one table for any team, and you only have to update one row for each game.

[smk17]

THanks for the direction, I'm not up to this level yet, but it's good to know what to work on.

[marvelade]

THanks for the direction, I'm not up to this level yet, but it's good to know what to work on.

 

parallel to studying PHP you really should also get basic knowledge on MySQL databases.

 

[smk17]

Yep, doing that also, thanks