Streamlining Dreamweaver generated PHP code

[MargateSteve]

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

[jcbones]

Dreamweaver is horrible at making PHP code, and alot is redundant, even more is useless.  All of the queries will be processed by a do/while loop, that needs to be changed to a while loop, etc.

 

Post this entire script, and we can get you pointed in the right direction.

[MargateSteve]

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

[MargateSteve]

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

[MargateSteve]

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

[wildteen88]

The code generated by Dreamweaver is a complete joke. It is impossible to learn from. Cleaning that code up will be a complete nightmare.You'll be better of starting from scratch.

 

There is no need to have 5 or 6 different queries for getting the manages/goalkeepers/defenders/midfielders/fowards separately. Depending on how you are structuring your tables you should only need to have just one query and one while loop.

[JonnoTheDev]

Agreed. If you attempt to modify the code produced by DW and then revert back to using the WYSIWYG in design view to add additional PHP, DW is likely to change the modifications you have made. Do not use DW in design view to connect to your database, create queries, list data, or add any server side code really. You are better just learning a bit of PHP/MySQL and working in the coder view. DW is actually very good in code view at assisting you with PHP's huge library of functions. If you are using an older version of DW be aware that some of the function library may be obsolete. I am running Adobe DW CS4.

[MargateSteve]

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

[wildteen88]

Here is some example code of how you'd output the results with just one query and a while loop.

$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);

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!';
    }
}

[MargateSteve]

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

[Pikachu2000]

The query may be failing. Change the query execution line as follows and see if any errors are generated. If not, you can then echo the query and paste it in to phpMyAdmin to see if any results are returned.

 

$results = mysql_query($query) or die( '<br />Query string: ' . $query . '<br />Failed with error: ' . mysql_error() . '<br />');

[wildteen88]

That query should be working. It is the same as your other queries except I omitted this line:

      players.position = '5' 

 

I also changed the ordering of the results.

ORDER BY player_positions.position_order, players.position, players.surname ASC";

 

I tested the code before posting it and it worked fine for me. However I populated the players, players_season and players_postions table with my own test data. Without knowing your exact data I cannot fully test the query. Could you provide some player data from your players, players_season and player_positions tables. That way I can test the query with your data.

 

This is the test data I used. I used the table structure  you posted earlier

INSERT INTO `players` (`player_id`, `surname`, `firstname`, `date_of_birth`, `position`, `image`, `date_joined`, `date_left`, `previous_clubs`, `place_of_birth`, `home_sponsor`, `away_sponsor`, `profile`, `Triallist`) VALUES
(1, 'O''CONNELL', 'IAIN', '1970-10-09', 5, NULL, '2010-02-01', '2011-07-11', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL),
(2, 'KEISTER', 'JOHN', '1970-11-11', 6, NULL, '2009-09-01', '2011-07-11', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL),
(3, 'LAMPRELL', 'MATT', NULL, 1, NULL, '2010-08-01', '2011-07-11', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL),
(4, 'TURNER', 'JAMIE', '1980-01-20', 1, NULL, '2010-03-01', '2011-07-11', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL),
(5, 'BALL', 'LAURENCE', '2010-09-02', 2, NULL, '2010-06-01', '2011-07-11', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL),
(6, 'CLOKE', 'CRAIG', '1984-09-21', 2, NULL, '2010-07-01', '2011-07-11', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL),
(7, 'MARSDEN', 'DARREN', '1987-01-21', 3, NULL, '2010-07-01', '2011-07-11', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL),
(8, 'SAUNDERS', 'JAY', '1979-04-27', 3, NULL, '2010-02-01', '2011-07-11', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL),
(9, 'KAMARA', 'ISHMAIL', '2010-09-02', 4, NULL, '2010-08-01', '2011-07-11', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL),
(10, 'PINNOCK', 'JAMES', '1978-08-01', 4, NULL, '2010-03-01', '2011-07-11', 'xxx', 'xxx', 'xxx', 'xxx', NULL, NULL);

--
-- Dumping data for table `player_positions`
--

