Jump to content

Page containing PHP and MySQL loading slowly - need advice

NiallAA

By NiallAA
in PHP Coding Help

 Share

Recommended Posts

NiallAA Newbie

NiallAA

Posted
Posted

Apologies if the title is quite vague, I suppose I am looking for some general advice on why some pages I write in PHP, which contain MySQL queries, might be running a bit slow.  The following page takes up to 3 seconds to display:


<?php

session_start();

include('admin/user.php');
$connection = mysql_connect("$host","$user","$password")
or die(mysql_error());
mysql_select_db("$txt_db_name",$connection)
or die(mysql_error());
	
$id = $_REQUEST['id'];

// MATCH INFO

$get_details = mysql_query("
SELECT
	MatchDateTime AS date,
	DATE_FORMAT(MatchDateTime, '%Y-%m-%d') AS formatdate
FROM 
	tplss_matches
WHERE 
	MatchID = '$id'
LIMIT 1",$connection) or die(mysql_error());

$factsdata = mysql_fetch_array($get_details);
mysql_free_result($get_details);
$matchdate = $factsdata['date'];
$matchdate2 = $factsdata['formatdate'];

// -----------SHOW FACTS ABOUT THE STARTING LINEUP---------

echo"
<h5>Appearances & Goals To Date</h5>
<table width=100%>";

$get_starters = mysql_query("
SELECT P.PlayerID AS playerid, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerNationID AS nation, P.PlayerPositionID as pos
FROM tplss_players P, tplss_appearances A
WHERE A.AppearancePlayerID = P.PlayerID AND A.AppearanceMatchID = '$id'
ORDER BY P.PlayerPositionID ASC
",$connection) or die(mysql_error());
	
$get_subbies = mysql_query("
SELECT P.PlayerID AS playerid, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerNationID AS nation, P.PlayerPositionID as pos
FROM tplss_players P, tplss_substitutions S
WHERE S.SubstitutionPlayerIDIn = P.PlayerID AND S.SubstitutionMatchID = '$id'
ORDER BY P.PlayerPositionID ASC
",$connection) or die(mysql_error());
			
while($combstarters = mysql_fetch_array($get_starters))		
	{

	echo"<tr>";
	
	echo"<td><a href=\"player.php?id=$combstarters[playerid]\">$combstarters[name]</a>";
	
	if($combstarters['pos'] == 1)
		{ echo" (GK)"; }
	
	echo"</td>";
	
	$combpid = $combstarters['playerid'];
	
	echo"
	<td align=\"left\" style=\"vertical-align: middle;\">
	<img src=\"images/flag_$combstarters[nation].jpg\" border=1>
	</td>
	";
	
	$get_comb_apps = mysql_query("SELECT 
	COUNT(A.AppearancePlayerID) AS apps
	FROM tplss_appearances A, tplss_matches M
	WHERE A.AppearancePlayerID = '$combpid' AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	",$connection) or die(mysql_error());

	$get_comb_ins = mysql_query("SELECT 
	COUNT(S.SubstitutionPlayerIDIn) AS ins
	FROM tplss_substitutions S, tplss_matches M
	WHERE S.SubstitutionPlayerIDIn = '$combpid' AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	",$connection) or die(mysql_error());
	
	while($combdata = mysql_fetch_array($get_comb_apps)) 
		{
		while($idata = mysql_fetch_array($get_comb_ins)) 
			{	
			$totalapps = $combdata['apps'] + $idata['ins'];
			if($totalapps == 1)
				{ echo"<td>$totalapps app (debut)</td>"; }
			else
				{ echo"<td>$totalapps apps</td>"; }
			}
		
		mysql_free_result($get_comb_ins);
		
		}
	
	mysql_free_result($get_comb_apps);
		
	$get_goals_all = mysql_query("
	SELECT COUNT(G.GoalPlayerID) AS total_goals
	FROM tplss_goals G, tplss_matches M WHERE G.GoalPlayerID = '$combpid' AND G.GoalMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' AND G.GoalOwn != 1
	GROUP BY G.GoalPlayerID
	",$connection) or die(mysql_error());
	
	if(mysql_num_rows($get_goals_all) == 0)
		{ echo"<td> - </td>"; }
	
	while($combgoals = mysql_fetch_array($get_goals_all))
		{
		
		if($combgoals['total_goals'] == 1)
			{ echo"<td>$combgoals[total_goals] goal</td>"; }
		else
			{ echo"<td>$combgoals[total_goals] goals</td>"; }

		}
	
	mysql_free_result($get_goals_all);
		
	echo"</tr>";

	}
	
while($combsubbies = mysql_fetch_array($get_subbies))		
	{

	echo"<tr>";
	
	echo"<td><a href=\"player.php?id=$combsubbies[playerid]\">$combsubbies[name]</a> (sub)";
	
	if($combsubbies['pos'] == 1)
		{ echo" (GK)"; }
	
	echo"</td>";
	
	$combpid = $combsubbies['playerid'];

	echo"
	<td align=\"left\" style=\"vertical-align: middle;\">
	<img src=\"images/flag_$combsubbies[nation].jpg\" border=1>
	</td>
	";
	
	$get_comb_apps = mysql_query("SELECT 
	COUNT(A.AppearancePlayerID) AS apps
	FROM tplss_appearances A, tplss_matches M
	WHERE A.AppearancePlayerID = '$combpid' AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	",$connection) or die(mysql_error());

	$get_comb_ins = mysql_query("SELECT 
	COUNT(S.SubstitutionPlayerIDIn) AS ins
	FROM tplss_substitutions S, tplss_matches M
	WHERE S.SubstitutionPlayerIDIn = '$combpid' AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	",$connection) or die(mysql_error());
	
	while($combdata = mysql_fetch_array($get_comb_apps)) 
		{
		while($idata = mysql_fetch_array($get_comb_ins)) 
			{
			$totalapps = $combdata['apps'] + $idata['ins'];
			if($totalapps == 1)
				{ echo"<td>$totalapps app (debut)</td>"; }
			else
				{ echo"<td>$totalapps apps</td>"; }
			}
		mysql_free_result($get_comb_ins);
		
		}
	
	mysql_free_result($get_comb_apps);
		
	$get_goals_all = mysql_query("
	SELECT COUNT(G.GoalPlayerID) AS total_goals
	FROM tplss_goals G, tplss_matches M WHERE G.GoalPlayerID = '$combpid' AND G.GoalMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' AND G.GoalOwn != 1
	GROUP BY G.GoalPlayerID
	",$connection) or die(mysql_error());
	
	if(mysql_num_rows($get_goals_all) == 0)
		{ echo"<td> - </td>"; }
	
	while($combgoals = mysql_fetch_array($get_goals_all))
		{
		
		if($combgoals['total_goals'] == 1)
			{ echo"<td>$combgoals[total_goals] goal</td>"; }
		else
			{ echo"<td>$combgoals[total_goals] goals</td>"; }

		}
	
	mysql_free_result($get_goals_all);
		
	echo"</tr>";

	}
	
echo"</table>";	




// -----------SHOW FACTS ABOUT THE STARTING LINEUP---------

echo"<br>
<h5>Starting Lineup</h5>
<table width=100%>";


// GET YOUNGEST PLAYER IN STARTING LINEUP

$get_youngest_player = mysql_query("
SELECT 
	P.PlayerDOB AS dob,
	DATE_FORMAT(P.PlayerDOB, '%d/%m/%Y') AS birth,
	CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, 
	P.PlayerID AS id
FROM 
	tplss_players P, tplss_appearances A, tplss_matches M
WHERE 
	P.PlayerID = A.AppearancePlayerID AND
	A.AppearanceMatchID = M.MatchID AND
	M.MatchDateTime = '$matchdate'
ORDER BY
	dob DESC
LIMIT 0,1
",$connection) or die(mysql_error());

while($youngest = mysql_fetch_array($get_youngest_player))

	{
	
	echo"<tr>";
	
	$dob = $youngest['dob'];
	
	echo"<td width=30%>Youngest Player:</td><td width=70%><a href=\"player.php?id=$youngest[id]\">$youngest[name]</a> (";
	
	$now = strtotime("$matchdate");
	$your_date = strtotime("$dob");
	$datediff_days = $now - $your_date;
	$datediff_years = ($now - $your_date) / 365;
	$days = floor($datediff_days/(60*60*24));
	$years = floor($datediff_years/(60*60*24));
	$remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365);

	echo"$years years $remainder days)</td>
	</tr>
	";
	
	}

mysql_free_result($get_youngest_player);
	
// GET OLDEST PLAYER IN STARTING LINEUP

$get_oldest_player = mysql_query("
SELECT 
	P.PlayerDOB AS dob,
	DATE_FORMAT(P.PlayerDOB, '%d/%m/%Y') AS birth,
	CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, 
	P.PlayerID AS id
FROM 
	tplss_players P, tplss_appearances A, tplss_matches M
WHERE 
	P.PlayerID = A.AppearancePlayerID AND
	A.AppearanceMatchID = M.MatchID AND
	M.MatchDateTime = '$matchdate'
ORDER BY
	dob ASC
LIMIT 0,1
",$connection) or die(mysql_error());
	
while($oldest = mysql_fetch_array($get_oldest_player))

	{
	
	echo"<tr>";
	
	$dob = $oldest['dob'];
	
	echo"<td width=30%>Oldest Player:</td><td width=70%><a href=\"player.php?id=$oldest[id]\">$oldest[name]</a> (";
	
	 $now = strtotime("$matchdate");
     $your_date = strtotime("$dob");

	 $datediff_days = $now - $your_date;
     $datediff_years = ($now - $your_date) / 365;
     $days = floor($datediff_days/(60*60*24));
	 $years = floor($datediff_years/(60*60*24));
	 $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365);

	echo"$years years $remainder days)</td>";
	
	echo"</tr>";
	
	}
	
mysql_free_result($get_oldest_player);
	
// GET AVERAGE DOB OF STARTING XI

$get_average_dob = mysql_query("
SELECT 
	FROM_DAYS(AVG(TO_DAYS(P.PlayerDOB))) AS dob
FROM 
	tplss_players P, tplss_appearances A, tplss_matches M
WHERE 
	P.PlayerID = A.AppearancePlayerID AND
	A.AppearanceMatchID = M.MatchID AND
	M.MatchDateTime = '$matchdate'
",$connection) or die(mysql_error());

while($average = mysql_fetch_array($get_average_dob))

	{
	
	echo"<tr>";
	
	$dob = $average['dob'];		
	$now = strtotime("$matchdate");
     $your_date = strtotime("$dob");

	 $datediff_days = $now - $your_date;
     $datediff_years = $datediff_days / 365;
     $days = floor($datediff_days/(60*60*24));
	 $years = floor($datediff_years/(60*60*24));
	 $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365);

	echo"<td width=30%>Average Player Age:</td><td width=70%>$years years $remainder days</td>";
	
	echo"</tr>";
	
	}
	
mysql_free_result($get_average_dob);
	
$get_players = mysql_query("
SELECT 
    COUNT(P.PlayerID) AS players
FROM 
	tplss_players P, tplss_appearances A, tplss_matches M
WHERE 
	P.PlayerID = A.AppearancePlayerID AND
	A.AppearanceMatchID = M.MatchID AND
	M.MatchDateTime = '$matchdate'
GROUP BY M.MatchID
",$connection) or die(mysql_error());

$get_scots = mysql_query("
SELECT 
    COUNT(P.PlayerID) AS scots
FROM 
	tplss_players P, tplss_appearances A, tplss_matches M
WHERE 
	P.PlayerID = A.AppearancePlayerID AND
	A.AppearanceMatchID = M.MatchID AND
	M.MatchDateTime = '$matchdate' AND
	P.PlayerNationID = 1
GROUP BY M.MatchID
",$connection) or die(mysql_error());	
	
while($players = mysql_fetch_array($get_players))

	{
	
	echo"<tr>";
	
	while($scots = mysql_fetch_array($get_scots))
		{ 
		$average = ($scots['scots'] / $players['players']) * 100;
		$average = number_format((float)$average, 2, '.', '');
		echo"<td width=30%>Domestic Players:</td><td width=70%>$scots[scots] ($average % of starting eleven)</td>"; 
		}
		
	echo"</tr>";
		
	}
	
mysql_free_result($get_players);



echo"</table>";

?>





<?
// -----------SHOW FACTS ABOUT THE MATCHDAY SQUAD--------------

echo"<br>
<h5>Matchday Squad</h5>
<table width=100%>
";

// GET YOUNGEST PLAYER IN SQUAD

$get_youngest_player_all = mysql_query("
SELECT 
    P.PlayerDOB AS dob,
    CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, 
    P.PlayerID AS id
FROM 
    tplss_players P
    INNER JOIN
        (
            SELECT AppearancePlayerID as PlayerID
            , AppearanceMatchID as MatchID
            FROM tplss_appearances
            UNION
            SELECT SubstitutePlayerID as PlayerID
            , SubstituteMatchID as MatchID
            FROM tplss_substitutes 
        ) 
        as total USING (PlayerID)
    INNER JOIN tplss_matches M USING (MatchID)
WHERE 
    M.MatchDateTime = '$matchdate'
ORDER BY
    dob DESC
LIMIT 0,1
",$connection) or die(mysql_error());

while($youngest_all = mysql_fetch_array($get_youngest_player_all))

	{
	
	echo"<tr>";
	
	$dob = $youngest_all['dob'];
	
	echo"<td width=30%>Youngest Player:</td><td width=70%><a href=\"player.php?id=$youngest_all[id]\">$youngest_all[name]</a> (";
	
	$now = strtotime("$matchdate");
	$your_date = strtotime("$dob");
	 $datediff_days = $now - $your_date;
     $datediff_years = ($now - $your_date) / 365;
     $days = floor($datediff_days/(60*60*24));
	 $years = floor($datediff_years/(60*60*24));
	 $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365);

	echo"$years years $remainder days)</td>";
	
	echo"</tr>";
	
	}

// GET OLDEST PLAYER IN SQUAD

$get_oldest_player_all = mysql_query("
SELECT 
    P.PlayerDOB AS dob,
    CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, 
    P.PlayerID AS id
FROM 
    tplss_players P
    INNER JOIN
        (
            SELECT AppearancePlayerID as PlayerID
            , AppearanceMatchID as MatchID
            FROM tplss_appearances
            UNION
            SELECT SubstitutePlayerID as PlayerID
            , SubstituteMatchID as MatchID
            FROM tplss_substitutes 
        ) 
        as total USING (PlayerID)
    INNER JOIN tplss_matches M USING (MatchID)
WHERE 
    M.MatchDateTime = '$matchdate'
ORDER BY
    dob ASC
LIMIT 0,1
",$connection) or die(mysql_error());

while($oldest_all = mysql_fetch_array($get_oldest_player_all))

	{
	
	echo"<tr>";
	
	$dob = $oldest_all['dob'];
	
	echo"<td width=30%>Oldest Player:</td><td width=70%><a href=\"player.php?id=$oldest_all[id]\">$oldest_all[name]</a> (";
	
	 $now = strtotime("$matchdate");
     $your_date = strtotime("$dob");

	 $datediff_days = $now - $your_date;
     $datediff_years = ($now - $your_date) / 365;
     $days = floor($datediff_days/(60*60*24));
	 $years = floor($datediff_years/(60*60*24));
	 $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365);

	echo"$years years $remainder days)</td>";
		
	echo"</tr>";
	
	}
	
// GET AVERAGE DOB OF WHOLE SQUAD

$get_average_dob_all = mysql_query("
SELECT 
    FROM_DAYS(AVG(TO_DAYS(P.PlayerDOB))) AS dob
FROM 
    tplss_players P
    INNER JOIN
        (
            SELECT AppearancePlayerID as PlayerID
            , AppearanceMatchID as MatchID
            FROM tplss_appearances
            UNION
            SELECT SubstitutePlayerID as PlayerID
            , SubstituteMatchID as MatchID
            FROM tplss_substitutes 
        ) 
        as total USING (PlayerID)
    INNER JOIN tplss_matches M USING (MatchID)
WHERE 
    M.MatchDateTime = '$matchdate'
",$connection) or die(mysql_error());

while($average_all = mysql_fetch_array($get_average_dob_all))

	{
	
	echo"<tr>";
	
	$dob = $average_all['dob'];	
	
	 $now = strtotime("$matchdate");
     $your_date = strtotime("$dob");

	 $datediff_days = $now - $your_date;
     $datediff_years = ($now - $your_date) / 365;
     $days = floor($datediff_days/(60*60*24));
	 $years = floor($datediff_years/(60*60*24));
	 $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365);

	echo"<td width=30%>Average Player Age:</td><td width=70%>$years years $remainder days</td>";
		
	echo"</tr>";	
	
	}
	
$get_players_all = mysql_query("
SELECT 
    COUNT(P.PlayerID) AS allplayers
FROM 
    tplss_players P
    INNER JOIN
        (
            SELECT AppearancePlayerID as PlayerID
            , AppearanceMatchID as MatchID
            FROM tplss_appearances
            UNION
            SELECT SubstitutePlayerID as PlayerID
            , SubstituteMatchID as MatchID
            FROM tplss_substitutes 
        ) 
        as total USING (PlayerID)
    INNER JOIN tplss_matches M USING (MatchID)
WHERE 
    M.MatchDateTime = '$matchdate'
GROUP BY M.MatchID
",$connection) or die(mysql_error());

$get_scots_all = mysql_query("
SELECT 
    COUNT(P.PlayerID) AS scots
FROM 
    tplss_players P
    INNER JOIN
        (
            SELECT AppearancePlayerID as PlayerID
            , AppearanceMatchID as MatchID
            FROM tplss_appearances
            UNION
            SELECT SubstitutePlayerID as PlayerID
            , SubstituteMatchID as MatchID
            FROM tplss_substitutes 
        ) 
        as total USING (PlayerID)
    INNER JOIN tplss_matches M USING (MatchID)
WHERE 
    M.MatchDateTime = '$matchdate' AND
	P.PlayerNationID = 1
GROUP BY M.MatchID
",$connection) or die(mysql_error());

while($players_all = mysql_fetch_array($get_players_all))

	{
	
	while($scots_all = mysql_fetch_array($get_scots_all))
		{ 
		echo"<tr>";
		$average = ($scots_all['scots'] / $players_all['allplayers']) * 100;
		$average = number_format((float)$average, 2, '.', '');
		echo"<td width=30%>Domestic Players:</td><td width=70%>$scots_all[scots] ($average % of matchday squad)</td>"; 
		echo"</tr>";
		}
		
	}
	
echo"</table>";
	
?>








<?
//--------------CHECK FOR ANY DEBUTS----------------

	
// GET STARTING XI FOR DEBUTS

$get_debuts = mysql_query("
SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id, DATE_FORMAT(P.PlayerSigned, '%M %D, %Y') AS signed
FROM tplss_players P, tplss_appearances A
WHERE A.AppearanceMatchID = '$id' AND P.PlayerID = A.AppearancePlayerID
ORDER BY name",$connection);

// GET SUBS FOR DEBUTS

$get_sub_debuts = mysql_query("
SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id, DATE_FORMAT(P.PlayerSigned, '%M %D, %Y') AS signed
FROM tplss_players P, tplss_substitutions S
WHERE S.SubstitutionMatchID = '$id' AND P.PlayerID = S.SubstitutionPlayerIDIn
ORDER BY name",$connection);



echo"<br><h5>First Team Debuts</h5>
<table width=100%>";

// SHOW ANY DEBUTS FOR PLAYERS IN STARTING XI

while($appdata = mysql_fetch_array($get_debuts))

	{
		
	$appplayerid = $appdata['id'];

	$get_starts = mysql_query("
	SELECT COUNT(A.AppearancePlayerID) AS total
	FROM tplss_appearances A, tplss_matches M
	WHERE A.AppearancePlayerID = $appplayerid AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	GROUP BY A.AppearancePlayerID
	",$connection) or die(mysql_error());

	$starts = mysql_fetch_array($get_starts);
	
	$get_subst = mysql_query("
	SELECT COUNT(S.SubstitutionPlayerIDIn) AS total
	FROM tplss_substitutions S, tplss_matches M
	WHERE S.SubstitutionPlayerIDIn = $appplayerid AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	GROUP BY S.SubstitutionPlayerIDIn
	",$connection) or die(mysql_error());
	
	$subst = mysql_fetch_array($get_subst);
	
	$total_apps = $starts['total'] + $subst['total'];
	$head_url = "images/heads/" . $appplayerid . ".jpg";
	
	if($total_apps == 1)
		{ echo"<tr>
		<td width=20%>
		<img src=\"";
		
		if(file_exists($head_url))
			{ echo"images/heads/$appplayerid.jpg"; }
		else
			{ echo"images/heads/none.jpg"; }
		
		echo"\" width=\"50\" style=\"border:0px solid; border-radius:25px;\">		
		</td>
		<td width=40%><a href=\"player.php?id=$appplayerid\">$appdata[name]</a></td><td width=40%>(Signed $appdata[signed])</td>"; }
	else	
		{ echo""; }
		
	}
	
mysql_free_result($get_debuts);

// SHOW ANY DEBUTS FOR PLAYERS COMING OFF BENCH

while($appdatas = mysql_fetch_array($get_sub_debuts))

	{
		
	$appplayerid = $appdatas['id'];

	$get_starts = mysql_query("
	SELECT COUNT(A.AppearancePlayerID) AS total
	FROM tplss_appearances A, tplss_matches M
	WHERE A.AppearancePlayerID = $appplayerid AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	GROUP BY A.AppearancePlayerID
	",$connection) or die(mysql_error());

	$starts = mysql_fetch_array($get_starts);
	
	$get_subst = mysql_query("
	SELECT COUNT(S.SubstitutionPlayerIDIn) AS total
	FROM tplss_substitutions S, tplss_matches M
	WHERE S.SubstitutionPlayerIDIn = $appplayerid AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	GROUP BY S.SubstitutionPlayerIDIn
	",$connection) or die(mysql_error());
	
	$subst = mysql_fetch_array($get_subst);
	
	$total_apps = $starts['total'] + $subst['total'];
	$head_url = "images/heads/" . $appplayerid . ".jpg";
	
	if($total_apps == 1)
		{ echo"<tr>
		<td width=20%>
		<img src=\"";
		
		if(file_exists($head_url))
			{ echo"images/heads/$appplayerid.jpg"; }
		else
			{ echo"images/heads/none.jpg"; }
		
		echo"\" width=\"50\" style=\"border:0px solid; border-radius:25px;\">		
		</td>
		<td width=40%><a href=\"player.php?id=$appplayerid\">$appdatas[name]</a></td><td width=40%>(Signed $appdatas[signed])</td>"; }
	else	
		{ echo""; }
		
	}
	
mysql_free_result($get_sub_debuts);
	
echo"</table>";	
	

?>









<?php
// --------------------CHECK FOR ANY MILESTONES----------------

// GET STARTING XI FOR MILESTONES

$get_milestones = mysql_query("
SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id
FROM tplss_players P, tplss_appearances A
WHERE A.AppearanceMatchID = '$id' AND P.PlayerID = A.AppearancePlayerID
ORDER BY name",$connection);

// GET SUBS FOR MILESTONES

$get_sub_milestones = mysql_query("
SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id
FROM tplss_players P, tplss_substitutions S
WHERE S.SubstitutionMatchID = '$id' AND P.PlayerID = S.SubstitutionPlayerIDIn
ORDER BY name",$connection);

// GET SCORERS FOR GOAL CHECKS

$get_goals = mysql_query("
SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id
FROM tplss_players P, tplss_goals G
WHERE G.GoalMatchID = '$id' AND P.PlayerID = G.GoalPlayerID AND G.GoalOwn != 1
ORDER BY name",$connection);

	
echo"<Br><h5>Milestones</h5>
<table width=100%>";

// SHOW MILESTONES FOR STARTING XI
	
while($appdatam = mysql_fetch_array($get_milestones))

	{
		
	$appplayerid = $appdatam['id'];

	$get_starts = mysql_query("
	SELECT COUNT(A.AppearancePlayerID) AS total
	FROM tplss_appearances A, tplss_matches M
	WHERE A.AppearancePlayerID = $appplayerid AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	GROUP BY A.AppearancePlayerID
	",$connection) or die(mysql_error());

	$starts = mysql_fetch_array($get_starts);
	
	$get_subst = mysql_query("
	SELECT COUNT(S.SubstitutionPlayerIDIn) AS total
	FROM tplss_substitutions S, tplss_matches M
	WHERE S.SubstitutionPlayerIDIn = $appplayerid AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	GROUP BY S.SubstitutionPlayerIDIn
	",$connection) or die(mysql_error());
	
	$subst = mysql_fetch_array($get_subst);
	
	$total_apps = $starts['total'] + $subst['total'];
		
	echo"";	
			
	if($total_apps == '50')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> played his 50th major competitive game for the Club.</td></tr>"; }
	elseif($total_apps == '100')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> played his 100th major competitive game for the Club.</td></tr>"; }
	elseif($total_apps == '200')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> made his 200th competitive appearance for the Club.</td></tr>"; }
	elseif($total_apps == '250')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> played his 250th major competitive game for the Club.</td></tr>"; }
	elseif($total_apps == '300')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> made his 300th competitive appearance for the Club.</td></tr>"; }
	elseif($total_apps == '400')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> played his 400th major competitive game for the Club.</td></tr>"; }
	elseif($total_apps == '500')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> made his 500th competitive appearance for the Club.</td></tr>"; }
	else	
		{ echo""; }	
		
	echo"";
	
	}	
	
mysql_free_result($get_milestones);

// SHOW MILESTONES FOR SUBS

while($appdatams = mysql_fetch_array($get_sub_milestones))

	{
		
	$appplayerid = $appdatams['id'];

	$get_starts = mysql_query("
	SELECT COUNT(A.AppearancePlayerID) AS total
	FROM tplss_appearances A, tplss_matches M
	WHERE A.AppearancePlayerID = $appplayerid AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	GROUP BY A.AppearancePlayerID
	",$connection) or die(mysql_error());

	$starts = mysql_fetch_array($get_starts);
	
	$get_subst = mysql_query("
	SELECT COUNT(S.SubstitutionPlayerIDIn) AS total
	FROM tplss_substitutions S, tplss_matches M
	WHERE S.SubstitutionPlayerIDIn = $appplayerid AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	GROUP BY S.SubstitutionPlayerIDIn
	",$connection) or die(mysql_error());
	
	$subst = mysql_fetch_array($get_subst);
	
	$total_apps = $starts['total'] + $subst['total'];
			
			
	if($total_apps == '50')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> played his 50th major competitive game for the Club.</td></tr>"; }
	elseif($total_apps == '100')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> played his 100th major competitive game for the Club.</td></tr>"; }
	elseif($total_apps == '200')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> made his 200th competitive appearance for the Club.</td></tr>"; }
	elseif($total_apps == '250')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> played his 250th major competitive game for the Club.</td></tr>"; }
	elseif($total_apps == '300')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> made his 300th competitive appearance for the Club.</td></tr>"; }
	elseif($total_apps == '400')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> played his 400th major competitive game for the Club.</td></tr>"; }
	elseif($total_apps == '500')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> made his 500th competitive appearance for the Club.</td></tr>"; }
	else	
		{ echo""; }	
		
	
	}	
	
mysql_free_result($get_sub_milestones);

// SHOW MILESTONES FOR STARTING XI
	
while($goaldata = mysql_fetch_array($get_goals))

	{
		
	$appplayerid = $goaldata['id'];

	$get_goal_totals = mysql_query("
	SELECT COUNT(G.GoalPlayerID) AS total
	FROM tplss_goals G, tplss_matches M
	WHERE G.GoalPlayerID = $appplayerid AND G.GoalMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	GROUP BY G.GoalPlayerID
	",$connection) or die(mysql_error());

	$goals = mysql_fetch_array($get_goal_totals);
	
	$total_goals = $goals['total'];
			
		
	if($total_goals == '1')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> scored his first goal for the Club.</td></tr>"; }
	elseif($total_goals == '10')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> reached 10 goals for the Club.</td></tr>"; }
	elseif($total_goals == '25')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> scored for the 25th time for the Club.</td></tr>"; }
	elseif($total_goals == '30')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> reached 30 goals for the Club.</td></tr>"; }
	elseif($total_goals == '50')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> scored his 50th goal for the Club.</td></tr>"; }
	elseif($total_goals == '75')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> reached 75 goals for the Club.</td></tr>"; }
	elseif($total_goals == '100')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> scored his 100th goal for the Club.</td></tr>"; }
	elseif($total_goals == '200')
		{ echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> scored his 200th goal for the Club.</td></tr>"; }
	else	
		{ echo""; }	
		
	
	}	

echo"</table>";
	
mysql_free_result($get_goals);

?>

<hr>

Any suggestions or general advice would be greatly appreciated.

Link to comment
Share on other sites
NiallAA Newbie

NiallAA

Posted
  • Author
Posted (edited)

This seems to be the bit that is causing the most delay:

$get_starters = mysql_query("
SELECT P.PlayerID AS playerid, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerNationID AS nation, P.PlayerPositionID as pos
FROM tplss_players P, tplss_appearances A
WHERE A.AppearancePlayerID = P.PlayerID AND A.AppearanceMatchID = '$id'
ORDER BY P.PlayerPositionID ASC
",$connection) or die(mysql_error());
			
while($combstarters = mysql_fetch_array($get_starters))		
	{

	echo"<tr>";
	
	echo"<td><a href=\"player.php?id=$combstarters[playerid]\">$combstarters[name]</a>";
	
	if($combstarters['pos'] == 1)
		{ echo" (GK)"; }
	
	echo"</td>";
	
	$combpid = $combstarters['playerid'];
	
	echo"
	<td align=\"left\" style=\"vertical-align: middle;\">
	<img src=\"images/flag_$combstarters[nation].jpg\" border=1>
	</td>
	";
	
	$get_comb_apps = mysql_query("SELECT 
	COUNT(A.AppearancePlayerID) AS apps
	FROM tplss_appearances A, tplss_matches M
	WHERE A.AppearancePlayerID = '$combpid' AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	",$connection) or die(mysql_error());

	$get_comb_ins = mysql_query("SELECT 
	COUNT(S.SubstitutionPlayerIDIn) AS ins
	FROM tplss_substitutions S, tplss_matches M
	WHERE S.SubstitutionPlayerIDIn = '$combpid' AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate'
	",$connection) or die(mysql_error());
	
	while($combdata = mysql_fetch_array($get_comb_apps)) 
		{
		while($idata = mysql_fetch_array($get_comb_ins)) 
			{	
			$totalapps = $combdata['apps'] + $idata['ins'];
			if($totalapps == 1)
				{ echo"<td>$totalapps app (debut)</td>"; }
			else
				{ echo"<td>$totalapps apps</td>"; }
			}
		}
		
	$get_goals_all = mysql_query("
	SELECT COUNT(G.GoalPlayerID) AS total_goals
	FROM tplss_goals G, tplss_matches M WHERE G.GoalPlayerID = '$combpid' AND G.GoalMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' AND G.GoalOwn != 1
	GROUP BY G.GoalPlayerID
	",$connection) or die(mysql_error());
	
	if(mysql_num_rows($get_goals_all) == 0)
		{ echo"<td> - </td>"; }
	
	while($combgoals = mysql_fetch_array($get_goals_all))
		{
		
		if($combgoals['total_goals'] == 1)
			{ echo"<td>$combgoals[total_goals] goal</td>"; }
		else
			{ echo"<td>$combgoals[total_goals] goals</td>"; }

		}
		
	echo"</tr>";

	}

Is there a way I can do this without executing queries within loops? Thank you in advance to anyone who can help.

 

What this page is, is a list of football players selected to play in a match.   Next to them in a column is a list of their combined appearances (full appearances (apps) and partial appearances (ins)) and goals scored to date.

Edited by NiallAA
Link to comment
Share on other sites
cyberRobot Prolific Member

cyberRobot

Posted
Posted

There may be a way to merge some of the queries to avoid executing them within loops as ginerjm suggested. If MySQL isn't your strong suit, you could look into collecting the information into a multidimensional associative array. Here's a quick example showing the code I've used for a similar issue:

http://www.cyberscorpion.com/2013-05/avoid-mysql-queries-within-loops/

Link to comment
Share on other sites
NiallAA Newbie

NiallAA

Posted
  • Author
Posted

You s/b using the JOIN operator in your sql to gather ALL the needed data in just one query.  Also - as mentioned - never run queries inside loops.  Change your thought process to tackle the queries in one pass and not in a loop.

 

Thanks ginerjm. This is where I may need the advice. My knowledge of JOINs is very underdeveloped. I think I went round in circles somewhat just coming to the point I'm at with this particular one.  If I may, could I give you the structure of the tables I am working with, and then a description of what I am trying to achieve?

 

tplss_matches

MatchID, MatchDateTime

 

tplss_players

PlayerID, PlayerName

 

tplss_appearances

AppearanceID, AppearancePlayerID, AppearanceMatchID

 

tplss_substitutions

SubstitutionID, SubstitutionPlayerIDIn, SubstitutionMatchID

 

tplss_goals

GoalID, GoalPlayerID, GoalMatchID

 

In the context of the PHP coding thus far, $id is the current MatchID, which has been requested from the URL earlier in the code.

 

What I want to display is a column of the players who have been selected to start the match in question.  I then want to display, in the next column, a corresponding list of each player's total appearances up to and including that match (i.e. prior to and including the MatchDateTime). Total appearances are the sum of any entries for the PlayerID and the current MatchID, found in either tplss_appearances or tplss_substititions.

 

Likewise, in the next corresponding column I want to show the player's goals scored, the sum of all entries match the PlayerID and the current MatchID in tplss_goals.

 

It would look a little like this:

 

stats.jpg

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.