Jump to content

Is their a faster way

cmb

By cmb
in PHP Coding Help

 Share

Recommended Posts

cmb Member

cmb

Posted
Posted

this is my script it gets the values form the database and adds them and saves it to a variable which is then echoed to the user before i typed this code 10 more times changing every variable name i was wondering if their was a more efficient way of doing this

<?php
            $adam_view_query = "SELECT * FROM pinkpanther_stats WHERE Player = 'Adam' ORDER BY Day_Played DESC";	
		$adam_view_results = mysql_query($adam_view_query)  or die("Query failed ($adam_view_query) - " . mysql_error());
		$adam_view_numrows = mysql_num_rows($adam_view_results);
		$adam_gli_query = "SELECT SUM(GLI) AS 'adam_gli' FROM pinkpanther_stats WHERE Player = 'Adam'";
		$adam_gli_results = mysql_query($adam_gli_query) or die ("Query failed ($adam_gli_query) - " . mysql_error());
		$adam_gli_row = mysql_fetch_array($adam_gli_results);
		$adam_gli = $adam_gli_row['adam_gli'];				
		$adam_goals_query = "SELECT SUM(Goals) AS 'adam_goals' FROM pinkpanther_stats WHERE Player = 'Adam'";
		$adam_goals_results = mysql_query($adam_goals_query) or die ("Query failed ($adam_goals_query) - " . mysql_error());
		$adam_goals_row = mysql_fetch_array($adam_goals_results);
		$adam_goals = $adam_goals_row['adam_goals']; 
        	$adam_saves_query = "SELECT SUM(Saves) AS 'adam_saves' FROM pinkpanther_stats WHERE Player = 'Adam'";
		$adam_saves_results = mysql_query($adam_saves_query) or die ("Query failed ($adam_saves_query) - " . mysql_error());
		$adam_saves_row = mysql_fetch_array($adam_saves_results);
		$adam_saves = $adam_saves_row['adam_saves'];
		$adam_sog_query = "SELECT SUM(SOG) AS 'adam_sog' FROM pinkpanther_stats WHERE Player = 'Adam'";
		$adam_sog_results = mysql_query($adam_sog_query) or die ("Query failed ($adam_sog_query) - " . mysql_error());
		$adam_sog_row = mysql_fetch_array($adam_sog_results);
		$adam_sog = $adam_sog_row['adam_sog'];
		$adam_assists_query = "SELECT SUM(Assists) AS 'adam_assists' FROM pinkpanther_stats WHERE Player = 'Adam'";
		$adam_assists_results = mysql_query($adam_assists_query) or die ("Query failed ($adam_assists_query) - " . mysql_error());
		$adam_assists_row = mysql_fetch_array($adam_assists_results);
		$adam_assists = $adam_assists_row['adam_assists'];
		$adam_ck_query = "SELECT SUM(CK) AS 'adam_ck' FROM pinkpanther_stats WHERE Player = 'Adam'";
		$adam_ck_results = mysql_query($adam_ck_query) or die ("Query failed ($adam_ck_query) - " . mysql_error());
		$adam_ck_row = mysql_fetch_array($adam_ck_results);
		$adam_ck = $adam_ck_row['adam_ck'];
		$adam_yc_query = "SELECT SUM(YC) AS 'adam_yc' FROM pinkpanther_stats WHERE Player = 'Adam'";
		$adam_yc_results = mysql_query($adam_yc_query) or die ("Query failed ($adam_yc_query) - " . mysql_error());
		$adam_yc_row = mysql_fetch_array($adam_yc_results);
		$adam_yc = $adam_yc_row['adam_yc'];
		$adam_rc_query = "SELECT SUM(RC) AS 'adam_rc' FROM pinkpanther_stats WHERE Player = 'Adam'";
		$adam_rc_results = mysql_query($adam_rc_query) or die ("Query failed ($adam_rc_query) - " . mysql_error());
		$adam_rc_row = mysql_fetch_array($adam_rc_results);
		$adam_rc = $adam_rc_row['adam_rc'];
		$adam_goalie = "";
		if (isset($_POST['adam_go'])) { 
			$adam_view = mysql_real_escape_string($_POST['adam_statview']);
			if ($adam_view == "Total"){
				$adam_gli_query = "SELECT SUM(GLI) AS 'adam_gli' FROM pinkpanther_stats WHERE Player = 'Adam'";
				$adam_gli_results = mysql_query($adam_gli_query) or die ("Query failed ($adam_gli_query) - " . mysql_error());
				$adam_gli_row = mysql_fetch_array($adam_gli_results);
				$adam_gli = $adam_gli_row['adam_gli'];					
				$adam_goals_query = "SELECT SUM(Goals) AS 'adam_goals' FROM pinkpanther_stats WHERE Player = 'Adam'";
				$adam_goals_results = mysql_query($adam_goals_query) or die ("Query failed ($adam_goals_query) - " . mysql_error());
				$adam_goals_row = mysql_fetch_array($adam_goals_results);
				$adam_goals = $adam_goals_row['adam_goals']; 					
	        	$adam_saves_query = "SELECT SUM(Saves) AS 'adam_saves' FROM pinkpanther_stats WHERE Player = 'Adam'";
				$adam_saves_results = mysql_query($adam_saves_query) or die ("Query failed ($adam_saves_query) - " . mysql_error());
				$adam_saves_row = mysql_fetch_array($adam_saves_results);
				$adam_saves = $adam_saves_row['adam_saves'];					
				$adam_sog_query = "SELECT SUM(SOG) AS 'adam_sog' FROM pinkpanther_stats WHERE Player = 'Adam'";
				$adam_sog_results = mysql_query($adam_sog_query) or die ("Query failed ($adam_sog_query) - " . mysql_error());
				$adam_sog_row = mysql_fetch_array($adam_sog_results);
				$adam_sog = $adam_sog_row['adam_sog'];					
				$adam_assists_query = "SELECT SUM(Assists) AS 'adam_assists' FROM pinkpanther_stats WHERE Player = 'Adam'";
				$adam_assists_results = mysql_query($adam_assists_query) or die ("Query failed ($adam_assists_query) - " . mysql_error());
				$adam_assists_row = mysql_fetch_array($adam_assists_results);
				$adam_assists = $adam_assists_row['adam_assists'];					
				$adam_ck_query = "SELECT SUM(CK) AS 'adam_ck' FROM pinkpanther_stats WHERE Player = 'Adam'";
				$adam_ck_results = mysql_query($adam_ck_query) or die ("Query failed ($adam_ck_query) - " . mysql_error());
				$adam_ck_row = mysql_fetch_array($adam_ck_results);
				$adam_ck = $adam_ck_row['adam_ck'];					
				$adam_yc_query = "SELECT SUM(YC) AS 'adam_yc' FROM pinkpanther_stats WHERE Player = 'Adam'";
				$adam_yc_results = mysql_query($adam_yc_query) or die ("Query failed ($adam_yc_query) - " . mysql_error());
				$adam_yc_row = mysql_fetch_array($adam_yc_results);
				$adam_yc = $adam_yc_row['adam_yc'];					
				$adam_rc_query = "SELECT SUM(RC) AS 'adam_rc' FROM pinkpanther_stats WHERE Player = 'Adam'";
				$adam_rc_results = mysql_query($adam_rc_query) or die ("Query failed ($adam_rc_query) - " . mysql_error());
				$adam_rc_row = mysql_fetch_array($adam_rc_results);
				$adam_rc = $adam_rc_row['adam_rc'];					
				$adam_goalie = "";						
			}else{					
				$adam_stats_query = "SELECT * FROM pinkpanther_stats WHERE Player = 'Adam' AND Day_Played = '$adam_view'";
				$adam_stats_results = mysql_query($adam_stats_query) or die ("Query failed ($adam_stats_query) - " . mysql_error());
				$adam_stats_row = mysql_fetch_array($adam_stats_results);
				$adam_goalie_test = $adam_stats_row['Goalie'];
				$adam_gli = $adam_stats_row['GLI'];
				$adam_goals = $adam_stats_row['Goals'];
				$adam_saves = $adam_stats_row['Saves'];
				$adam_sog = $adam_stats_row['SOG'];
				$adam_assists = $adam_stats_row['Assists'];
				$adam_ck = $adam_stats_row['CK'];
				$adam_yc = $adam_stats_row['YC'];
				$adam_rc = $adam_stats_row['RC'];
				if ($adam_goalie_test == 1){
					$adam_goalie = "Yes";
				}else{
					$adam_goalie = "No";
				}						
			}
		}
           ?>