INSERT INTO `player_positions` (`player_position_id`, `position`, `position_order`) VALUES
(1, 'goalkeepers', 3),
(2, 'defenders', 4),
(3, 'midfielders', 5),
(4, 'forwards', 6),
(5, 'manager', 1),
(6, 'player manager', 2);

--
-- Dumping data for table `player_season`
--

INSERT INTO `player_season` (`player_season_id`, `season`, `player`) VALUES
(1, 104, 1),
(2, 104, 2),
(3, 104, 3),
(4, 104, 4),
(5, 104, 5),
(6, 104, 6),
(7, 104, 7),
(8, 104, ,
(9, 104, 9),
(10, 104, 10);

[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

[MargateSteve]

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!

INSERT INTO `player_season` (`player_season_id`, `season`, `player`) VALUES
(1, 104, 34),
(2, 104, 37),
(3, 104, 22),
(4, 104, 33),
(5, 104, 35),
(6, 104, 29),
(7, 104, 38),
(8, 104, 20),
(9, 104, 30),
(10, 104, 14),
(11, 104, 13),
(12, 104, 39),
(13, 104, 74),
(14, 104, 75),
(15, 104, 76),
(21, 104, 89),
(17, 104, 78),
(18, 104, 84),
(22, 104, 36),
(23, 104, 82),
(24, 104, 83),
(25, 104, 87),
(26, 104, 86),
(27, 104, 90);

INSERT INTO `player_positions` (`player_position_id`, `position`, `position_order`) VALUES
(1, 'Goalkeeper', 3),
(2, 'Defender', 4),
(3, 'Midfielder', 5),
(4, 'Forward', 6),
(5, 'Manager', 1),
(6, 'Player Coach', 2);

INSERT INTO `players` (`player_id`, `surname`, `firstname`, `date_of_birth`, `position`, `image`, `date_joined`, `date_left`, `previous_clubs`, `place_of_birth`, `home_sponsor`, `away_sponsor`, `profile`, `Triallist`) VALUES
(74, 'Cloke', 'Craig', '1984-11-26', 2, 'http://www.margate-fc.com...med_IMG_7926.JPG', '2010-07-01', '2011-07-01', 'Dover Athletic, Ashford Town', NULL, NULL, NULL, 'profile text', NULL);
(75, 'Ball', 'Laurence', '2001-01-01', 2, 'http://www.margate-fc.com...med_IMG_7927.JPG', '2010-06-01', '2011-07-01', 'Dover Athletic, Altrincham, Ramsgate', 'Canterbury', NULL, NULL, 'profile text', NULL);
(76, 'Marsden', 'Darren', '1987-01-17', 3, 'http://www.margate-fc.com...med_IMG_7931.JPG', '2010-07-01', '2011-07-01', 'Herne Bay, Gillingham, Lordswood, Welling United', 'Sidcup', 'The Swinnock Family', NULL, 'profile text', NULL);
(78, 'Sigere', 'Jean-Michel', '1977-01-26', 4, 'http://www.margate-fc.com/...med_IMG_7929.JPG', '2010-07-01', '2010-08-01', 'Bordeaux, Lusitanos Saint-Maur (loan), Rushden & Diamonds, Stevenage Borough, Hornchurch, Heybridge Swifts, Gravesend & Northfleet, Lewes,	 Horsham (loan), Eastbourne Borough, Farnborough, Tonbridge Angels', NULL, 'The Swinnock Family', NULL, 'profile text', NULL);
NULL),
(82, 'Waugh', 'Alex', '2001-01-01', 4, 'http://www.margate-fc.com...med_IMG_7930.JPG', '2010-08-01', '2011-07-01', 'Malgo', NULL, 'Gary Lever', NULL, 'profile text', NULL);
(83, 'Kamara', 'Ishmail', NULL, 4, 'http://www.margate-fc.com...med_IMG_9398.JPG', '2010-08-01', '2011-07-01', 'Motala AIF', NULL, 'Harvey and Lesley Lockwood', NULL 'profile text', NULL);
(84, 'Sherman', 'Jim', '1989-02-27', 3, 'http://www.margate-fc.com...med_IMG_7924.JPG', '2010-08-01', '2011-07-01', 'Melbourne Knights, Whittlesea Zebras, Glenorchy Knights', 'Tasmania', NULL, NULL, 'profile text', NULL);
(86, 'Harrington', 'Kane', '2038-01-19', 2, 'http://www.margate-fc.com...med_IMG_7934.JPG', '2010-07-01', '2011-07-01', 'Gillingham, Chatham Town, Woking', NULL, NULL, NULL, 'profile text', NULL);
(87, 'Lamprell', 'Matt', '2038-01-19', 1, 'http://www.margate-fc.com...med_IMG_7922.JPG', '2010-08-01', '2011-07-01', 'Ebbsfleet United', NULL, 'C&J Promotions', NULL, 'profile text', NULL);
(89, 'O''Connell', 'Iain', '1970-10-09', 5, 'http://www.margate-fc.com...med_IMG_7936.JPG', '2010-02-01', NULL, 'Southend United, Dover Athletic, Chelmsford City, Great Wakering Rovers (Manager)', NULL, NULL, NULL, 'profile text', NULL);
(90, 'Welford', 'Shaun', '2038-01-19', 4, 'http://dover-athletic.co.uk...welford.jpg', '2010-08-01', '2011-07-01', 'Corinthian, Dover Athletic, Ramsgate', NULL, NULL, NULL, 'profile text', NULL);

 

Thanks again

Steve

[wildteen88]

Umm.The issue is with the data in the date_left column. It will be far better if your didn't set a date in the date_left column if the player hasn't left the team yet. Just set the date_left column to NULL for players that haven't left the team. You should only set the date in the date_left column when the player has left the team.

 

To test for NULL values in an SQL query you'd use the IS NULL statement. So I'd change this part of the query:

WHERE date_left > '2011-07-01' 

to

WHERE date_left IS NULL 

 

NOTE: To covert 2011-07-01 to NULL values you can run this query

UPDATE players SET date_left = NULL WHERE date_left = '2011-07-01'

[pornophobic]

I'm just going to put my two cents here: if you're learning PHP, don't rely on dreamweaver to generate code for you. I do use Dreamweaver myself, but that's because it helps me speed up my production and stay organized. If you're learning forget Dreamweaver for a bit, download Notepad ++ or some other PHP editor with syntax highlighting and start from the very beginning. It takes a lot more time, but in the long run you will much, much less confused and see how silly Dreamweaver PHP code is.

[JonnoTheDev]

If you're learning forget Dreamweaver for a bit, download Notepad ++ or some other PHP editor with syntax highlighting and start from the very beginning.

Dreamweaver has plenty of syntax highlighting. In fact I think it is one of the best IDEs for design & development. The issue is, not using the WYSIWYG for creating server side code. Coding should be done in the coder view. Design done in the design view. As I have already said, I use Adobe DW CS4 purely in coder view, even if I am just creating HTML. I never use the design view or any elements of the WYSYWIG. I maybe losing out on a lot of the functionality of DW but this is purely force of habbit as I am a developer.

[MargateSteve]

wildteen88 - You are a star!!!

 

I had tried to use NULL values before but it would not work for some reason. It is possible that I originally had the fields set that to NOT NULL until I started tidying everything up. Anyway, the test page I was working on (http://www.margate-fc.com/content/1st_team/squad2.php) it is all working and just needs finishing off.

I will not pretend for one second that I even begin to understand how it all works (although I can clearly see which part checks to see if there are any players left for a position and if not to move on to the next - it is just the first part that I still do not understand) so am going to try to learn exactly what it is actually doing!

 

As for Dreamweaver, I really only used it out of a necessity in that I had a very tight deadline to meet with it. However, the whole point of my posting on here is to try to take the overcomplicated dreamweaver code and learn how to do the same thing with less code and better functionality!!

 

Anyway, massive thanks to everyone for the help. Beware, though, there will be more questions and requests!!

Steve

[MargateSteve]

Also, even though I still have to put a little bit more in to get all the details I need on the page, the page is currently 680ish lines shorter!! That can only be a good thing!

 

Steve

[MargateSteve]

I started implementing all of the solutions I have been given by this forum this evening and this one works fine at the test page (http://www.margate-fc.com/content/1st_team/squad2.php) apart from a couple of stumbling blocks!!

 

Basically it comes from a couple of IF statements and a strtotime call to get the dates to show up the way I want ('dd/mm/yy' for dates of birth and 'mmm/yy' joined and left dates).

This is the html from my original page

 

<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['date_of_birth']));  ?>                   <br /><strong>JOINED<br /></strong>            <?php echo date('F Y',strtotime($row['date_joined'])); ?></td>  <td width="309" align="left" ><strong>HOME SPONSOR<br /></strong>           <?php if ($row['home_sponsor']<>"")               echo  $row['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['away_sponsor']<>"")                echo  $row['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>

 

 

I have tried every way that I can think of to get the 'IF's and the 'strtotime's to work with this but I cannot work out how to put these into an already running echo (that is clearly not the right terminology but hopefully you get what I mean!) and get it to parse correctly.

 

Obviously the <?php ?> tags are superfluous as they are already opened in the code you gave but not sure if this can even be done. I have tried concatenating them

 

'. echo date('jS F Y',strtotime($row['date_of_birth'])) .'

 

 

'. if ($row['home_sponsor']<>"")               echo  $row['home_sponsor'];                 else	               echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a> .'

 

but that obviously did not work and I have tried enclosing them in all the { }'s and [ ]'s I can find on my keyboard!

 

Is there actually away to make these work or does anyone know a better way to try it?

 

The whole code on the page, which works except for the IF's and the date formats is

 

<?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 '<div class="Title_Lt_Blue_Bg">' . strtoupper($row['position']) . '</div>';                                $last_player_postion_id = $row['player_position_id'];            }                        echo '<table width="590" border="0" align="center" cellspacing="0">                <tr>          <td colspan="2" class="opposition_name">' . $row['surname'] . ', ' . $row['firstname'] . '</td><td width="78" rowspan="3" valign="top" class="Normal_Table_Column"><img src="'.  $row['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 />'. $row['date_of_birth'] .'<br />            <strong>JOINED<br />            </strong>'. $row['date_joined'] .'</td>          <td width="309" align="left" ><strong>HOME SPONSOR<br />            </strong>' . $row['home_sponsor'] .' <br /><strong>AWAY SPONSOR<br />            </strong>'. $row['away_sponsor'] .'<br /></td>        </tr>        <tr>          <td height="19" align="right" class="Normal_Table_Column"><a href="squad_details.php?recordID='. $row['player_id'] .'"> See '. $row['firstname'] .'\'s Profile</a>   </td>        </tr>             </table>';        }    }    else    {        echo 'No results!';    }} ?>

 

