bschultz Posted June 19, 2009 Share Posted June 19, 2009 I have a database that's filled with a roster and stats for a football team. The db has columns like this: number name position height weight year city state rush_attempts rush_yards rush_tds catches receive_yards receive_tds I define which jersey number is playing running back like this: <?php $rb1 = "22"; $rb2 = "25"; ?> I need the select to pull all the info for that player like this: <?php $sql = "SELECT * FROM tablenamehere WHERE number='$rb1'"; $rs = mysql_query($sql,$dbc); $matches = 0; while ($row = mysql_fetch_assoc($rs)) { $matches++; echo "<table><tr><td width='80' class='number'>$row[number]</td>"; echo "<td width='200' ><div class='name'>$row[name] </div> <div class='height'> $row[height] - $row[weight] - $row[year] <br /> $row[city], $row[state] </div>"; echo "</td></tr>"; } if (! $matches) { echo ("<table><tr><td width='80'></td><td width='200' >"); } echo "</table>"; ?></td> I'd like to combine all the selects into one GIANT select statement with aliases. Do I need to create an alias for EVERY position on the team AND for EVERY column in the db, or can that be simplified at all? Let me know if you need any more info. Quote Link to comment Share on other sites More sharing options...
ninedoors Posted June 19, 2009 Share Posted June 19, 2009 Can you explain what your desired result will look like on the page. In your position column, what do you insert here? Is it a varchar or an int column? I noticed there is no team column, did you just forget to put that in there? Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 19, 2009 Author Share Posted June 19, 2009 http://brian-schultz.com/football/bsuo.php That's what it will look like on the page. I threw this together last football season, and want to do it right this season. I had a db query and select for EVERY player on that page. I know that's not even close to doing it the right way...! I want two tables...one for "my" team...and one for the opponent. The rosters and stats get imported into the db from a csv excel spreadsheet, and since I only need the opponent once per season, I empty that table and insert the new info for the team that we play that week. All columns are varchar except for the stats columns, which are int. The position field is what position on the team they play...used for another application...not this one. Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 19, 2009 Share Posted June 19, 2009 Hi I have no idea about American football so will probably miss some things. I presume that is the details for a team for a particular game. I also presume that players do not move positions (well, will make that assumption for ease for now). I guess what you want is to pick a game and team and output the stats as shown in your example. As such I think you need several tables Table of games (GamesTab) GameId GameDate Table of teams / Game (GamesTeamTab) GtId GameId TeamId Etc Table of teams (TeamsTab) TeamId TeamName Table of players (PlayersTab) PlayerId PlayerName Basic table of positions, along with data specifying where that position is displayed on screen (PositionsTab) PositionId PositionName DisplayOrder Table of players for a team for a game (ie, linking the above 4 tables) (GameDetTab) Id GameId TeamId PlayerId PlayerPositionId Put that all together and get something like:- SELECT GameDate, TeamName, PlayerName, TeamId, PositionName FROM GamesTab a JOIN GamesTeamTab b ON a.GameId = b.GameId JOIN TeamsTab c ON b.TeamId = c.TeamId JOIN GameDetTab d ON a.GameId = d.GameId AND c.TeamId = d.TeamId JOIN PlayersTab e ON d.PlayerId = e.PlayerId JOIN PositionsTab f ON d.PlayerPositionId = f.PositionId WHERE a.GameId = $SomeSelectedGame ORDER BY b.TeamId, f.PositionOrder That should give you all the teams, and each player in those teams for a match, with first one team and then the other. Players with a team will be ordered by their poisition (ie, some order you define connected to their position). Hope that gives you some ideas. All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 19, 2009 Author Share Posted June 19, 2009 thanks for the help kickstart...I'll start going to work. Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 20, 2009 Author Share Posted June 20, 2009 I REALLY don't want multiple tables. I copy and paste the info from the website of the school we play...and then insert that into excel...and then into the db. Easy to copy and paste...not so easy to copy and paste into 5 tables. Here's what I've tried (for one player) for a multiple select query...and it returns this error and no results... Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /test.php on line 33 no matches This is line 33 while ($row = mysql_fetch_assoc($rs)) { $matches++; Here's the code <?php $lt1 = "68"; $lt2 = "77"; $dbc = mysql_pconnect('xxx','xxx','xxx'); mysql_select_db('fb',$dbc); $sql = "select *, (select number WHERE number = $lt1) as lt_number, (select name WHERE number = $lt1) as lt_name, (select pos WHERE number = $lt1) as lt_pos, (select height WHERE number = $lt1) as lt_height, (select weight WHERE number = $lt1) as lt_weight, (select year WHERE number = $lt1) as lt_year, (select city WHERE number = $lt1) as lt_city, (select state WHERE number = $lt1) as lt_state from bsu"; $rs = mysql_query($sql,$dbc); $matches = 0; while ($row = mysql_fetch_assoc($rs)) { $matches++; echo "$row[lt_number]<br />$row[lt_name]<br />$row[lt_name]<br />$row[lt_height]<br />$row[lt_weight]<br />$row[lt_year]<br />$row[lt_city]<br />$row[lt_state]"; } if (! $matches) { echo ("no matches"); } ?> Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 20, 2009 Share Posted June 20, 2009 Hi Cleaning that up a tad:- <?php $lt = array("68","77"); $dbc = mysql_pconnect('xxx','xxx','xxx'); mysql_select_db('fb',$dbc); $sql = "select number as lt_number, name as lt_name, pos as lt_pos, height as lt_height, weight as lt_weight, year as lt_year, city as lt_city, state as lt_state WHERE number IN ('".implode("','",$lt)."') FROM bsu ORDER BY number, pos"; $rs = mysql_query($sql,$dbc); if (mysql_num_rows($rs) > 0) { while ($row = mysql_fetch_assoc($rs)) { echo "$row[lt_number]<br />$row[lt_name]<br />$row[lt_name]<br />$row[lt_height]<br />$row[lt_weight]<br />$row[lt_year]<br />$row[lt_city]<br />$row[lt_state]"; } } else { echo ("no matches"); } ?> All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 20, 2009 Author Share Posted June 20, 2009 Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /football/test.php on line 19 no matches this is line 19: if (mysql_num_rows($rs) > 0) Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 20, 2009 Share Posted June 20, 2009 Then something's wrong with the SQL. Add an or trigger_error(mysql_error(), E_USER_ERROR) after the mysql_query. Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 20, 2009 Author Share Posted June 20, 2009 Fatal error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE number IN ('68','77') FROM bsu ORDER BY number, pos' at line 1 in /football/test.php on line 18 Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 20, 2009 Author Share Posted June 20, 2009 This work for one player...but I can't get it to work for the second player... <?php $lt1 = "68"; $lt2 = "77"; $dbc = mysql_pconnect('xxx','xxx','xxx'); mysql_select_db('fb',$dbc); $sql = "(select number as lt_number1, name as lt_name1, pos as lt_pos1, height as lt_height1, weight as lt_weight1, year as lt_year1, city as lt_city1, state as lt_state1 from bsu WHERE number = $lt1)"; $rs = mysql_query($sql,$dbc); $matches = 0; while ($row = mysql_fetch_assoc($rs)) { $matches++; echo "$row[lt_number1]<br />$row[lt_name1]<br />$row[lt_pos1]<br />$row[lt_height1]<br />$row[lt_weight1]<br />$row[lt_year1]<br />$row[lt_city1]<br />$row[lt_state1]";} if (! $matches) { echo ("no matches"); } ?> I tried this for the second player...and I got the same error I did in reply #5 above. $sql = "(select number as lt_number1, name as lt_name1, pos as lt_pos1, height as lt_height1, weight as lt_weight1, year as lt_year1, city as lt_city1, state as lt_state1 from bsu WHERE number = $lt1), (select number as lt_number2, name as lt_name2, pos as lt_pos2, height as lt_height2, weight as lt_weight2, year as lt_year2, city as lt_city2, state as lt_state2 from bsu WHERE number = $lt2) "; Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 20, 2009 Share Posted June 20, 2009 Hi Another typo on my part:- $sql = "select number as lt_number, name as lt_name, pos as lt_pos, height as lt_height, weight as lt_weight, year as lt_year, city as lt_city, state as lt_state WHERE number IN ('".implode("','",$lt)."') FROM bsu ORDER BY number, pos"; should be $sql = "select number as lt_number, name as lt_name, pos as lt_pos, height as lt_height, weight as lt_weight, year as lt_year, city as lt_city, state as lt_state FROM bsu WHERE number IN ('".implode("','",$lt)."') ORDER BY number, pos"; All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 20, 2009 Author Share Posted June 20, 2009 That worked...thank you! Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 20, 2009 Author Share Posted June 20, 2009 I still can't add a second position, though... <?php $sql = " (select number as lt_number, name as lt_name, pos as lt_pos, height as lt_height, weight as lt_weight, year as lt_year, city as lt_city, state as lt_state FROM bsu WHERE number IN ('".implode("','",$lt)."')), (select number as rt_number, name as rt_name, pos as rt_pos, height as rt_height, weight as rt_weight, year as rt_year, city as rt_city, state as rt_state FROM bsu WHERE number IN ('".implode("','",$rt)."')) "; ?> Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 20, 2009 Share Posted June 20, 2009 You're just running the same SQL twice. If $rt is an array of positions numbers, kickstart's query should give results for all of them. Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 20, 2009 Author Share Posted June 20, 2009 But I need the second position to be a second alias Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 20, 2009 Share Posted June 20, 2009 Hi Not sure there is an easy way to do that, and to me it just appears to be a way to make the php more difficult. The queries above bring back each player as a seperate line. Simplest way I can think of:- <?php $sql = " SELECT * FROM (select number as lt_number, name as lt_name, pos as lt_pos, height as lt_height, weight as lt_weight, year as lt_year, city as lt_city, state as lt_state FROM bsu WHERE number IN ('$lt')), (select number as rt_number, name as rt_name, pos as rt_pos, height as rt_height, weight as rt_weight, year as rt_year, city as rt_city, state as rt_state FROM bsu WHERE number IN ('$rt'))"; ?> Note this will give really screwey results if there are multiple records for $lt or $rt. All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 21, 2009 Author Share Posted June 21, 2009 I've never seen this error... Fatal error: Every derived table must have its own alias in /football/test.php on line 26 This is line 26 $rs = mysql_query($sql,$dbc) or trigger_error(mysql_error(), E_USER_ERROR); All code... <?php $lt = array("68","77"); $rt = array("65","67"); $dbc = mysql_pconnect('xxx','xxx','xxx'); mysql_select_db('fb',$dbc); $sql = " SELECT * FROM (select number as lt_number, name as lt_name, pos as lt_pos, height as lt_height, weight as lt_weight, year as lt_year, city as lt_city, state as lt_state FROM bsu WHERE number IN ('$lt')), (select number as rt_number, name as rt_name, pos as rt_pos, height as rt_height, weight as rt_weight, year as rt_year, city as rt_city, state as rt_state FROM bsu WHERE number IN ('$rt'))"; $rs = mysql_query($sql,$dbc) or trigger_error(mysql_error(), E_USER_ERROR); if (mysql_num_rows($rs) > 0) { while ($row = mysql_fetch_assoc($rs)) { echo "Left Tackle<br />$row[lt_number]<br />$row[lt_name]<br />$row[lt_pos]<br />$row[lt_height]<br />$row[lt_weight]<br />$row[lt_year]<br />$row[lt_city]<br />$row[lt_state]<br /><br />"; echo "Right Tackle<br />$row[rt_number]<br />$row[rt_name]<br />$row[rt_pos]<br />$row[rt_height]<br />$row[rt_weight]<br />$row[rt_year]<br />$row[rt_city]<br />$row[rt_state]<br /><br />"; } } else { echo ("no matches"); } ?> Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 21, 2009 Share Posted June 21, 2009 Hi Derived tables need an alias:- <?php $lt = array("68","77"); $rt = array("65","67"); $dbc = mysql_pconnect('xxx','xxx','xxx'); mysql_select_db('fb',$dbc); $sql = " SELECT * FROM (select number as lt_number, name as lt_name, pos as lt_pos, height as lt_height, weight as lt_weight, year as lt_year, city as lt_city, state as lt_state FROM bsu WHERE number IN ('$lt')) Deriv1, (select number as rt_number, name as rt_name, pos as rt_pos, height as rt_height, weight as rt_weight, year as rt_year, city as rt_city, state as rt_state FROM bsu WHERE number IN ('$rt')) Deriv2"; $rs = mysql_query($sql,$dbc) or trigger_error(mysql_error(), E_USER_ERROR); if (mysql_num_rows($rs) > 0) { while ($row = mysql_fetch_assoc($rs)) { echo "Left Tackle<br />$row[lt_number]<br />$row[lt_name]<br />$row[lt_pos]<br />$row[lt_height]<br />$row[lt_weight]<br />$row[lt_year]<br />$row[lt_city]<br />$row[lt_state]<br /><br />"; echo "Right Tackle<br />$row[rt_number]<br />$row[rt_name]<br />$row[rt_pos]<br />$row[rt_height]<br />$row[rt_weight]<br />$row[rt_year]<br />$row[rt_city]<br />$row[rt_state]<br /><br />"; } } else { echo ("no matches"); } ?> All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 21, 2009 Author Share Posted June 21, 2009 No errors...but I get NO MATCHES printed to the screen. This page gets accessed once a week...so server load isn't an issue. Should I just quit trying to do this the "right way", and go back to the way I threw together last year and have this: <?php $dbc = mysql_pconnect('xxx','xxx','xxx'); mysql_select_db('fb',$dbc); $sql = "SELECT * FROM bsu WHERE number='$lt1'"; $rs = mysql_query($sql,$dbc); $matches = 0; while ($row = mysql_fetch_assoc($rs)) { $matches++; echo "<table><tr><td width='80' class='number'>$row[number]</td>"; echo "<td width='200' ><div class='name'>$row[name] </div> <div class='height'> $row[height] - $row[weight] - $row[year] <br /> $row[city], $row[state] </div>"; echo "</td></tr>"; } if (! $matches) { echo ("<table><tr><td width='80'></td><td width='200' >"); } echo "</table>"; ?> in the script 33 times? Quote Link to comment Share on other sites More sharing options...
fenway Posted June 22, 2009 Share Posted June 22, 2009 What was the problem with the solution proposed earlier? Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 22, 2009 Author Share Posted June 22, 2009 I need a second alias for another position on the team. That way, I can echo the player out in an html table where they will be playing (might be a starting player today...and a third stringer next week...at a different position). Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 22, 2009 Author Share Posted June 22, 2009 This is working for multiple players...back to my original question...can this be done easier? <?php $sql = " SELECT * FROM (select number as lt1_number FROM bsu WHERE number = $lt1) as lt1_1, (select name as lt1_name FROM bsu WHERE number = $lt1) as lt1_2, (select pos as lt1_pos FROM bsu WHERE number = $lt1) as lt1_3, (select height as lt1_height FROM bsu WHERE number = $lt1) as lt1_4, (select weight as lt1_weight FROM bsu WHERE number = $lt1) as lt1_5, (select year as lt1_year FROM bsu WHERE number = $lt1) as lt1_6, (select city as lt1_city FROM bsu WHERE number = $lt1) as lt1_7, (select state as lt1_state FROM bsu WHERE number = $lt1) as lt1_8, (select number as lt2_number FROM bsu WHERE number = $lt2) as lt2_1, (select name as lt2_name FROM bsu WHERE number = $lt2) as lt2_2, (select pos as lt2_pos FROM bsu WHERE number = $lt2) as lt2_3, (select height as lt2_height FROM bsu WHERE number = $lt2) as lt2_4, (select weight as lt2_weight FROM bsu WHERE number = $lt2) as lt2_5, (select year as lt2_year FROM bsu WHERE number = $lt2) as lt2_6, (select city as lt2_city FROM bsu WHERE number = $lt2) as lt2_7, (select state as lt2_state FROM bsu WHERE number = $lt2) as lt2_8 "; ?> Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 22, 2009 Share Posted June 22, 2009 I need a second alias for another position on the team. That way, I can echo the player out in an html table where they will be playing (might be a starting player today...and a third stringer next week...at a different position). Hi You method while it works is likely to be hideously inefficient, doing 8 selects for one row. With the attempt to cover a player moving positions, with keeping it how it is you appear to only cover one change of position (one reason for my early suggestion of multiple tables). Suspect the issue with no matches might be due to having one half of the select not being found. All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted June 22, 2009 Author Share Posted June 22, 2009 Guys...thanks for the the input and help. This is working... <?php $sql = " SELECT * FROM (select number as lt1_number, name as lt1_name, pos as lt1_pos, height as lt1_height, weight as lt1_weight, year as lt1_year, city as lt1_city, state as lt1_state FROM bsu WHERE number = $lt1) as lt1, (select number as lt2_number, name as lt2_name, pos as lt2_pos, height as lt2_height, weight as lt2_weight, year as lt2_year, city as lt2_city, state as lt2_state FROM bsu WHERE number = $lt2) as lt2 "; ?> As soon as I put the players in an array, and add a second position, it fails...so I'm just going to stick with my original method for this. Thanks again, though! 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.