Link to comment
Share on other sites
PFMaBiSmAd Advanced Member

PFMaBiSmAd

Posted
Posted

If you are doing this for ALL the players in the table, you would simply use GROUP BY player in the query to consolidate all the rows for each player together (you will get a resultant row in the result set for each player - you would need to add the player column to the select list so that you can identify each row.) You would also sum(...) as '...' each column/field in ONE query, not separate queries for each column/field.

 

You also would not use a players name in any of the variable names or alias names in the query. You would use generic variable names like $query, $result,... and alias names like 'gli', 'goals', ...

 

Edit: If you are doing this for just one, two, or a few players, you would still use GROUP BY player and in the WHERE clause use WHERE player IN('adam','bill','bob')

 

The point of all of this is to get all the data you want, in the order that you want it, using one query (or as few queries as possible.)

Link to comment
Share on other sites
cmb Member

cmb

Posted
  • Author
Posted

So when i use this code for the 10 other players having the generic names like $query or $results wont affect the results for the other players because they are all being viewed on the same page

<?php
$query = "SELECT Player, SUM(GLI) AS 'gli', SUM(Goals) AS 'goals', SUM(Saves) AS 'saves', SUM(SOG) AS 'sog', SUM(Assists) AS'assists', SUM(CK) AS 'ck', SUM(YC) AS 'yc', SUM(RC) AS 'rc' FROM pinkpanther_stats WHERE Player IN('Adam') GROUP BY Player; ";
				$results = mysql_query($query);
				$row = mysql_fetch_array($results);
				$adam_gli = $row[1];
				$adam_goals = $row[2];
				$adam_saves = $row[3];
				$adam_sog = $row[4];
				$adam_assists = $row[5];
				$adam_ck = $row[6];
				$adam_yc = $row[7];
				$adam_rc = $row[8];
				$adam_goalie = "";	