Thanks in advance

Steve

[wildteen88]

To concatenate the date into your string you'd do something like this

echo 'your existing string here
....
<strong>DATE OF BIRTH: </strong><br />'. date('jS F Y',strtotime($row['date_of_birth']))  .'<br />
....
the rest of the string';

 

For the if statement you'll want to end the current echo statement, then do your if/else statement outputting whatever it is and then start another echo statement.

echo ' your existing string here
....
<strong>HOME SPONSOR</strong><br />'; // end the current echo statement

// now do the if/else statement
if ($row['home_sponsor'] <> "")
    echo  $row['home_sponsor'];
else	
    echo '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>';

// now start another echo statement.
echo '.....
the rest of your string';

 

Or you can use a  ternary operator (an inline if/else statement)

 

echo ' your existing string here 
....
<strong>HOME SPONSOR</strong><br />' . 
    ( ($row['home_sponsor'] <> "") ? $row['home_sponsor'] : '<a href="http://www.margate-fc.com/content/commercial/sponsorship/shirt_sponsors.php">AVAILABLE - Click here for info</a>' ) 
  . 
'
.....
the rest of your string';

 

 

[MargateSteve]

Excellent advice as always!

 

I had not come across  ternary operator's before but have added them to my 'read up' list, but the other two suggestions worked a treat.

In my attempts I had come frustratingly close to getting them right (just a superfluous echo and a couple of missing ;'s between the two)  so I am beginning to understand it all. Slowly!

 

Thanks again

Steve