lakeshoretech

Thanks. Perfect.

s0c0 - ? Why I'd want to what? You lost me. Needing my result to be a "batting average" of sorts. Eliminate zero (but not a 1) before the decimal and round to 3 places.

$f15 = 0.345677 (output from sql data pull) <?php echo number_format ($f15,3); ?> output = 0.345 <?php echo ltrim($f15,"0"); number_format ($f15,3); ?> output = .345677 How do I combine these so that I am BOTH rounding the result AND trimming a leading zero? Seems I can get one or the other, but not both. Suggestions on the syntax?

Brilliant. Issue resolved. Thanks

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.

PHP/MYSQL - - - Thank you in advance for any help. Quick question. I am calc'ing whether each line db output is a win/loss/tie below. Outputting $res in each line. Is there a simple way to COUNT the number of instances where $ res = "win" or loss or tie? I'd like to put an "overall" record at the top of the output. For example, for a given query, this chosen team's record is x WINS, Y Losses, Z TIES. Win/Loss/Tie is NOT a field in the db table. I know how to pull the total # of records pulled, but not this. Example User pulled a certain team to see game scores. 7 - 3 - 2 is their record. is what i'm trying to figure out Game Result Score Score opp Date game10 WIN * 7 2 blah game11 LOSS* 2 3 .... .... //winloss begin works * above is a result of this code if ($row['sch_uba_score'] > $row['sch_opp_score']) $res = 'Win'; elseif ($row['sch_uba_score'] < $row['sch_opp_score']) $res = 'Loss'; else $res = 'Tie'; // winloss end works

That was it. Thank you.

To explain the reason for the CASE statement (I would use something else if recommended) User has two choices before they pull the search. 1) AGE (player_age) - ALL ages = 999, or a specific age selection AND 2) STATUS (player_status) - ALL status = 999, or a specific status selection (on the 2nd case, I'm just trying to get it to work at all with a hard coded 999)

Can someone take a look at this? I'm trying to add a 2nd case condition to my where statement. This works perfectly UNTIL I add the second CASE, then I get no output whatsoever. Variables are from a user form. When I hard code these, it pulls correctly (one at a time). Am I missing something obvious here? WHERE.... uba_players.player_status <> '7 - Quit' AND (CASE $player_age WHEN 999 THEN uba_players.player_age like '%' AND ELSE uba_players.player_age = $player_age AND END) (CASE '999' WHEN 999 THEN uba_players.player_status like '%' ELSE uba_players.player_age = $player_status END) ORDER BY.....

Awesome. Thank you.

I would like some advice from someone who already has the quick answer. User uses a form (drop down) to define a variable in their search of the db. PHP/MYSQL Choices are ALL, A, B, C. EXAMPLE1 They choose B. $variable = 'B' SELECT .... WHERE.... x table value = $variable (equals B) All works perfectly. EXAMPLE2 Now if they chose ALL (or don't chose), I have a problem. They choose nothing (which should be ALL records). I need it to go back to having no WHERE portion of the sql command, or a WHERE that pulls all records. They choose nothing. $ variable = 'ALL' SELECT ... WHERE... x table value = $variable (which now equals ALL) I thought % was a wildcard but doesn't work. This seems simple... but eludes me. What should I use for a variable value if there is NO selection or ALL selection???? The WHERE x table value = has to equal SOMETHING, right? Should I be using an IF THEN to zap the line if it equals "all"? Any advice would be helpful! Thanks!

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

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)

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

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!

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

Yucky - You rock. Yeah, yeah... I know, it's obvious. But not after you've stared at it for 3 hours. Thanks again.

If anyone can help find the issue here.. I would much appreciate it! Also, if you see anything whacked out, please feel free to point that out (esp the CASE WHEN as I'm looking to do a IF THEN in the WHERE to show ALL records if no choice is made by user) ERROR: Parse error: syntax error, unexpected T_LNUMBER in /home/ubas..... port/public_html/modules/mod_php/mod_php.php(36) : eval()'d code on line 59 Upcoming Schedule <form action="" method="post"> <select name="team_id"> <option value="33" selected="selected">default pick</option> <option value="999">team 999</option> <option value="11">team 11</option> <option value="22">team 22</option> </select> <br><br> <input type="Submit"> </form> // FROM HERE <?php $team_id = JRequest::getVar('team_id'); $username=xx; $password="sss"; $database="5555"; 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); ?> <table border="2" cellspacing="2" cellpadding="2"> <tr> <th><font face="Arial, Helvetica, sans-serif">player_id</font></th> <th><font face="Arial, Helvetica, sans-serif">first</font></th> <th><font face="Arial, Helvetica, sans-serif">last</font></th> <th><font face="Arial, Helvetica, sans-serif">age group</font></th> <th><font face="Arial, Helvetica, sans-serif">game id</font></th> <th><font face="Arial, Helvetica, sans-serif">coach name</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_coach"); $f3=mysql_result($result,$i,"uba_teams.team_coach"); $f4=mysql_result($result,$i,"uba_teams.team_coach"); $f5=mysql_result($result,$i,"uba_teams.team_coach"); $f6=mysql_result($result,$i,"uba_teams.team_coach"); ?> <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++; } ?>