stuck...

[peanutgallery83]

I've been teaching myself PHP in an attempt to set up an online referee assigning program for a local soccer referee association. So far it has been going well, I've gotten all of the administration system set up, as well as individual account stuff, but I've gotten stuck.

Any advice would be GREATLY appreciated. I'm not sure of how to do this using PHP....

What I'm trying to do:

I need to set it up so that each individual official can log into the system and select dates that they are availabile to work games.

On the master schedule of games that need officials assigned, there is a spot for a "referee" "ar1" and "ar2". I need to be able to create a dropdown menu in each of these 3 spaces on each game that will only list officials who said they were available on the day of the game in question.

What would be the best way to set up the date selection? Do I need to create a 365 field table, one field for each day? Then how do I set it up for the drop down menus to show up on the schedule (which is pulling the list of games from a table ("schedule") in the database "test_db" ).

An example of an old email-based form used for availability now can be found at http://www.ecisoa.org/pages/referee_availability.html if it will help understand what I'm trying to do.

Thanks again for any help!

Landis Wiley

[dalecosp]

Originally posted by peanutgallery83
What I'm trying to do:

I need to set it up so that each individual official can log into the system and select dates that they are availabile to work games.

This whole thing is basically an exercise in database design, which I'm not real good at. We'll give it a whack...

First, do you know how to interact with the database? Do you have one?

Second, iis the schedule of games finalized in terms of dates?

On the master schedule of games that need officials assigned, there is a spot for a "referee" "ar1" and "ar2". I need to be able to create a dropdown menu in each of these 3 spaces on each game that will only list officials who said they were available on the day of the game in question.

Not tremendously difficult if you have good db design.

What would be the best way to set up the date selection? Do I need to create a 365 field table, one field for each day? Then how do I set it up for the drop down menus to show up on the schedule (which is pulling the list of games from a table ("schedule") in the database "test_db" ).

What does the schedule table look like?

I'd definitely take a Google around for "database design". There are some semi-complex interactions here, and designing a good set of tables will go a long way in making the PHP scripting easy.

You have several datum that need to be stored: dates, games, probably teams, officials ... also need an "available officials", perhaps. I sure wouldn't make a 365-field table unless I had games almost every day of the year.

Perhaps one idea would be a table like this, maybe called "refs_available":

Game-Date   |  AR-1 | AR-2 | AR-3 | AR-4 | AR-5 | AR-6

It would depend highly upon how many officials you have "in total" as to how well that might work, and there might be a better way, but I don't know ATM what I'd suggest.

Probably what I would consider first, after putting some db tables together, is simply grabbing an array of the dates of all the games for which I need officials, and printing it as a list with checkboxes ... you have a login/ID system for officials, they mark when they're available, you store the data. The trick is storing it in a way that the calculation isn't too difficult (either to program, or for the server to crunch quickly enough for a page load ... probably not a big issue unless it's a very busy box or you have a huge algorithm....

HTH,

[peanutgallery83]

thank you for the help. to answer your questions, I currently have 2 tables set up.

table "reflist" contains all the data about each official which is collected when each official registers at the site, and looks like this:
id | f_name | l_name | address_1 | address_2 | city | state | zip | prim_phone | alt_phone | email | username | password

The table "schedule" stores all data about each game, including, obviously, the game scheduled, as well as the referees who are assigned to the game:
id | date | time | location | home_team | visiting_team | league |referee | ar1 | ar2

[dalecosp]

So, you can add one or more columns to the table "reflist" which you'd use to store information about their availability; OR, you could write a script that would peruse the "schedule" table, see which rows didn't have both "ar1" and "ar2" full, list them, and provide some type of form so they could click themselves into an afternoon of work 😉, OR, create some 3rd table that would somehow bridge the gaps.

I'd pick option 2 or 3, myself; you might ask the guys over at the database section of the board about it; or hang around and see if someone else takes an interest in your thread...