lakeshoretech Posted November 7, 2010 Share Posted November 7, 2010 I am pulling records from a mysql db. Example Output: Team A= Our Team Team b = An opponent team Team A score = 7 Team B score = 8 Team A = Our Team Team b = Another Opponent Team A score = 3 Team B score = 1 What I need now is to add ONE more output, which is whether this is a "WIN" or a "LOSS". Should I add a field into the table that calc's this? If so, how? Please be specific. Or, should I use php to compare Team A score vs Team B score PER every record and then insert this new variable into the output. If so, how do I create a new variable value for every single output in the table? I know ultimately that this is a IF THEN type of calc, but can't quite figure out how specifically. Please be specific. Thanks Quote Link to comment Share on other sites More sharing options...
Anti-Moronic Posted November 7, 2010 Share Posted November 7, 2010 I guess it's down to your required uses. If you want to later count the amount of wins/losses per team or in total, or per season etc etc, then you will have to create a new column in your database for that, or a new table to hold data about winners/losers. This would then update the database at final score and set whether the team lost or won, or depending how your database is setup, *which* team lost or won. Can't be more specific than that without details of how your tables are setup and your uses. Quote Link to comment Share on other sites More sharing options...
lakeshoretech Posted November 8, 2010 Author Share Posted November 8, 2010 After further thought on your comments, it seems wiser to actually add a win loss column in the db itself. Rather than have to type/choose a "win" or "loss" when a user enters game data (they will be updating scores via detailed updates). ie. i want field to calc simple win or loss result once the score field is not null. I don't want the user to "determine" this. Or, it creates the field just as a auto number field is created. Any examples on how/where to define or create this field? Thanks again! Quote Link to comment Share on other sites More sharing options...
ninedoors Posted November 8, 2010 Share Posted November 8, 2010 We will need your database structure to be able to help you add the win/loss field you are looking for. Just a note that adding this field breaks relational database law in that you have a column in a table that depends on another columns data. Meaning if a user inserted a score incorrectly say home 7 away 8 it would record a win for the home team. But if you realize your mistake and change the score to say home 7 away 7 you will have to have your program update the win/loss column as well. It doesn't sound like much but unless you are going to have 500, 000 games in the database I would calculate the result as you pull out the scores. I do this with my system that has around 2000 games in it and the query is pretty quick. Just my 2 cents. Nick Quote Link to comment Share on other sites More sharing options...
lakeshoretech Posted November 8, 2010 Author Share Posted November 8, 2010 ninedoors - The scale I'm talking is less than 500 games (records). If you think using PHP is the way to go, I will go there. I just want it correct and eliminate any opportunity for it to show the wrong result (once the code is correct that is). I will be outputting several games (game id's) at once. (by game id) gameid ourscore theirscore CALC FIELD teamname 434 5 1 = = = = WIN U12 White 435 1 9 = == = = LOSS U13 BLUE 487 22 1 - - - - - WIN U11 White 157 6 8 - ----- LOSS U12 White also in a separate pull / output / page (same concept though).... (by team id) (count) (count) (calc) teamid WINS LOSSES % teamname 43 1 0 1000% U11 White 22 1 1 500% U12 White 31 0 1 0% U13 Blue Quote Link to comment Share on other sites More sharing options...
ninedoors Posted November 9, 2010 Share Posted November 9, 2010 Hey Lake, I can definitely help you get this setup. So we don't have to do things twice, I just have a couple questions. [*]Do you have any tables setup in your database right now? [*]How are you going to go out entering the scores? Are you going to use an html form? [*]Are you going to be the only one entering the scores or will other people be doing it as well? [*]Do you have a schedule table setup already? If yes, what is it's structure? It take long for you to get this setup after we figure out these questions. Nick Quote Link to comment Share on other sites More sharing options...
jdavidbakr Posted November 9, 2010 Share Posted November 9, 2010 Something like this? select ourscore, theirscore, if(ourscore > theirscore, 'WIN', if(theirscore > ourscore, 'LOSS', 'TIE')) calc; Quote Link to comment Share on other sites More sharing options...
lakeshoretech Posted November 10, 2010 Author Share Posted November 10, 2010 Ninedoors - Nick - Here's more than you want but it should answer all questions from above. Anyone else with input, I'm very open to criticism/feedback on any aspects. I really appreciate your help. It's amazing how many hours someone can spend looking for something when someone else just did it. That's why these forums rock (to avoid that when possible). I promise to give back some day. I also added a few "todo" related issues I have yet to figure out at the bottom, though it's beyond the scope of what I asked. Anyone, feel free to chime in if you have any suggestions. (making myself a list here as well) Hey Lake, I can definitely help you get this setup. So we don't have to do things twice, I just have a couple questions. [*]Do you have any tables setup in your database right now? [*]How are you going to go out entering the scores? Are you going to use an html form? [*]Are you going to be the only one entering the scores or will other people be doing it as well? [*]Do you have a schedule table setup already? If yes, what is it's structure? It take long for you to get this setup after we figure out these questions. Nick 1 - Do you have any tables setup in your database right now? Yes. Here are the tables, with what I believe are the key fields. uba_players - - - player_id, player_age, player_team_id_fall2010, player_team_id_ss2011, player_status uba_results- - - - game_id, player_id, off_runs, def_p_runs (plus a ton of other fields) - - - 100's of records, each game has 12 lines of data (per player) (total of off_runs vs total of def_p_runs per game_id = game score = win or loss) uba_schedule - - - team_id, game_id, sch_date,sch_uba_score,sch_opp_score,sch_season,opp_name, game_location (sch_uba_score vs sch_opp_score = win or loss, would like it if i could pull these fields from totals in results table, currently hard keying both results detailed data and then score again in uba_schedule) uba_teams - - - team_id, team_name, team_age, team_coachid, team_season other tables incl. coaches/uniforms/fees/etc 2 - How are you going to go out entering the scores? Are you going to use an html form? Coaches with no technical expertise will enter scores (into uba_results) on front end via html forms. Will take them 10 minutes or so. Will need to be able to edit data here as well, by game_id. (coach sees later that ge put 2 strike outs for player x, and it should have been 1) Would like to have a check sum before or immediately after they submit detail that says something like... "you are about to submit the following... that you lost x game by a score of x to y and it's about to be updated in the public results, submit only if correct, else edit". Would also like to feed this "score" to the uba_schedule table, rather than having to key the score again. Not sure how or if poss. 3. Are you going to be the only one entering the scores or will other people be doing it as well? Right now I am hard keying into the db until it's up and running. Once ready, only coaches will update their game data after the individual games. They will have password access to get at the insert/edit form. I will always be able to get at any of it, but only as a backup plan. I will update schedules into the uba_schedule table, hopefully only rarely and at beginning of seasons or when new games are added. 4. Do you have a schedule table setup already? If yes, what is it's structure? Yes, see above. Would be fairly easy to adjust/edit tables though. Current coding examples below.... A) Show Schedule (pick your team or see all teams) <?php $username=ggg; $password="bbb"; $database="111"; mysql_connect (localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $result = mysql_query( "SELECT DISTINCT uba_teams.team_name, uba_teams.team_id FROM uba_schedule INNER JOIN uba_teams ON uba_schedule.team_id = uba_teams.team_id WHERE uba_schedule.sch_uba_score IS NULL ORDER BY uba_teams.team_name ASC" ) or die("SELECT Error: ".mysql_error()); $options=""; while ($row=mysql_fetch_array($result)) { $value=$row["team_name"]; $value2=$row["team_id"]; $options.="<OPTION VALUE=\"$value2\">".$value.'</option>'; }?> <form action="" method="post"> <SELECT NAME=team_id> <option value='999' >See all teams</option>; <option value="pick" selected="selected">Pick your team</option> <?php echo $options?> </select> <input type="Submit"><br><br> <?php $team_id = JRequest::getVar('team_id'); $username=wertqwertwert; $password="xcvxcvxcvxcv"; $database="fghfghfghfgh"; mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="SELECT uba_teams.team_coach, uba_teams.team_name, uba_schedule.sch_date, uba_schedule.game_location, uba_schedule.game_time, uba_schedule.opp_name FROM uba_schedule INNER JOIN uba_teams ON uba_schedule.team_id = uba_teams.team_id WHERE (uba_schedule.sch_uba_score IS NULL) AND (CASE $team_id WHEN 999 THEN uba_teams.team_id ELSE uba_teams.team_id = $team_id END) ORDER BY uba_schedule.sch_date ASC "; $result=mysql_query($query); $num=mysql_numrows($result); ?> <?php $team_name ?> <table border="1" cellspacing="1" cellpadding="10"> <tr> <th><font face="Arial, Helvetica, sans-serif">Coach</font></th> <th><font face="Arial, Helvetica, sans-serif">UBA Team</font></th> <th><font face="Arial, Helvetica, sans-serif">Date</font></th> <th><font face="Arial, Helvetica, sans-serif">Location</font></th> <th><font face="Arial, Helvetica, sans-serif">Time</font></th> <th><font face="Arial, Helvetica, sans-serif">Opponent</font></th> </tr> <?php $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"uba_teams.team_coach"); $f2=mysql_result($result,$i,"uba_teams.team_name"); $f3=mysql_result($result,$i,"uba_schedule.sch_date"); $f4=mysql_result($result,$i,"uba_schedule.game_location"); $f5=mysql_result($result,$i,"uba_schedule.game_time"); $f6=mysql_result($result,$i,"uba_schedule.opp_name"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td> </tr> <?php $i++; } ?> B) Show Completed Game Scores (by team or all) but Win Loss does not work yet (hard coded "WIN") <?php $username=jjjjj; $password="8956896789678"; $database="bnmcvbncvbn"; mysql_connect (localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $result = mysql_query( "SELECT DISTINCT uba_teams.team_name, uba_teams.team_id FROM uba_schedule INNER JOIN uba_teams ON uba_schedule.team_id = uba_teams.team_id WHERE uba_schedule.sch_uba_score IS NOT NULL ORDER BY uba_teams.team_name ASC" ) or die("SELECT Error: ".mysql_error()); $options=""; while ($row=mysql_fetch_array($result)) { $value=$row["team_name"]; $value2=$row["team_id"]; $options.="<OPTION VALUE=\"$value2\">".$value.'</option>'; }?> <form action="" method="post"> <SELECT NAME=team_id> <option value='999' >See all teams</option>; <option value="pick" selected="selected">Pick your team</option> <?php echo $options?> </select> <input type="Submit"><br><br> <br><br> <?php $team_id = JRequest::getVar('team_id'); $username=asdfasdfasdf; $password="456756u845678"; $database="bncvbncvbn"; mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="SELECT uba_teams.team_coach, uba_schedule.sch_date, uba_schedule.game_location, uba_schedule.game_time, uba_teams.team_name, uba_schedule.sch_uba_score, uba_schedule.opp_name, uba_schedule.sch_opp_score FROM uba_schedule INNER JOIN uba_teams ON uba_schedule.team_id = uba_teams.team_id WHERE (uba_schedule.sch_uba_score IS NOT NULL) AND (CASE $team_id WHEN 999 THEN uba_teams.team_id ELSE uba_teams.team_id = $team_id END) ORDER BY uba_schedule.sch_date DESC "; $result=mysql_query($query); $num=mysql_numrows($result); ?> <?php $team_name ?> <table border="1" cellspacing="1" cellpadding="10"> <tr> <th><font face="Arial, Helvetica, sans-serif">Coach</font></th> <th><font face="Arial, Helvetica, sans-serif">Date</font></th> <th><font face="Arial, Helvetica, sans-serif">Win/Loss</font></th> <th><font face="Arial, Helvetica, sans-serif">UBA Team</font></th> <th><font face="Arial, Helvetica, sans-serif">UBA Score</font></th> <th><font face="Arial, Helvetica, sans-serif">Opp</font></th> <th><font face="Arial, Helvetica, sans-serif">Opp Score</font></th> </tr> <?php $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"uba_teams.team_coach"); $f2=mysql_result($result,$i,"uba_schedule.sch_date"); [b]$f3="WIN";[/b] $f4=mysql_result($result,$i,"uba_teams.team_name"); $f5=mysql_result($result,$i,"uba_schedule.sch_uba_score"); $f6=mysql_result($result,$i,"uba_schedule.opp_name"); $f7=mysql_result($result,$i,"uba_schedule.sch_opp_score"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td> </tr> <?php $i++; } ?> C) Need Standings/Team Summaries winloss summary SS2011 season Team Wins Losses % U12 White 7 4 565% U11 Blue 8 8 500% U10 White 4 3 400% U13 7 7 100% D) Box Scores game_id season uba team anem and score their team name and score win or loss player stat1 ab ba% obp% stolen bases singles pa player1 8 4 456% 333% 2 1 5 player5... player6... player8... 3 2 666% 656 1 2 3 total team 11 6 etc Right now, I can get the total team calc's correct, but i can not figure out how to list individual players and have their stats show correctly for a given game. D) Stat Leaders User can pick.... team or all teams age level or all age levels specific stat pitching or defensive stat results... Batting average (U14) 1) BOB PLAYER 454% 2) BOB SMITH 300% ... 5) BOB DOLE 232% or... Batting average (ENTIRE LEAGUE) 1) RICK JAMESR 454% 2) SCOOTER 300% ... 5) JOE MONTANA 232% or PITCHING ERA (SPECIFIC TEAM) 1) Joe Blow 1.21 2) Joe Pitcher 2.3 ... 5) Jack Morris 8.9 Quote Link to comment Share on other sites More sharing options...
ninedoors Posted November 10, 2010 Share Posted November 10, 2010 Hey Lake, So I used the code you posted and just cleaned it up a bit and added the part to get your win/loss dynamically. Make you add the style I put at the top of each script in your style you include in each page. Just saves you adding it to each td tag. These "should" get you at least your win/loss column working. The rest on the stuff you mentioned will be a lot more work and will take quite awhile to get it implemented but is definitely attainable. I have all these type stats setup on my mens hockey league website. It is still a work in progress and I have been at it for 5 years now!!! Here's my site. The main thing you should do is sit down and decide exactly what stats you want and might want to capture from your baseball games, now and for future. Even if you are entering something in the database now and aren't using it, it's good to have it there if you eventually decide you would like to track it. I will help you out if you want as I am actually about to start a baseball website for my men's league so it would be good to get the train moving. Nick [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
lakeshoretech Posted November 11, 2010 Author Share Posted November 11, 2010 Nick - I can't get to this until tomorrow, but thanks in advance. I will reconnect with you once I do that. Perhaps I can help you avoid duplication for your men's softball db (not yet of course) once I make some further progress. For me, the most frustrating thing is converting mentally from Excel to SQL. I'm an expert (yes, I'l say that) in Excel but it's a tough transition to learn this new (to me) language/syntax. Frustrating for sure... but with so many helpful people, it makes it quite doable. I'l be in touch. Thanks Tim Quote Link to comment Share on other sites More sharing options...
lakeshoretech Posted November 13, 2010 Author Share Posted November 13, 2010 Kudos to Nick at ninedoors.... this guy is helpful and knows what he's doing. Thanks. If anyone has experience or is working on anything similar (i.e. baseball stats), I'd love to hear from you. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.