PHP or sql - pulling data - determine A or B

lakeshoretech · 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

Anti-Moronic · 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.

lakeshoretech · 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!

ninedoors · 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

lakeshoretech · 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

ninedoors · 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

jdavidbakr · November 9, 2010

Something like this?

select ourscore, theirscore, if(ourscore > theirscore, 'WIN', if(theirscore > ourscore, 'LOSS', 'TIE')) calc;

lakeshoretech · 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"> 
<?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>Coach</th>
<th>UBA Team</th>
<th>Date</th>
<th>Location</th>
<th>Time</th>
<th>Opponent</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><?php echo $f1; ?></td>
<td><?php echo $f2; ?></td>
<td><?php echo $f3; ?></td>
<td><?php echo $f4; ?></td>
<td><?php echo $f5; ?></td>
<td><?php echo $f6; ?></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"> 
 


<?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>Coach</th>
<th>Date</th>
<th>Win/Loss</th>
<th>UBA Team</th>
<th>UBA Score</th>
<th>Opp</th>
<th>Opp Score</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><?php echo $f1; ?></td>
<td><?php echo $f2; ?></td>
<td><?php echo $f3; ?></td>
<td><?php echo $f4; ?></td>
<td><?php echo $f5; ?></td>
<td><?php echo $f6; ?></td>
<td><?php echo $f7; ?></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

ninedoors · 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]

lakeshoretech · 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

lakeshoretech · 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.

Sign In

PHP or sql - pulling data - determine A or B

Recommended Posts

lakeshoretech

Link to comment

Share on other sites

Anti-Moronic

Link to comment

Share on other sites

lakeshoretech

Link to comment

Share on other sites

ninedoors

Link to comment

Share on other sites

lakeshoretech

Link to comment

Share on other sites

ninedoors

Link to comment

Share on other sites

jdavidbakr

Link to comment

Share on other sites

lakeshoretech

Link to comment

Share on other sites

ninedoors

Link to comment

Share on other sites

lakeshoretech

Link to comment

Share on other sites

lakeshoretech

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information