an update script for php/mysql

[unclescrooge]

hi,

i am creating an interface for my baseball team to allow our coach to enter in our at bats and our hits.

i want it to pull the previous number for each batter out of the db, add the number of at bats and hits to it and then update the corresponding cell for that player

sorta like:
SELECT from BATTERS where name=$batter

$atbats = $abs +$currentatbat
$hits = $basehits =$currenthits

and that's about as far as i get. i can't figure out how to update the cells.

[Tekime]

Check out the UPDATE syntax: http://www.mysql.com/doc/en/UPDATE.html

You would probably do something along the lines of:

UPDATE batters SET atbats=$atbats, hits=$hits WHERE name=$batter 

Although you may want to use a unique key for batters, otherwise no two batters can have the same name since it wouldn't be unique.

[unclescrooge]

ah...so in that case i'd be better off using their numbers as the key. good idea.

[Begby]

I wouldn't use their number as a key since numbers get reused and you might want to reuse the database at some point and keep old data. Like if one year joe, player #42 leaves and someone else gets his old number, now you have to edit your DB and you will permanately lose Joe's stats. Probably good idea to use autonumber for a primary key in this case.

[Tekime]

Yes like Begby said you should use an autonumber. There is no pressing reason why you want to do otherwise, and chances are #42 will be gone eventually (or any other number obviously).

[OS-Wiz]

A better key would be playerNumber and playerName. That way you can retain history should someone return with their old number. I'd also add 'year' to the row.

[Grizzly]

And if you wanted to take it one step further, an even more flexible design would seperate the "team-number" as an entity on to its own. That way relationships could exist between numbers, and players in a many-to-many fashion, and those relationships would probably be best defined with a time-span.

That would be a more normalized approach than the compound-key OS-Wiz suggested, since a compound-key would demand that player "X" have his profile repeated for every team-number he needed to be related to.

But I'm guessing this isn't some enterprise database you're designing - so I'd have to think that a more simple, but less flexible design would be your best option here.

[unclescrooge]

thanks guys...i should've known i was over my head when i hunted on the web and couldn't find one script anywhere that dealt with baseball stats using mysql.

and the ideas about teams and years is a good idea but the whole purpose of this is to simplify the computation of our batting averages for display on our website.

no one had really planned on keeping these stats from year to year but that is a valid concern and i think i need to keep that in mind.

i like the year/number as the key...ie i'm #21 and this is 03 so i'd be 0321.

the displayed table will show, picture, number, name, position(s), and batting average.

also i've never done pictures via a db. should i try it via php/mysql of just use a regular html line for that?

fwiw...my wife calls this "little league for old men"