?>

Link to comment
Share on other sites
PFMaBiSmAd Advanced Member

PFMaBiSmAd

Posted
Posted

The indirect answer to that is they won't interfere with each other because there will only be one $query and one $results variable. You won't have 10 sets of code to view all the players on one page. You will have one set of code that loops over the players.

 

Use one query (without a where clause) to get the results for all the players at once, then loop over the result set and process or output the information for each player inside the loop.

 

<?php
	$query = "SELECT Player,SUM(GLI) AS 'gli',
			SUM(Goals) AS 'goals',
			SUM(Saves) AS 'saves',
			SUM(SOG) AS 'sog',
			SUM(Assists) AS 'assists',
			SUM(CK) AS 'ck',
			SUM(YC) AS 'yc',
			SUM(RC) AS 'rc'
			FROM pinkpanther_stats GROUP BY Player";
	$results = mysql_query($query) or die ("Query failed ($query) - " . mysql_error());
	while($row = mysql_fetch_array($results)){
		// use the data or display the result for each player here... $row['Player'] will contain the player's name.

	}

Link to comment
Share on other sites
cmb Member

cmb

Posted
  • Author
Posted

Ok so this is the table that i want to be populated whit the data retrieved from the database


<table width="483" height="320" border="0">
	   <tr>
           	<td colspan="4">View Stats For:<?php 
		if ($adam_view_numrows > 0){
			echo "<form id='adam_go' method='post' action=''><select name='adam_statview' id='adam_statview' size='1' class='txtbox'>";
			while ($adam_view_row = mysql_fetch_assoc($adam_view_results)){
				$adam_games = $adam_view_row['Day_Played'];
				echo "<option value='" . $adam_games . "'>" . $adam_games ."</option>";
			}
			echo "</select><input type='submit' value='Go' name='adam_go' id='adam_go' /></form>";		
		}else{
			echo "<select name='adam_statview' id='adam_statview' size='1' class='txtbox'><option value='#'>No Stats Avalable </option></select>";
		}
	  ?></td>
           </tr>
           <tr>
             <td width="182">Goalie:</td>
             <td width="62"><input type="text" id="adam_goalie" name="adam_goalie" size="1" readonly class="txtbox" value="<?php echo $adam_goalie ?>" /></td>
             <td width="170">GLI:</td>
             <td width="51"><input type="text" id="adam_gli" name="adam_gli" size="1" readonly class="txtbox" value="<?php echo $adam_gli?>" /></td>
           </tr>
           <tr>
             <td>Goals:</td>
             <td><input type="text" id="adam_goals" name="adam_goals" size="1" readonly class="txtbox" value="<?php echo $adam_goals?>" /></td>
             <td>Saves:</td>
             <td><input type="text" id="adam_saves" name="adam_saves" size="1" readonly class="txtbox" value="<?php echo $adam_saves?>" /></td>
           </tr>
           <tr>
             <td>SOG:</td>
             <td><input type="text" id="adam_sog" name="adam_sog" size="1" readonly class="txtbox" value="<?php echo $adam_sog?>" /></td>
             <td>Assists:</td>
             <td><input type="text" id="adam_assists" name="adam_assists" size="1" readonly class="txtbox" value="<?php echo $adam_assists?>" /></td>
           </tr>
           <tr>
             <td>CK:</td>
             <td><input type="text" id="adam_fouls" name="adam_fouls" size="1" readonly class="txtbox" value="<?php echo $adam_ck?>" /></td>
             <td>YC:</td>
             <td><input type="text" id="adam_yc" name="adam_yc" size="1" readonly class="txtbox" value="<?php echo $adam_yc?>" /></td>
           </tr>
           <tr>
             <td>RC:</td>
             <td><input type="text" id="adam_rc" name="adam_rc" size="1" readonly class="txtbox" value="<?php echo $adam_rc?>" /></td>
             <td></td>
             <td></td>
           </tr>
         </table>

so i guess im not understanding i should just change all this to genaric names and put it in a while loop would that do what im trying to acomplish and will the selection of which stats to view still work that way

and im only 16 im still in high school

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.