MargateSteve
-
Posts
240 -
Joined
-
Last visited
Posts posted by MargateSteve
-
-
Sorry for the delay in getting back but there was a bit of delay in changing ISP's and thanks for your patience with this.
A quick look at your tests data suggests that it is pretty much the same but will have a proper look and post some of my data after work tomorrow.
Steve
-
I gave this a go but probably got it wrong as it just came back with 'No Results!'.
At the very top of the code I have
<?php require_once('../Connections/Test.php'); ?> <?php mysql_select_db($database_Test, $Test); $query = "SELECT * FROM player_season, players, player_positions WHERE date_left > '2011-07-01' AND player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position ORDER BY player_positions.position_order, players.position, players.surname ASC"; $results = mysql_query($query); ?>And in the body I have
<?php if($results) { if(mysql_num_rows($results)) { $last_player_postion_id = 0; while($row = mysql_fetch_assoc($results)) { // when the players positon id changes output a new heading if($last_player_postion_id != $row['player_position_id']) { echo '<h1>' . strtoupper($row['position']) . '</h1>'; $last_player_postion_id = $row['player_position_id']; } echo '<h3>' . $row['surname'] . ', ' . $row['firstname'] . '</h3>'; } } else { echo 'No results!'; } } ?>Did I need to put it together differently?
Steve
-
I must admit that I thought that would be the response once I posted the code!!!!
To be honest, I have got to the point now where on pages with fairly simple queries I am just hand coding them anyway but there are some things that I cannot find any definitive answers for wherever I search. The trouble is that there was a strict timescale to get the site live so I had to do whatever it took!!
Some things are still stumping me however much I read up and grouping is one of them. I have no problem (I think) with the query side of it but it is just getting it to show on the page as I want it. The only way that I think I could get a single query with grouping to show up the way it currently does on the page is with a repeat region within a repeat region but I am probably way of the mark!
I will put a new query together with the grouping and hopefully someone will be able to nudge me in the right direction to get it displayed directly!
Thanks
Steve
-
As I mentioned in the previous post, this is the code at http://www.margate-fc.com/content/1st_team/squad.php
The tables used are
TABLE `players` (
`player_id` int(11) NOT NULL auto_increment,
`surname` varchar(255) default NULL,
`firstname` varchar(255) default NULL,
`date_of_birth` date default NULL,
`position` int(11) default NULL, *FK to 'player_positions.player*
`image` varchar(255) default NULL,
`date_joined` date default NULL,
`date_left` date default NULL,
`previous_clubs` varchar(255) default NULL,
`place_of_birth` varchar(255) default NULL,
`home_sponsor` varchar(255) default NULL,
`away_sponsor` varchar(255) default NULL,
`profile` longtext,
`Triallist` varchar(10) default NULL,
PRIMARY KEY (`player_id`)
)
TABLE`player_season` (
`player_season_id` int(11) NOT NULL auto_increment,
`season` int(11) default NULL,
`player` int(11) default NULL, *FK to 'players.player_id*
PRIMARY KEY (`player_season_id`)
)
TABLE `player_positions` (
`player_position_id` int(11) NOT NULL auto_increment,
`position` varchar(45) default NULL,
`position_order` int(11) default NULL,
PRIMARY KEY (`player_position_id`)
)
There are 6 queries on the page, 5 of which are exactly the same all bar 'players.position' which allows me to group the players by their position. I am assuming that it would be better to use one query with a GROUP BY but cannot get my head around how to do that and get the information to display on the page as it does currently.
These 5 queries are
<?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $currentPage = $_SERVER["PHP_SELF"]; $maxRows_Management = 100; $pageNum_Management = 0; if (isset($_GET['pageNum_Management'])) { $pageNum_Management = $_GET['pageNum_Management']; } $startRow_Management = $pageNum_Management * $maxRows_Management; mysql_select_db($database_Test, $Test); $query_Management = "SELECT * FROM player_season, players, player_positions WHERE date_left > '2011-07-01' AND player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position AND players.position = '5' ORDER BY surname ASC"; $query_limit_Management = sprintf("%s LIMIT %d, %d", $query_Management, $startRow_Management, $maxRows_Management); $Management = mysql_query($query_limit_Management, $Test) or die(mysql_error()); $row_Management = mysql_fetch_assoc($Management); if (isset($_GET['totalRows_Management'])) { $totalRows_Management = $_GET['totalRows_Management']; } else { $all_Management = mysql_query($query_Management); $totalRows_Management = mysql_num_rows($all_Management); } $totalPages_Management = ceil($totalRows_Management/$maxRows_Management)-1; $queryString_Management = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_Management") == false && stristr($param, "totalRows_Management") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_Management = "&" . htmlentities(implode("&", $newParams)); } } $queryString_Management = sprintf("&totalRows_Management=%d%s", $totalRows_Management, $queryString_Management); ?><?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $currentPage = $_SERVER["PHP_SELF"]; $maxRows_PlayerManagement = 100; $pageNum_PlayerManagement = 0; if (isset($_GET['pageNum_PlayerManagement'])) { $pageNum_PlayerManagement = $_GET['pageNum_PlayerManagement']; } $startRow_PlayerManagement = $pageNum_PlayerManagement * $maxRows_PlayerManagement; mysql_select_db($database_Test, $Test); $query_PlayerManagement = "SELECT * FROM player_season, players, player_positions WHERE date_left > '2011-07-01' AND player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position AND players.position = '6' ORDER BY surname ASC"; $query_limit_PlayerManagement = sprintf("%s LIMIT %d, %d", $query_PlayerManagement, $startRow_PlayerManagement, $maxRows_PlayerManagement); $PlayerManagement = mysql_query($query_limit_PlayerManagement, $Test) or die(mysql_error()); $row_PlayerManagement = mysql_fetch_assoc($PlayerManagement); if (isset($_GET['totalRows_PlayerManagement'])) { $totalRows_PlayerManagement = $_GET['totalRows_PlayerManagement']; } else { $all_PlayerManagement = mysql_query($query_PlayerManagement); $totalRows_PlayerManagement = mysql_num_rows($all_PlayerManagement); } $totalPages_PlayerManagement = ceil($totalRows_PlayerManagement/$maxRows_PlayerManagement)-1; $queryString_PlayerManagement = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_PlayerManagement") == false && stristr($param, "totalRows_PlayerManagement") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_PlayerManagement = "&" . htmlentities(implode("&", $newParams)); } } $queryString_PlayerManagement = sprintf("&totalRows_PlayerManagement=%d%s", $totalRows_PlayerManagement, $queryString_PlayerManagement); ?><?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $currentPage = $_SERVER["PHP_SELF"]; $maxRows_Goalkeepers = 100; $pageNum_Goalkeepers = 0; if (isset($_GET['pageNum_Goalkeepers'])) { $pageNum_Goalkeepers = $_GET['pageNum_Goalkeepers']; } $startRow_Goalkeepers = $pageNum_Goalkeepers * $maxRows_Goalkeepers; mysql_select_db($database_Test, $Test); $query_Goalkeepers = "SELECT * FROM player_season, players, player_positions WHERE date_left > '2011-07-01' AND player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position AND players.position = '1' ORDER BY surname ASC"; $query_limit_Goalkeepers = sprintf("%s LIMIT %d, %d", $query_Goalkeepers, $startRow_Goalkeepers, $maxRows_Goalkeepers); $Goalkeepers = mysql_query($query_limit_Goalkeepers, $Test) or die(mysql_error()); $row_Goalkeepers = mysql_fetch_assoc($Goalkeepers); if (isset($_GET['totalRows_Goalkeepers'])) { $totalRows_Goalkeepers = $_GET['totalRows_Goalkeepers']; } else { $all_Goalkeepers = mysql_query($query_Goalkeepers); $totalRows_Goalkeepers = mysql_num_rows($all_Goalkeepers); } $totalPages_Goalkeepers = ceil($totalRows_Goalkeepers/$maxRows_Goalkeepers)-1; $queryString_Goalkeepers = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_Goalkeepers") == false && stristr($param, "totalRows_Goalkeepers") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_Goalkeepers = "&" . htmlentities(implode("&", $newParams)); } } $queryString_Goalkeepers = sprintf("&totalRows_Goalkeepers=%d%s", $totalRows_Goalkeepers, $queryString_Goalkeepers); ?><?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $currentPage = $_SERVER["PHP_SELF"]; $maxRows_Defenders = 100; $pageNum_Defenders = 0; if (isset($_GET['pageNum_Defenders'])) { $pageNum_Defenders = $_GET['pageNum_Defenders']; } $startRow_Defenders = $pageNum_Defenders * $maxRows_Defenders; mysql_select_db($database_Test, $Test);$query_Defenders = "SELECT * FROM player_season, players, player_positions WHERE date_left > '2011-07-01' AND player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position AND players.position = '2' ORDER BY surname ASC "; $query_limit_Defenders = sprintf("%s LIMIT %d, %d", $query_Defenders, $startRow_Defenders, $maxRows_Defenders); $Defenders = mysql_query($query_limit_Defenders, $Test) or die(mysql_error()); $row_Defenders = mysql_fetch_assoc($Defenders); if (isset($_GET['totalRows_Defenders'])) { $totalRows_Defenders = $_GET['totalRows_Defenders']; } else { $all_Defenders = mysql_query($query_Defenders); $totalRows_Defenders = mysql_num_rows($all_Defenders); } $totalPages_Defenders = ceil($totalRows_Defenders/$maxRows_Defenders)-1; $queryString_Defenders = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_Defenders") == false && stristr($param, "totalRows_Defenders") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_Defenders = "&" . htmlentities(implode("&", $newParams)); } } $queryString_Defenders = sprintf("&totalRows_Defenders=%d%s", $totalRows_Defenders, $queryString_Defenders); header('Content-Type: text/html; charset=ISO-8859-1', true); ?><?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $currentPage = $_SERVER["PHP_SELF"]; $maxRows_Midfielders = 100; $pageNum_Midfielders = 0; if (isset($_GET['pageNum_Midfielders'])) { $pageNum_Midfielders = $_GET['pageNum_Midfielders']; } $startRow_Midfielders = $pageNum_Midfielders * $maxRows_Midfielders; mysql_select_db($database_Test, $Test);$query_Midfielders = "SELECT * FROM player_season, players, player_positions WHERE date_left > '2011-07-01' AND player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position AND players.position = '3' ORDER BY surname ASC"; $query_limit_Midfielders = sprintf("%s LIMIT %d, %d", $query_Midfielders, $startRow_Midfielders, $maxRows_Midfielders); $Midfielders = mysql_query($query_limit_Midfielders, $Test) or die(mysql_error()); $row_Midfielders = mysql_fetch_assoc($Midfielders); if (isset($_GET['totalRows_Midfielders'])) { $totalRows_Midfielders = $_GET['totalRows_Midfielders']; } else { $all_Midfielders = mysql_query($query_Midfielders); $totalRows_Midfielders = mysql_num_rows($all_Midfielders); } $totalPages_Midfielders = ceil($totalRows_Midfielders/$maxRows_Midfielders)-1; $queryString_Midfielders = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_Midfielders") == false && stristr($param, "totalRows_Midfielders") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_Midfielders = "&" . htmlentities(implode("&", $newParams)); } } $queryString_Midfielders = sprintf("&totalRows_Midfielders=%d%s", $totalRows_Midfielders, $queryString_Midfielders); header('Content-Type: text/html; charset=ISO-8859-1', true); ?><?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $currentPage = $_SERVER["PHP_SELF"]; $maxRows_Forwards = 100; $pageNum_Forwards = 0; if (isset($_GET['pageNum_Forwards'])) { $pageNum_Forwards = $_GET['pageNum_Forwards']; } $startRow_Forwards = $pageNum_Forwards * $maxRows_Forwards; mysql_select_db($database_Test, $Test);$query_Forwards = "SELECT * FROM player_season, players, player_positions WHERE date_left > '2011-07-01' AND player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position AND players.position = '4' ORDER BY surname ASC"; $query_limit_Forwards = sprintf("%s LIMIT %d, %d", $query_Forwards, $startRow_Forwards, $maxRows_Forwards); $Forwards = mysql_query($query_limit_Forwards, $Test) or die(mysql_error()); $row_Forwards = mysql_fetch_assoc($Forwards); if (isset($_GET['totalRows_Forwards'])) { $totalRows_Forwards = $_GET['totalRows_Forwards']; } else { $all_Forwards = mysql_query($query_Forwards); $totalRows_Forwards = mysql_num_rows($all_Forwards); } $totalPages_Forwards = ceil($totalRows_Forwards/$maxRows_Forwards)-1; $queryString_Forwards = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_Forwards") == false && stristr($param, "totalRows_Forwards") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_Forwards = "&" . htmlentities(implode("&", $newParams)); } } $queryString_Forwards = sprintf("&totalRows_Forwards=%d%s", $totalRows_Forwards, $queryString_Forwards); header('Content-Type: text/html; charset=ISO-8859-1', true); ?>The 6th query also differs in the 'WHERE date_left' part as it is used to show players that left before the end of this current season (I wanted to leave the 'WHERE date_left' part out of the first 5 queries but every time I try to leave a date field NULL it inserts the current date, even if I have set the column as NULL in PHPMyAdmin)
<?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $currentPage = $_SERVER["PHP_SELF"]; $maxRows_Left = 100; $pageNum_Left = 0; if (isset($_GET['pageNum_Left'])) { $pageNum_Left = $_GET['pageNum_Left']; } $startRow_Left = $pageNum_Left * $maxRows_Left; mysql_select_db($database_Test, $Test);$query_Left = "SELECT * FROM player_season, players, player_positions WHERE date_left <= '2011-06-01' AND player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position ORDER BY surname ASC"; $query_limit_Left = sprintf("%s LIMIT %d, %d", $query_Left, $startRow_Left, $maxRows_Left); $Left = mysql_query($query_limit_Left, $Test) or die(mysql_error()); $row_Left = mysql_fetch_assoc($Left); if (isset($_GET['totalRows_Left'])) { $totalRows_Left = $_GET['totalRows_Left']; } else { $all_Left = mysql_query($query_Left); $totalRows_Left = mysql_num_rows($all_Left); } $totalPages_Left = ceil($totalRows_Left/$maxRows_Left)-1; $queryString_Left = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_Left") == false && stristr($param, "totalRows_Left") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_Left = "&" . htmlentities(implode("&", $newParams)); } } $queryString_Left = sprintf("&totalRows_Left=%d%s", $totalRows_Left, $queryString_Left); header('Content-Type: text/html; charset=ISO-8859-1', true); ?>One of the problems I have had with this code is that there is potentially no limit to the number of players that could be in any position. I have tried to remove the
$maxRows_xxx = 100;
but each time I do the browser shows an error pointing to the lines that
$query_limit_Left = sprintf("%s LIMIT %d, %d"is on.
The html is a similar thing as the first 5 queries are shown in identical ways but in only 4 sections as the 'management' and 'player_management' queries are (sort of) grouped together.
<div class="Profile_Info">MANAGEMENT</div> <?php do { ?> <table width="590" border="0" align="center" cellspacing="0"> <?php do { ?> <tr> <td colspan="2" class="opposition_name"><?php echo $row_Management['surname']; ?>, <?php echo $row_Management['firstname']; ?></a> <em>( <?php echo $row_Management['position']; ?> )</em></td> <td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><?php echo ('<img src="'.$row_Management['image'].'" width="120" height="120" />'); ?></td> </tr> <tr> <td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br /> <?php echo date('jS F Y',strtotime($row_Management['date_of_birth'])); ?> <br /> <strong>JOINED<br /> </strong><?php echo date('F Y',strtotime($row_Management['date_joined'])); ?></td> <td width="309" align="left" ><strong>HOME SPONSOR<br /> </strong><?php if ($row_Management['home_sponsor']<>"") echo $row_Management['home_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /> <strong>AWAY SPONSOR<br /> </strong><?php if ($row_Management['away_sponsor']<>"") echo $row_Management['away_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /></td> </tr> <tr> <td height="19" align="right" class="Normal_Table_Column"><a href="management_details.php?recordID=<?php echo $row_Management['player_id']; ?>"> See <?php echo $row_Management['firstname']; ?>'s Profile</a> </td> </tr> <?php } while ($row_league_opponents = mysql_fetch_assoc($league_opponents)); ?> </table> <?php } while ($row_Management = mysql_fetch_assoc($Management)); ?> <?php do { ?> <table width="590" border="0" align="center" cellspacing="0"> <?php do { ?> <tr> <td colspan="2" class="opposition_name"><?php echo $row_PlayerManagement['surname']; ?>, <?php echo $row_PlayerManagement['firstname']; ?></a> <em>( <?php echo $row_PlayerManagement['position']; ?> )</em></td> <td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><?php echo ('<img src="'.$row_PlayerManagement['image'].'" width="120" height="120" />'); ?></td> </tr> <tr> <td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br /> <?php echo date('jS F Y',strtotime($row_PlayerManagement['date_of_birth'])); ?> <br /> <strong>JOINED<br /> </strong><?php echo date('F Y',strtotime($row_PlayerManagement['date_joined'])); ?></td> <td width="309" align="left" ><strong>HOME SPONSOR<br /> </strong><?php if ($row_PlayerManagement['home_sponsor']<>"") echo $row_PlayerManagement['home_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /> <strong>AWAY SPONSOR<br /> </strong><?php if ($row_PlayerManagement['away_sponsor']<>"") echo $row_PlayerManagement['away_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /></td> </tr> <tr> <td height="19" align="right" class="Normal_Table_Column"><a href="squad_details.php?recordID=<?php echo $row_PlayerManagement['player_id']; ?>"> See <?php echo $row_PlayerManagement['firstname']; ?>'s Profile</a> </td> </tr> <?php } while ($row_league_opponents = mysql_fetch_assoc($league_opponents)); ?> </table> <?php } while ($row_PlayerManagement = mysql_fetch_assoc($PlayerManagement)); ?><div class="Profile_Info">GOALKEEPERS</div> <?php do { ?> <table width="590" border="0" align="center" cellspacing="0"> <?php do { ?> <tr> <td colspan="2" class="opposition_name"><?php echo $row_Goalkeepers['surname']; ?>, <?php echo $row_Goalkeepers['firstname']; ?></a></td> <td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><?php echo ('<img src="'.$row_Goalkeepers['image'].'" width="120" height="120" />'); ?></td> </tr> <tr> <td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br /> <?php if ($row_Goalkeepers['date_of_birth']>"2000-01-01") echo 'TBA'; else echo date('jS F Y',strtotime($row_Goalkeepers['date_of_birth'])); ?> <br /> <strong>JOINED<br /> </strong><?php echo date('F Y',strtotime($row_Goalkeepers['date_joined'])); ?></td> <td width="309" align="left" ><strong>HOME SPONSOR<br /> </strong><?php if ($row_Goalkeepers['home_sponsor']<>"") echo $row_Goalkeepers['home_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /> <strong>AWAY SPONSOR<br /> </strong><?php if ($row_Goalkeepers['away_sponsor']<>"") echo $row_Goalkeepers['away_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /></td> </tr> <tr> <td height="19" align="right" class="Normal_Table_Column"><a href="squad_details.php?recordID=<?php echo $row_Goalkeepers['player_id']; ?>"> See <?php echo $row_Goalkeepers['firstname']; ?>'s Profile</a> </td> </tr> <?php } while ($row_league_opponents = mysql_fetch_assoc($league_opponents)); ?> </table> <?php } while ($row_Goalkeepers = mysql_fetch_assoc($Goalkeepers)); ?> <br /><div class="Profile_Info">DEFENDERS</div> <?php do { ?> <table width="590" border="0" align="center" cellspacing="0"> <?php do { ?> <tr> <td colspan="2" class="opposition_name"><?php echo $row_Defenders['surname']; ?>, <?php echo $row_Defenders['firstname']; ?></a></td> <td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><?php echo ('<img src="'.$row_Defenders['image'].'" width="120" height="120" />'); ?></td> </tr> <tr> <td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br /> <?php if ($row_Defenders['date_of_birth']>"2000-01-01") echo 'TBA'; else echo date('jS F Y',strtotime($row_Defenders['date_of_birth'])); ?> <br /> <strong>JOINED<br /> </strong><?php echo date('F Y',strtotime($row_Defenders['date_joined'])); ?></td> <td width="309" align="left" ><strong>HOME SPONSOR<br /> </strong><?php if ($row_Defenders['home_sponsor']<>"") echo $row_Defenders['home_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /> <strong>AWAY SPONSOR<br /> </strong><?php if ($row_Defenders['away_sponsor']<>"") echo $row_Defenders['away_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /></td> </tr> <tr> <td height="19" align="right" class="Normal_Table_Column"><a href="squad_details.php?recordID=<?php echo $row_Defenders['player_id']; ?>"> See <?php echo $row_Defenders['firstname']; ?>'s Profile</a> </td> </tr> <?php } while ($row_league_opponents = mysql_fetch_assoc($league_opponents)); ?> </table> <?php } while ($row_Defenders = mysql_fetch_assoc($Defenders)); ?> <br /><div class="Profile_Info">MIDFIELDERS</div> <?php do { ?> <table width="590" border="0" align="center" cellspacing="0"> <?php do { ?> <tr> <td colspan="2" class="opposition_name"><?php echo $row_Midfielders['surname']; ?>, <?php echo $row_Midfielders['firstname']; ?></a></td> <td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><?php echo ('<img src="'.$row_Midfielders['image'].'" width="120" height="120" />'); ?></td> </tr> <tr> <td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br /> <?php if ($row_Midfielders['date_of_birth']>"2000-01-01") echo 'TBA'; else echo date('jS F Y',strtotime($row_Midfielders['date_of_birth'])); ?> <br /> <strong>JOINED<br /> </strong><?php echo date('F Y',strtotime($row_Midfielders['date_joined'])); ?></td> <td width="309" align="left" ><strong>HOME SPONSOR<br /> </strong><?php if ($row_Midfielders['home_sponsor']<>"") echo $row_Midfielders['home_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /> <strong>AWAY SPONSOR<br /> </strong><?php if ($row_Midfielders['away_sponsor']<>"") echo $row_Midfielders['away_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /></td> </tr> <tr> <td height="19" align="right" class="Normal_Table_Column"><a href="squad_details.php?recordID=<?php echo $row_Midfielders['player_id']; ?>"> See <?php echo $row_Midfielders['firstname']; ?>'s Profile</a> </td> </tr> <?php } while ($row_league_opponents = mysql_fetch_assoc($league_opponents)); ?> </table> <?php } while ($row_Midfielders = mysql_fetch_assoc($Midfielders)); ?> <br /><div class="Profile_Info">FORWARDS</div> <?php do { ?> <table width="590" border="0" align="center" cellspacing="0"> <?php do { ?> <tr> <td colspan="2" class="opposition_name"><?php echo $row_Forwards['surname']; ?>, <?php echo $row_Forwards['firstname']; ?></a></td> <td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><?php echo ('<img src="'.$row_Forwards['image'].'" width="120" height="120" />'); ?></td> </tr> <tr> <td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br /> <?php if ($row_Forwards['date_of_birth']>"2000-01-01") echo 'TBA'; else echo date('jS F Y',strtotime($row_Forwards['date_of_birth'])); ?> <br /> <strong>JOINED<br /> </strong><?php echo date('F Y',strtotime($row_Forwards['date_joined'])); ?></td> <td width="309" align="left" ><strong>HOME SPONSOR<br /> </strong><?php if ($row_Forwards['home_sponsor']<>"") echo $row_Forwards['home_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /> <strong>AWAY SPONSOR<br /> </strong><?php if ($row_Forwards['away_sponsor']<>"") echo $row_Forwards['away_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /></td> </tr> <tr> <td height="19" align="right" class="Normal_Table_Column"><a href="squad_details.php?recordID=<?php echo $row_Forwards['player_id']; ?>"> See <?php echo $row_Forwards['firstname']; ?>'s Profile</a> </td> </tr> <?php } while ($row_league_opponents = mysql_fetch_assoc($league_opponents)); ?> </table> <?php } while ($row_Forwards = mysql_fetch_assoc($Forwards)); ?> <br /> <br /> <br />The last one caters for the players that have left during the season and the only difference is the addition of echoing 'date_left'. Once again, I would have liked to just had something along the lines of 'If date_left ="" echo "" else echo date_left' throughout the page but as the 'date_left' field will not accept NULL that would not work.
<div class="Profile_Info">GONE BUT NOT FORGOTTEN.....</div> <?php do { ?> <table width="590" border="0" align="center" cellspacing="0"> <?php do { ?> <tr> <td colspan="2" class="opposition_name"><?php echo $row_Left['surname']; ?>, <?php echo $row_Left['firstname']; ?></a></td> <td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><?php echo ('<img src="'.$row_Left['image'].'" width="120" height="120" />'); ?></td> </tr> <tr> <td width="189" rowspan="2" valign="top" class="Normal_Table_Column"><strong>DATE OF BIRTH: </strong><br /> <?php if ($row_Left['date_of_birth']>"2000-01-01") echo 'TBA'; else echo date('jS F Y',strtotime($row_Left['date_of_birth'])); ?> <br /> <strong>JOINED<br /> </strong><?php echo date('F Y',strtotime($row_Left['date_joined'])); ?> <br /> <strong>LEFT<br /> </strong> <?php echo date('F Y',strtotime($row_Left['date_left'])); ?></td> <td width="309" align="left" ><strong>HOME SPONSOR<br /> </strong><?php if ($row_Left['home_sponsor']<>"") echo $row_Left['home_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /> <strong>AWAY SPONSOR<br /> </strong><?php if ($row_Left['away_sponsor']<>"") echo $row_Left['away_sponsor']; else echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ?><br /></td> </tr> <tr> <td height="19" align="right" class="Normal_Table_Column"><a href="squad_details.php?recordID=<?php echo $row_Left['player_id']; ?>"> See <?php echo $row_Left['firstname']; ?>'s Profile</a> </td> </tr> <?php } while ($row_Left = mysql_fetch_assoc($Left)); ?> </table> <?php } while ($row_Left = mysql_fetch_assoc($Left)); ?>Even with my naivety, I can see there is far more code there than is needed and am pretty sure that grouping would work for most of it but, as the site is actually live I am trying to reduce the page sizes before I go into reading up on new things!!!
Any suggestions, advice or pointers would be gratefully received!! Also, if I am not being cheeky or asking to much, it would be nice to know why certain bits of the code are needed or not needed as it will help me help myself in future!!
Thanks in advance
Steve
-
I haven't forgotten about this. There has been a couple of other problems with the site that needed sorting first but I will post the code tomorrow evening.
Steve
-
Dreamweaver is horrible at making PHP code, and alot is redundant, even more is useless.
That is exactly what I thought!!
Off to bed now as I have work in a few hours but I will post the whole code this evening (UK evening!).
Thanks
Steve
-
My learning curve to this point in using php with MySql databases is automatically generated code within Dreamweaver. However, I am sure that it is adding a lot more code than I actually need and I sometimes have 5 or more queries all on one page that are just all the same except that each one has a different ID to reference.
Presumably the best way to do this would be with one query with grouping but as that is still on my list of things to understand I am hoping to tidy things up myself first.
For example, two of the queries that I have on one page (http://www.margate-fc.com/content/1st_team/squad.php) are
<?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $currentPage = $_SERVER["PHP_SELF"]; $maxRows_Midfielders = 100; $pageNum_Midfielders = 0; if (isset($_GET['pageNum_Midfielders'])) { $pageNum_Midfielders = $_GET['pageNum_Midfielders']; } $startRow_Midfielders = $pageNum_Midfielders * $maxRows_Midfielders; mysql_select_db($database_Test, $Test);$query_Midfielders = "SELECT * FROM player_season, players, player_positions WHERE player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position AND players.position = '3' ORDER BY surname ASC"; $query_limit_Midfielders = sprintf("%s LIMIT %d, %d", $query_Midfielders, $startRow_Midfielders, $maxRows_Midfielders); $Midfielders = mysql_query($query_limit_Midfielders, $Test) or die(mysql_error()); $row_Midfielders = mysql_fetch_assoc($Midfielders); if (isset($_GET['totalRows_Midfielders'])) { $totalRows_Midfielders = $_GET['totalRows_Midfielders']; } else { $all_Midfielders = mysql_query($query_Midfielders); $totalRows_Midfielders = mysql_num_rows($all_Midfielders); } $totalPages_Midfielders = ceil($totalRows_Midfielders/$maxRows_Midfielders)-1; $queryString_Midfielders = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_Midfielders") == false && stristr($param, "totalRows_Midfielders") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_Midfielders = "&" . htmlentities(implode("&", $newParams)); } } $queryString_Midfielders = sprintf("&totalRows_Midfielders=%d%s", $totalRows_Midfielders, $queryString_Midfielders); header('Content-Type: text/html; charset=ISO-8859-1', true); ?>and
<?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $currentPage = $_SERVER["PHP_SELF"]; $maxRows_Forwards = 100; $pageNum_Forwards = 0; if (isset($_GET['pageNum_Forwards'])) { $pageNum_Forwards = $_GET['pageNum_Forwards']; } $startRow_Forwards = $pageNum_Forwards * $maxRows_Forwards; mysql_select_db($database_Test, $Test);$query_Forwards = "SELECT * FROM player_season, players, player_positions WHERE date_left = '2011-07-01' AND player_season.season = '104' AND player_season.player = players.player_id AND player_positions.player_position_id = players.position AND players.position = '4' ORDER BY surname ASC"; $query_limit_Forwards = sprintf("%s LIMIT %d, %d", $query_Forwards, $startRow_Forwards, $maxRows_Forwards); $Forwards = mysql_query($query_limit_Forwards, $Test) or die(mysql_error()); $row_Forwards = mysql_fetch_assoc($Forwards); if (isset($_GET['totalRows_Forwards'])) { $totalRows_Forwards = $_GET['totalRows_Forwards']; } else { $all_Forwards = mysql_query($query_Forwards); $totalRows_Forwards = mysql_num_rows($all_Forwards); } $totalPages_Forwards = ceil($totalRows_Forwards/$maxRows_Forwards)-1; $queryString_Forwards = ""; if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_Forwards") == false && stristr($param, "totalRows_Forwards") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_Forwards = "&" . htmlentities(implode("&", $newParams)); } } $queryString_Forwards = sprintf("&totalRows_Forwards=%d%s", $totalRows_Forwards, $queryString_Forwards); header('Content-Type: text/html; charset=ISO-8859-1', true); ?>The only difference in the two queries are the query names and the WHERE players.position = 'XX'
The way that I see it is that everything below
$currentPage = $_SERVER["PHP_SELF"];
is unique to each query but everything above is replicated.
Would it work to have all of it between one set of PHP tags with the duplicated code there just once? If so would it simply mean copying everything below '$currentPage = $_SERVER["PHP_SELF"];' in the second query and pasting it just before the '?>' in the first.
Also, if anyone is an expert with grouping, I would be more than open to suggestions about setting the page up that way. My head says that it would need a repeat region inside a repeat region to get the data separated the way I want it but my head doesn't often make sense!
Thanks in advance
Steve
-
Firstly, thanks for your patience with this. Often on forums the response to someone with as little knowledge as I have is to tell the asker to look at a page at dev.mysql.com or php.net that explains it in overly technical terms before coming back and asking the question properly. It is refreshing to find somewhere that gives help to noobs!
Secondly, you would not believe the length that this post was originally because I still could not get it to work. I put in all my various attempts at tweaking the query and other things I had tried but as I went to hit post I had a brainwave and sorted it!! Simply put, I put the wrong call in the html looking for
<?php echo $row_all_games['team_name']; ?>
instead of <?php echo $row_all_games['HomeTeam']; ?>
<?php echo $row_all_games['AwayTeam']; ?>
Column 'aliasing' is a new thing to me but I think I get the gist and can understand that
FROM all_games AS AG
basically gives a 'shorthand' version of the 'all_games' table name for future use in the query as does 'HT' and 'VT' for the 'teams' table.
I have taken out the AG alias and replaced it with the table names just to stop me getting confused for now. I have also taken out the alias's for home_score and away_score as '*' seems to do the trick with it. I am not picking your code apart as I have not got a clue what I am doing, but it will be easier for me to just restrict it to two alias's for now!!!
The query that I used in the end, including the id passed from a previous page is
SELECT *, HT.team_name as HomeTeam, VT.team_name as AwayTeam FROM all_games, teams JOIN teams as HT ON all_games.home_team = HT.team_id JOIN teams AS VT ON all_games.away_team = VT.team_id WHERE '%s' IN (home_team, away_team )
I will move on in the next couple of days with the next stage of it and a few other columns that need to go in but I know where to come running back to if(when) I hit another problem!
Massive thanks once again
Steve
-
Thanks for that David but I am afraid it has gone way over my head!!
I replaced the query with your one but all that was returned was
Unknown column 'AG.home_score' in 'field list'
I assume that some of that had to be changed to suit my needs but as I am very new to all of this I am not sure what to change! Based on the theory that "AG.home_score" is 'table.field' I changed it to "all_games.home_score" but got
Unknown table 'all_games' in field list
As I am on a steep learning curve with this (only started in earnest in June) would it be possible to give me an explanation of what the query is doing. Hopefully with a bit of understanding I could work out what to change. Also, if I understand it will save me from asking the same question when I hit a future (and there will be) stumbling block!
Thanks in advance
Steve
-
I have a table that holds a set of matches in a football (soccer) league. On a teams individual page I want to show just their matches and am very close to getting it right but just cannot get there!
I am using 2 tables for this....
teams
team_idteam_name
1TEAM1
2TEAM2
3TEAM3
4TEAM4and
all_games
game_idhome_teamhome_scoreaway_teamaway_score
11 220
23 041
34 211
42 030Both 'home_team' and 'away_team' are foreign keys to teams.team_id.
The query that I have used to almost get it working is
SELECT * FROM all_games, teams WHERE '%s' IN (home_team, away_team ) AND all_games.home_team=teams.team_id
And the html is
<tr> <td>all_games_id</td> <td>home_team</td> <td>home_goals</td> <td>away_team</td> <td>away_goals</td> </tr> <tr> <td><?php echo $row_all_games['all_games_id']; ?></td> <td><?php echo $row_all_games['team_name']; ?></td> <td><?php echo $row_all_games['home_goals']; ?></td> <td><?php echo $row_all_games['team_name']; ?></td> <td><?php echo $row_all_games['away_goals']; ?></td> </tr>
This pulls the correct games into the page but will show the 'home_team.team_name' as both 'home_team' and 'away_team'. For example the page for TEAM1 shows up as
match_idhome_teamhome_scoreaway_teamaway_score
1TEAM12TEAM10
3TEAM42TEAM41What it should show is
match_idhome_teamhome_scoreaway_teamaway_score
1TEAM12TEAM20
3TEAM42TEAM11Obviously in the current query there is nothing to show that the 'away_team' is also equal to 'teams.team_id'. I have tried this query
SELECT * FROM all_games, teams WHERE '%s' IN (home_team, away_team ) AND (all_games.home_team=teams.team_id OR all_games.away_team=teams.team_id)
But this gives the exact opposite result with just the 'away_team.team_name' showing up as 'home_team' and 'away_team'
match_idhome_teamhome_scoreaway_teamaway_score
1TEAM22TEAM20
3TEAM12TEAM11while
SELECT * FROM all_games, teams WHERE '%s' IN (home_team, away_team ) AND (all_games.home_team=teams.team_id AND all_games.away_team=teams.team_id)
throws up an empty query.
Does anyone have any suggestions on how this can be done? I have exhausted my patience with Google simply as I do not know what the correct search term is.
As an aside, when I do get it working, I want the format on the page to be slightly different in that instead of showing both home_team and away_team names I would want it to show "H" if the team in question is the 'home_team' and "A" if it is the 'away_ team' plus the name of the team that they played and whether the 'home_score' or 'away_score' belonged to them, as well as whether they won, drew or lost the game. So, staying with the page for TEAM1 it would show as
MATCH IDHOME or AWAYOPPONENTW/D/LSCORE FORSCORE AGAINST
1HTEAM2Won20
3ATEAM4Lost12I am currently planning on doing this with IF statements in the html, something along these lines
if home_team = %s echo "Home" else echo "Away"
and
if home_team = %s AND home_score>away_score echo "Won" else if away_team = %s and away_score>home_score echo "Won" else if home_score= away_score echo "Drew" else echo "Lost
If anyone can think of a better way to do this ( I am naturally assuming that it would not work in the way I intend!)I would be extremely greatful too!
Thanks in advance for any advice, pointers or offers to write the whole code!!!
Steve
-
I am your typical newbie who has got as far as they can developing a database driven web site and now has come to the point were desperate cries for help are needed!! In fact I did join this site back in June and posted a question but there was no response, probably because of the way I set the question out. However, I feel I understand things slightly better now and am quite pleased with how far I have come along but there is still a long way to go.
I have redesigned the web site for my local football(soccer) team (http://www.margate-fc.com) and from the outset decided that is needed to be mainly php/mysql driven as a lot of the information (mainly statistics) would be replicated in several different pages. Also this would mean other people could update the site simply via forms.
The actual news side is powered by CuteNews and the static content is through PulseCMS but anything on the team side such as results, players and statistics is pulled from several tables.
I have been trying to master this for years but finally sat down and read up on the basics and coupled with Dreamweaver and a trial of PHPMaker have got the site working. However, both those programs are fine at getting a basic site together, both have restrictions that I have run into already.
For example, PHPMaker's drop down boxes only allow two display fields when I will, occasionally, need more. Dreamweaver likes helping you set up recordsets and queries that use one table but if you are using more it is down to hard coding. On top of that, I am, pretty sure that, because of their nature, both are guilty of putting in a lot more code than is actually needed and with the case of Dreamweaver I tend to copy working code and edit it for a new query so there may well be 3 or 4 bits of duplicate code that is redundant in a page.
What I am hoping to do now is sort out the two or three things I still have left to set-up with the site but also I would like to learn how do do all of this off my own back and not need to rely on third-party software. I have already tentatively dipped a foot into the water with this by setting up a list page that has add/edit/delete buttons and most of this worked other than hitting delete meant that the record was just deleted instead of taking me to delete.php and asking me to confirm I wanted to delete.
I have searched forums and read up on the bits and pieces that I am still trying to do but in a lot of cases the descriptions have seemingly been written for those with a complete understanding and I am not yet in that category!!
So hopefully there are people on here who can fill the gaps I have and also not get too frustrated if I do not understand the instructions first time!
Regards
Steve
-
Apologies for joining and then steaming straight in with a question but I am really struggling with a project that is running out of time!
I am redesigning my Football Clubs web site and want all the statistical side to be database driven. After struggling to get to grips with most of it I am currently resigned to putting them together in Excel and importing into the database via PHPMyAdmin just to get it up and running, but am struggling on some of the simpler points. I will only go through them one at a time though and can reassure everyone that I have searched high and low for the answers but am not sure exactly how the question needs to be asked ('using radio buttons to filter mysql query' is how I asked it), hence the fact that time is running out. So the first question (with apologies if it is not formatted/asked correctly!)......
I am running a query that shows a list of football matches that a player has featured in but want users to be able to choose what they actually see via a combination of radio buttons or check boxes.
For example, if someone wanted to see only 'Home' matches that the player scored a goal in they could check those two buttons and the table will filter, preferably in the same page. The final form will have a lot more than that but I just want to get a grasp first!
My query is
SELECT * FROM player_apps WHERE Player LIKE 'saund%' ORDER BY Year DESC, Month DESC, Day DESC
This one is for the page for a specific player (surname Saunders)
And the 'call is
<?php do { ?> <tr> <td><?php echo $row_Recordset3['Day']; ?>/<?php echo $row_Recordset3['Month']; ?>/<?php echo $row_Recordset3['Year']; ?></td> <td><?php echo $row_Recordset3['H_A']; ?></td> <td><?php echo $row_Recordset3['Opponent']; ?></td> <td><?php echo $row_Recordset3['Comp']; ?></td> <td><?php echo $row_Recordset3['WDL']; ?></td> <td><?php echo $row_Recordset3['F']; ?> - <?php echo $row_Recordset3['A']; ?></td> <td><?php echo $row_Recordset3['Type']; ?></td> <td><?php echo $row_Recordset3['On']; ?></td> <td><?php echo $row_Recordset3['Off']; ?></td> <td><?php echo $row_Recordset3['MinsPlay']; ?></td> <td><?php echo $row_Recordset3['Goals']; ?></td> <td><?php echo $row_Recordset3['Assists']; ?></td> <td><?php echo $row_Recordset3['OwnGoals']; ?></td> <td><?php echo $row_Recordset3['Yellows']; ?></td> <td><?php echo $row_Recordset3['SentOff']; ?></td> <td><?php echo $row_Recordset3['Substituted By']; ?></td> </tr> <?php } while ($row_Recordset3 = mysql_fetch_assoc($Recordset3)); ?>The basic form I have got is
<form id="form1" name="form1" method="post" action=""> <p> <input type="radio" name="radio" id="All Games" value="All Games" /> <label for="All Games">All Games</label> <input type="radio" name="radio" id="Home Games" value="Home Games" /> <label for="Home Games">Home Games</label> <input type="radio" name="radio" id="Away Games" value="Away Games" /> <label for="Away Games">Away Games</label> <br /> <input type="radio" name="radio" id="Scored" value="Scored" /> <label for="Scored">Scored</label> <input type="radio" name="radio" id="Did not Score" value="Did not Score" /> <label for="Did not Score">Did not Score</label> </p> </form>
All I need to do now is work out how to set it so that if the 'Home' radio button is checked it will only show records where the 'H_A' field has 'Home' in it, which I have found some code for elsewhere. However, if 'Home' and 'Scored' are both checked it would need to show records where the 'H_A' field has 'Home' in it and 'Goals' is >0.
I am very new php and mysql but as I am under a very tight schedule to get this done I am doing it all the wrong way round but hopefully if I can get it all up and running, I can improve it at a later date.
If anyone can help, point me to a simple tutorial or even suggest a better way then I would be extremely greatful!
Regards
Steve
Streamlining Dreamweaver generated PHP code
in PHP Coding Help
Posted
The player_positions and player_season tables are dead right and the players one seems that way too other than any blank 'date_of_birth' have a future date as I cannot get NULLS to work correctly in the pages with an if 'date_of_birth' = "" sort of thing!
I have put some of my actual data from the tables (with the 'image' and 'profile' fields in 'players shortened) here just in case there is a difference I can't see!
Thanks again
Steve