Newbie question - data entry form for team/match players

[PeteB]

Hi all, sorry, but I have a newbie question which is probably PHP or Javascript or Ajax related, but the data is held on MySQL tables so I'll start here if that's o.k.?

Essentially I am converting my database from OooBase (previously MSaccess) and want to mimic a form/subform combination that I use for data entry.

I have 3 tables:
Games
which has the fields 'gameid', 'MatchDate', 'Opponents', 'PtsFor', 'PtsAgainst',...etc

Players
Which has the fields 'playerID', 'Firstname', 'Lastname',.. etc

Game_players
Which links the 2 tables and so has the fields:
'gpID', 'gameid', 'playerID', 'goalsscored', etc...

So...
My end result is to have a form which allows me to enter the Game details and a subform to enter the players for that game.

My question regards - what is the best way to lookup and enter the list of players ?

Thanks in advance for any design suggestions.

[djr33]

From what you've said, it's a little hard to understand exactly what your level of experience with MySQL/PHP/etc is. My impression, though, is that you understand MySQL and PHP well and are now concerned with making an easy user interface to interact with your database.

To start with the basics, you will need a player id (or set/list of player ids) to actually process it in the end. You also start with a list of player ids in the database. You also have the ability to display a name from a given ID.

That said, now the question is how you actually want to handle the search process. A major consideration will be how many players will exist in the database. If this is a team of 12 people and there will never be more than that, it won't be very difficult to organize. If this is a list of many players (100? 1000?) maybe even split across different teams, then that gets more complicated.

So what you will want to do is somehow end up with a list of IDs for the players that should be added to the game. However, since you are adding a separate row for each, I imagine a separate form for each. One way to do this would be very basic: have a link "add player" and a list of existing players, within each game. Next to each player, "edit" and "delete" links as well.
In the add/edit player page, you can create a form that contains a dropdown select menu for player name (and use as the value of each item the player's ID), then you can have a field for 'goalsscored', etc.

If you want to integrate this into a single page without reloading a new form for each player, that's a little more complicated. You could do it using a dynamic form in JS: generate a new subsection of fields for a new player automatically. You can find information about that on google. Basically google "Javascript add form input" and take what you find and expand/change it to fit the situation here.

Finally, if you are thinking about a player "search" option, then you could make a popup that has a Javascript function to set the value of a field on the main page to the player ID when you select a certain player. That's a fairly complicated version.


Does that help? Do you have any specific requirements?

[PeteB]

Daniel, thanks for the quick reply and you have pretty well grasped what I was trying to achieve.

My background is in IBM/CICS/DB2 Mainframes but I have been 'dabbling' in web based stuff for a few years now.

If this was something for my 'day-job' I'd have a 80*25 screen where I'd enter all the details and have a program read that screen and set up the database records as appropriate - I'm a bit of a dinosaur and so I often need a bit of a nudge to get out of that way of thinking.

Can I just clarify what you are saying..
I have 2 choices...
1) A 'screen' with lots of html forms, each one with a 'Select Player' drop down box (e.g. the equivalent of a MsAccess 'Continuous form')?
or
2) A screen with one form and each player is added individually (the growing list of additions can be displayed elsewhere on the page) ?

The data is for a Rugby club and my draft website can be found here: http://dingsrfcstats.info

My specific task at the moment is not for the website, but for the entry of the data to MySQL.

The application (OooBase/MsAccess) that I am trying to replace is like Option 1) but for my new MySQL system I think I prefer option 2).

SO, specifically:
Step 1) is to have a screen where I enter the game/match details like date, opponent, venue, scores.
Step 2) this enters a record onto the Games table (gameID) generated by Auto_increment
Step 3) A second screen with a drop-down box which is built from the Players table
Step 4) Select a Player from the dropdown to be added to the game_players table using the gameid and playerID selected.

Using Google, I've seen seperate examples of most of the steps above, but not one 'all singing/all dancing' example to confirm that it is the best method achieving what I want.

Any pointers gratefully received.
Thanks,
Pete.

[djr33]

Hi Pete, you're doing fine. Your steps 1-4 are correct. Step 4 might be a multiple player on one page step, or repeated for each player, on separate pages. Regardless, it does go in that order. Think of it like making a basket (the game) then putting the players in it. You can't put them in something that doesn't exist yet, so everything you've said makes complete sense.

1) A 'screen' with lots of html forms, each one with a 'Select Player' drop down box (e.g. the equivalent of a MsAccess 'Continuous form')?
or
2) A screen with one form and each player is added individually (the growing list of additions can be displayed elsewhere on the page) ?

Yes, those are basically your two options. There is a third option, a kind of combination of the two.
That would be to create a dynamic page that allows multiple entry but isn't an overwhelmingly long list of blank inputs.
There are two general approaches:
1. Use Javascript to have "add player" links and use that to generate new fields on the page for each player. So you can click "add player" and a new subsection of the form will show up for adding a new player. Do this for as many players as you have. Then when you process the data on the server, you can use an array (name the inputs in the form as an array*), and input them all, however many there are, into the database.
2. Hide the complexity from the user by making a nice interface. You can use hidden inputs, basically like in (1) but not visible to the user, and allow a more graphic approach: click on a link for the players name, that name appears in a list, etc. This would involve Javascript to make everything move around, then the same methods as (1) to actually save the data.


I would recommend starting with a page to enter games, then a link to add a player. This opens a new page and is connected to the game by game ID. That page has a form for one player (and only one player). Then you repeat that process for each player. This is the most basic way to approach the data and your database can be the same regardless of how you input the data. This means that after you have a fully functional (if slightly awkward) system, you can go back and add a nicer UI and make everything work well.


(*Arrays in HTML inputs work like this: <input name="fruit[ ]" .....>. Crucially, name several inputs this same thing. HTML will then automatically, when you submit the form, process them into a list so that every "fruit" is part of the "fruit" array. But you don't need to specify an index for each one, like fruit1, fruit2, etc. Then there will be an array named $_POST['fruit'] available in PHP. Google will have more information on this detail, or post here asking for specific help with that if you need it.)

[PeteB]

Daniel,
that's great, thanks.

I'll give the 'simple' approach a try. Hopefully, I'll be able to eventually build up to a 'nice' gui interface.

[djr33]

That sounds like a good approach for now.

By the way, here's an example of something from a very old project of mine. I can't really endorse the code as the best method (since it's so old) but it is functional.

It's for managing crew members listed to work on certain scenes in a film, but that's very similar to players in a game.

Here's how it works:
1. Add this Javascript function to the page (in the head section, <script> tags etc):

function addcrewmember(addThis){
	var current = document.shot.crewmembers.value;
	var len = current.length;
	var lchrs = current.substring(len-2);
	if (current!='') {
		if (lchrs!=', ') {
			if (lchrs.substring(1)==',') {
				addThis = ' '+addThis;
			}
			else {
				addThis = ', '+addThis;
			}
		}
	}
	document.shot.crewmembers.value += addThis;
}

2. Make a form named "shot" with an input (actually a textarea) named "crewmembers".
3. Next to that form, add a list of names, each with the following format. Using a database you can easily loop through the list to generate all of them:

<a href="#" onclick="addcrewmember('8'); return false;">John Smith</a>,

(assuming that John Smith is crew member with ID #7)

This is just a basic version that speeds things up, but what it does is makes a list of names you can click and when you click them they are added to a list (just separated by commas) in a form field. This is a little simpler than what you need because you need to modify each player as well, but I think it might give you an idea about creating a somewhat more interactive system.