Jump to content

Converting timestamp to varchar


AS147

Recommended Posts

I am not a developer and am learning so forgive lack of what may be obvious knowledge to you folks.

 

Our hosting provider has upgraded our version of SQL 5.7.7 which sets the default SQL mode to strict. As strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE we now have an error with some of our old PHP code. 

We are trying to insert a timestamp as follows

$insertSQL = sprintf("INSERT INTO tournaments (tourn_id, tourn_name, tourn_year, site_visible, tourn_type, ranking_type, tourn_class, how_seed, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['tourn_id'], "int"),
                       GetSQLValueString($_POST['tourn_name'], "text"),
                       GetSQLValueString($_POST['tourn_year'], "date"),
                       GetSQLValueString($_POST['site_visible'], "text"),
                       GetSQLValueString($_POST['tourn_type'], "text"),
                       GetSQLValueString($_POST['ranking_type'], "text"),
                       GetSQLValueString($_POST['tourn_class'], "text"),
                       GetSQLValueString($_POST['how_seed'], "text"),
                       GetSQLValueString($_POST['status'], "text"));

But we now get an error stating "Column tourn_year cannot be null"

The hosting provider has stated that since MySQL version 5.7.7 the default sql_mode is strict a value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. As specified in https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

They have also said that the value passed for tourn_year is now not recognized as correct due to MySQL's strict mode being active. At the moment, this row is the only one set with timestamp while all other ones are using varchar

I am wondering what is the easiest least impactful way to fix this.

Do I change to varchar (see image) or change the code?

Thanks for your help in advance

 

 

 

Screenshot 2021-03-02 224223.jpg

Link to comment
Share on other sites

There may be a problem with your schema. The column name implies "year" but the type is time stamp. What do you really want to go there? The schema also says that column cannot be null so that is why you are getting the error. The implication is '$_POST['tourn_year']' is null. You should be doing data validation first.

The first question is what does that column really represent, a timestamp or just a year (type int). Where does that $_POST value come from? If it is a user entry then you need to format it into a timestamp as the user will certainly not enter a valid one. If it is automatically entered by Javascript on the form then you need to check that form to see why that value is not provided.

Link to comment
Share on other sites

Hi, what we get when we run the php code is the year (2021) that is auto populated in the form and not editable. I have attached an image of the form and the full php code below.

Apologies but I am not a developer and just starting to learn PHP and this is not my code so any help is much appreciated.

 

<?php require_once('../../Connections/connvbsa.php'); ?>
<?php
if (!isset($_SESSION)) {
  session_start();
}
$MM_authorizedUsers = "Webmaster,Treasurer";
$MM_donotCheckaccess = "false";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) { 
  // For security, start by assuming the visitor is NOT authorized. 
  $isValid = False; 

  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username. 
  // Therefore, we know that a user is NOT logged in if that Session variable is blank. 
  if (!empty($UserName)) { 
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login. 
    // Parse the strings into arrays. 
    $arrUsers = Explode(",", $strUsers); 
    $arrGroups = Explode(",", $strGroups); 
    if (in_array($UserName, $arrUsers)) { 
      $isValid = true; 
    } 
    // Or, you may restrict access to only certain users based on their username. 
    if (in_array($UserGroup, $arrGroups)) { 
      $isValid = true; 
    } 
    if (($strUsers == "") && false) { 
      $isValid = true; 
    } 
  } 
  return $isValid; 
}

$MM_restrictGoTo = "../../page_error.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {   
  $MM_qsChar = "?";
  $MM_referrer = $_SERVER['PHP_SELF'];
  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
  if (isset($_SERVER['QUERY_STRING']) && strlen($_SERVER['QUERY_STRING']) > 0) 
  $MM_referrer .= "?" . $_SERVER['QUERY_STRING'];
  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
  header("Location: ". $MM_restrictGoTo); 
  exit;
}
?>
<?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;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO tournaments (tourn_id, tourn_name, tourn_year, site_visible, tourn_type, ranking_type, tourn_class, how_seed, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['tourn_id'], "int"),
                       GetSQLValueString($_POST['tourn_name'], "text"),
                       GetSQLValueString($_POST['tourn_year'], "date"),
                       GetSQLValueString($_POST['site_visible'], "text"),
                       GetSQLValueString($_POST['tourn_type'], "text"),
                       GetSQLValueString($_POST['ranking_type'], "text"),
                       GetSQLValueString($_POST['tourn_class'], "text"),
                       GetSQLValueString($_POST['how_seed'], "text"),
                       GetSQLValueString($_POST['status'], "text"));

  mysql_select_db($database_connvbsa, $connvbsa);
  $Result1 = mysql_query($insertSQL, $connvbsa) or die(mysql_error());

  $insertGoTo = "../aa_tourn_index.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="googlebot" content="noarchive,noindex,nofollow,nosnippet" />
<meta name="robots" content="noarchive,noindex,nofollow" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>VBSA Administration</title>
<script type="text/javascript" src="../../Scripts/AC_RunActiveContent.js"></script>
<script type="text/javascript" src="../../Scripts/datepicker.js"></script>

<link href="../../Admin_xx_CSS/vbsa_DB.css" rel="stylesheet" type="text/css" />
<link href="../../Admin_xx_CSS/VBSA_db_links.css" rel="stylesheet" type="text/css" />
<link href="../../Admin_xx_CSS/VBSA_Scores_Index.css" rel="stylesheet" type="text/css" /> 
<link href="../../Admin_xx_CSS/Datepicker.css" rel="stylesheet" type="text/css" />
<link href="../../Admin_DB_VBSA/php_mail_merge/php_mail_merge.css" rel="stylesheet" type="text/css" />
</head>

<body>
<?php include '../../admin_xx_includes/db_nav.php';?>
<?php include '../../admin_xx_includes/db_srch_treas.php';?>
<table width="800" align="center">
  <tr>
    <td colspan="2">&nbsp;</td>
  </tr>
  <tr>
    <td align="center" class="red_bold">Insert a new Tournament</td>
    <td align="center"><input type="button" value="Return to previous page" onclick="history.go(-1)"/></td>
  </tr>
</table>
    <form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
      <table align="center" cellpadding="5" cellspacing="5">
        <tr valign="baseline">
          <td align="right" nowrap="nowrap" class="red_text">Tournament ID is auto generated</td>
          <td>&nbsp;</td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Please create a name for this tournament:</td>
          <td><input type="text" name="tourn_name" value="" size="60" /></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Please Select a Class for this tournament:</td>
          <td><select name="tourn_class">
            <option value="Aust Rank" <?php if (!(strcmp("Aust Rank", ""))) {echo "SELECTED";} ?>>Aust Rank</option>
            <option value="Victorian" <?php if (!(strcmp("Victorian", ""))) {echo "SELECTED";} ?>>Victorian</option>
            <option value="Junior" <?php if (!(strcmp("Junior", ""))) {echo "SELECTED";} ?>>Junior</option>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Does this tournament attract ranking points?</td>
          <td><select name="ranking_type">
            <option value="No Entry" selected="selected" <?php if (!(strcmp("", ""))) {echo "SELECTED";} ?>>No Entry</option>
            <option value="Vic Rank" <?php if (!(strcmp("Vic Rank", ""))) {echo "SELECTED";} ?>>Vic Rank</option>
            <option value="Womens Rank" <?php if (!(strcmp("Womens Rank", ""))) {echo "SELECTED";} ?>>Womens Rank</option>
            <option value="Junior Rank" <?php if (!(strcmp("Junior Rank", ""))) {echo "SELECTED";} ?>>Junior Rank</option>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td colspan="2" align="center" class="red_text">"No Entry" for tournaments that do not attract Victorian Ranking points. <br />
            "Vic Rank" for tournaments that DO attract Victorian Ranking points. <br />
            "Junior Rank" for tournaments that attract Victorian JUNIOR Ranking points. <br />
            "Womens Rank" for tournaments that DO attract Victorian WOMENS Ranking points. <br />
           </td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">How is this tournament seeded?</td>
          <td><select name="how_seed">
          <option value="NA" <?php if (!(strcmp("NA", ""))) {echo "SELECTED";} ?>>Not Applicable</option>
            <option value="Aust Rankings" <?php if (!(strcmp("Aust Rankings", ""))) {echo "SELECTED";} ?>>Aust Rankings</option>
            <option value="Vic Rankings" <?php if (!(strcmp("Vic Rankings", ""))) {echo "SELECTED";} ?>>Victorian Rankings</option>
            <option value="Aust Womens Rankings" <?php if (!(strcmp("Aust Womens Rankings", ""))) {echo "SELECTED";} ?>>Aust Womens Rankings</option>
            <option value="Vic Womens Rankings" <?php if (!(strcmp("Vic WomensRankings", ""))) {echo "SELECTED";} ?>>Victorian Womens Rankings</option>
            <option value="Junior Rankings" <?php if (!(strcmp("Junior Rankings", ""))) {echo "SELECTED";} ?>>Junior Rankings</option>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Do you want this tournament to be visible on the website: </td>
          <td><select name="site_visible">
            <option value="Yes" selected="selected" <?php if (!(strcmp("Yes", ""))) {echo "SELECTED";} ?>>Yes</option>
            <option value="No" <?php if (!(strcmp("Yes", ""))) {echo "SELECTED";} ?>>No</option>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Tournament Type: </td>
          <td><select name="tourn_type">
            <option value="Snooker" selected="selected" <?php if (!(strcmp("Snooker", ""))) {echo "SELECTED";} ?>>Snooker</option>
            <option value="Billiards" <?php if (!(strcmp("Billiards", ""))) {echo "SELECTED";} ?>>Billiards</option>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">The Year for this tournament is auto inserted for current year:</td>
          <td><?php echo date("Y"); ?></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">&nbsp;</td>
          <td><input type="submit" value="Insert Tournament" /></td>
        </tr>
      </table>
      <input type="hidden" name="tourn_id" value="" />
      <input type="hidden" name="tourn_year" value="" />
      <input type="hidden" name="status" value="Open" />
      <input type="hidden" name="MM_insert" value="form1" />
</form>
    <p>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>

 

Tourn_insert.jpg

Link to comment
Share on other sites

The purpose of a column defined as TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP is to record the datetime a record was added to the table. If that is its purpose you shouldn't be inserting data into that column in your insert query.

If its purpose is other than that, such as recording a tournament date, then it should be redefined (probably as DATETIME or DATE).

Link to comment
Share on other sites

If it is auto-populated with just the year then that column should be int not timestamp. However, there is probably no reason for it to be populated by the form at all. Set the year in PHP and use that same value on the form and updating the database.

Link to comment
Share on other sites

Thanks folks. Its only purpose is to record the year of the tournament. We maintain a history of all tournaments by the year of the tournament. 

The consensus from both your replies is that the value of that column should not be timestamp.   

I like the idea that we don't insert data into that column in the insert query as it sounds like the simplest approach (delete the insert of that variable).

Under this set up can you confirm what column should be defined to so we have the ability to display the year in the form?

 

I also like the idea of setting the year in PHP and use that value on the form to update the database

Under this set up can you confirm what column should be defined to so we have the ability to display the year in the form and what the PHP code would be to set the year please.

 

Again apologies but I am a newbie to PHP so this experiential learning is a very good start. Also if you can suggest a good beginners guide to learning PHP I would appreciate it. When I look at PHP I am currently guessing what the code is doing. 

 

Here is the code that allows editing of any historic tournament (I noticed the tourn_year' variable is handled differently!?

<?php require_once('../../Connections/connvbsa.php'); ?>
<?php
if (!isset($_SESSION)) {
  session_start();
}
$MM_authorizedUsers = "Webmaster,Treasurer";
$MM_donotCheckaccess = "false";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) { 
  // For security, start by assuming the visitor is NOT authorized. 
  $isValid = False; 

  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username. 
  // Therefore, we know that a user is NOT logged in if that Session variable is blank. 
  if (!empty($UserName)) { 
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login. 
    // Parse the strings into arrays. 
    $arrUsers = Explode(",", $strUsers); 
    $arrGroups = Explode(",", $strGroups); 
    if (in_array($UserName, $arrUsers)) { 
      $isValid = true; 
    } 
    // Or, you may restrict access to only certain users based on their username. 
    if (in_array($UserGroup, $arrGroups)) { 
      $isValid = true; 
    } 
    if (($strUsers == "") && false) { 
      $isValid = true; 
    } 
  } 
  return $isValid; 
}

$MM_restrictGoTo = "../../page_error.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {   
  $MM_qsChar = "?";
  $MM_referrer = $_SERVER['PHP_SELF'];
  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
  if (isset($_SERVER['QUERY_STRING']) && strlen($_SERVER['QUERY_STRING']) > 0) 
  $MM_referrer .= "?" . $_SERVER['QUERY_STRING'];
  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
  header("Location: ". $MM_restrictGoTo); 
  exit;
}
?>
<?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;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}


if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE tournaments SET tourn_name=%s, tourn_year=%s, site_visible=%s, tourn_type=%s, ranking_type=%s, tourn_class=%s, how_seed=%s, status=%s WHERE tourn_id=%s",
                       GetSQLValueString($_POST['tourn_name'], "text"),
                       GetSQLValueString($_POST['tourn_year'], "date"),
                       GetSQLValueString($_POST['site_visible'], "text"),
                       GetSQLValueString($_POST['tourn_type'], "text"),
                       GetSQLValueString($_POST['ranking_type'], "text"),
                       GetSQLValueString($_POST['tourn_class'], "text"),
                       GetSQLValueString($_POST['how_seed'], "text"),
                       GetSQLValueString($_POST['status'], "text"),
                       GetSQLValueString($_POST['tourn_id'], "int"));

  mysql_select_db($database_connvbsa, $connvbsa);
  $Result1 = mysql_query($updateSQL, $connvbsa) or die(mysql_error());

    $updateGoTo = $_SESSION['tourn_page'];
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}

if (isset($_GET['tourn_id'])) {
  $tourn_id = $_GET['tourn_id'];
}

mysql_select_db($database_connvbsa, $connvbsa);
$query_tourn1 = "SELECT * FROM tournaments WHERE tourn_id = '$tourn_id'";
$tourn1 = mysql_query($query_tourn1, $connvbsa) or die(mysql_error());
$row_tourn1 = mysql_fetch_assoc($tourn1);
$totalRows_tourn1 = mysql_num_rows($tourn1);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="googlebot" content="noarchive,noindex,nofollow,nosnippet" />
<meta name="robots" content="noarchive,noindex,nofollow" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>VBSA Administration</title>
<meta name="googlebot" content="noarchive,noindex,nofollow,nosnippet" />
<meta name="robots" content="noarchive,noindex,nofollow" />


<script type="text/javascript" src="../../Scripts/AC_RunActiveContent.js"></script>
<script type="text/javascript" src="../../Scripts/datepicker.js"></script>

<link href="../../Admin_xx_CSS/vbsa_DB.css" rel="stylesheet" type="text/css" />
<link href="../../Admin_xx_CSS/VBSA_db_links.css" rel="stylesheet" type="text/css" />
<link href="../../Admin_xx_CSS/VBSA_Scores_Index.css" rel="stylesheet" type="text/css" /> 
<link href="../../Admin_xx_CSS/Datepicker.css" rel="stylesheet" type="text/css" />

</head>

<body>

<table width="1000" border="0" align="center" cellpadding="2" cellspacing="2" class="greenbg">
  <tr>
    <td><img src="../../Admin_Images/Admin_Header.jpg" alt="" width="1000" height="140" /></td>
  </tr>
</table>

<table width="800" align="center">
  <tr>
    <td colspan="2">&nbsp;<?php echo $_SESSION['tourn_page']; ?></td>
  </tr>
  <tr>
    <td width="772" align="center" class="red_bold">Edit selected tournament</td>
    <td width="216" align="center"><input type="button" value="Return to previous page" onclick="history.go(-1)"/></td>
  </tr>
</table>
    <form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1" > 
      <table align="center" cellpadding="5" cellspacing="5">
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Tournament ID: </td>
          <td><?php echo $row_tourn1['tourn_id']; ?></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Tournament Name:</td>
          <td><input type="text" name="tourn_name" value="<?php echo htmlentities($row_tourn1['tourn_name'], ENT_COMPAT, 'utf-8'); ?>" size="60" /></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Tournament Class:</td>
          <td><select name="tourn_class">
            <option value="Aust Rank" <?php if (!(strcmp("Aust Rank", htmlentities($row_tourn1['tourn_class'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Aust Rank</option>
            <option value="Victorian" <?php if (!(strcmp("Victorian", htmlentities($row_tourn1['tourn_class'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Victorian</option>
            <option value="Junior" <?php if (!(strcmp("Junior", htmlentities($row_tourn1['tourn_class'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Junior</option>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">How is this tournament seeded?</td>
          <td><select name="how_seed">
          <option value="NA" <?php if (!(strcmp("NA", htmlentities($row_tourn1['how_seed'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Not Applicable</option>
          <option value="Aust Rankings" <?php if (!(strcmp("Aust Rankings", htmlentities($row_tourn1['how_seed'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Aust Rankings</option>
          <option value="Vic Rankings" <?php if (!(strcmp("Vic Rankings", htmlentities($row_tourn1['how_seed'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Victorian Rankings</option>
          <option value="Aust Womens Rankings" <?php if (!(strcmp("Aust Womens Rankings", htmlentities($row_tourn1['how_seed'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Aust Womens Rankings</option>
          <option value="Vic Womens Rankings" <?php if (!(strcmp("Vic Womens Rankings", htmlentities($row_tourn1['how_seed'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Vic Womens Rankings</option>
          <option value="Junior Rankings" <?php if (!(strcmp("Junior Rankings", htmlentities($row_tourn1['how_seed'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Junior Rankings</option>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Does this tournament attract ranking points in Victoria?</td>
          <td><select name="ranking_type">
          <option value="No Entry" <?php if (!(strcmp("No Entry", htmlentities($row_tourn1['ranking_type'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>No Entry</option>
            <option value="Vic Rank" <?php if (!(strcmp("Vic Rank", htmlentities($row_tourn1['ranking_type'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Vic Rank</option>
            <option value="Womens Rank" <?php if (!(strcmp("Womens Rank", htmlentities($row_tourn1['ranking_type'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Womens Rank</option>
            <option value="Junior Rank" <?php if (!(strcmp("Junior Rank", htmlentities($row_tourn1['ranking_type'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Junior Rank</option>
          </select> 
            Select type</td>
        </tr>
        <tr valign="baseline">
          <td colspan="2" align="center" class="red_text">"No Entry" for tournaments that do not attract Victorian Ranking points. <br />
            "Vic Rank" for tournaments that DO attract Victorian Ranking points. <br />
            "Junior Rank" for tournaments that attract Victorian JUNIOR Ranking points. <br />
            "Womens Rank" for tournaments that DO attract Victorian WOMENS Ranking points. <br />
           </td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Tournament Year:</td>
          <td><?php $newDate = date("Y", strtotime($row_tourn1['tourn_year'])); echo $newDate; ?> (cannot be edited)</td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Do you want this tournament to be visible on the website: </td>
          <td><select name="site_visible">
            <option value="Yes" <?php if (!(strcmp("Yes", htmlentities($row_tourn1['site_visible'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Yes</option>
            <option value="No" <?php if (!(strcmp("No", htmlentities($row_tourn1['site_visible'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>No</option>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Tournament Type: </td>
          <td><select name="tourn_type">
            <option value="Snooker" <?php if (!(strcmp("Snooker", htmlentities($row_tourn1['tourn_type'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Snooker</option>
            <option value="Billiards" <?php if (!(strcmp("Billiards", htmlentities($row_tourn1['tourn_type'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Billiards</option>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Entries:</td>
          <td><select name="status">
            <option value="Open" <?php if (!(strcmp("Open", htmlentities($row_tourn1['status'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Open</option>
            <option value="Closed" <?php if (!(strcmp("Closed", htmlentities($row_tourn1['status'], ENT_COMPAT, 'utf-8')))) {echo "SELECTED";} ?>>Closed</option>
          </select></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">&nbsp;</td>
          <td><input type="submit" value="Update Tournament" /></td>
        </tr>
      </table>
      <input type="hidden" name="tourn_id" value="<?php echo $row_tourn1['tourn_id']; ?>" />
      <input type="hidden" name="tourn_year" value="<?php echo htmlentities($row_tourn1['tourn_year'], ENT_COMPAT, 'utf-8'); ?>" />
      <input type="hidden" name="MM_update" value="form1" />
      <input type="hidden" name="tourn_id" value="<?php echo $row_tourn1['tourn_id']; ?>" />
</form>
    <p>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>

Here is some other code that shows the tournament history

 

<?php require_once('../Connections/connvbsa.php'); ?>
<?php

if (!isset($_SESSION)) {
  session_start();
}

$tourn_page = "http://www.vbsa.org.au/Admin_Tournaments/aa_tourn_index_history.php";
$_SESSION['tourn_page'] = $tourn_page;

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

if (isset($_GET['tourn_year'])) {
  $tourn_year = $_GET['tourn_year'];
}

mysql_select_db($database_connvbsa, $connvbsa);
$query_tourn1 = "SELECT * FROM tournaments WHERE YEAR(tourn_year) = '$tourn_year' AND tourn_type='Snooker' ORDER BY tournaments.tourn_name";
$tourn1 = mysql_query($query_tourn1, $connvbsa) or die(mysql_error());
$row_tourn1 = mysql_fetch_assoc($tourn1);
$totalRows_tourn1 = mysql_num_rows($tourn1);

mysql_select_db($database_connvbsa, $connvbsa);
$query_tourn2 = "SELECT * FROM tournaments WHERE YEAR(tourn_year) = '$tourn_year' AND tourn_type='Billiards' ORDER BY tournaments.tourn_name";
$tourn2 = mysql_query($query_tourn2, $connvbsa) or die(mysql_error());
$row_tourn2 = mysql_fetch_assoc($tourn2);
$totalRows_tourn2 = mysql_num_rows($tourn2);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="googlebot" content="noarchive,noindex,nofollow,nosnippet" />
<meta name="robots" content="noarchive,noindex,nofollow" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>VBSA Administration</title>
<meta name="googlebot" content="noarchive,noindex,nofollow,nosnippet" />
<meta name="robots" content="noarchive,noindex,nofollow" />


<script type="text/javascript" src="../Scripts/AC_RunActiveContent.js"></script>
<script type="text/javascript" src="../Scripts/datepicker.js"></script>

<link href="../Admin_xx_CSS/vbsa_DB.css" rel="stylesheet" type="text/css" />
<link href="../Admin_xx_CSS/VBSA_db_links.css" rel="stylesheet" type="text/css" />
<link href="../Admin_xx_CSS/Datepicker.css" rel="stylesheet" type="text/css" />

</head>

<body>
<?php include '../admin_xx_includes/db_nav.php';?>
<?php include '../admin_xx_includes/db_srch_treas.php';?>
<table width="600" align="center">
  <tr>
    <td colspan="2" class="red_bold">&nbsp;</td>
  </tr>
  <tr>
    <td class="red_bold">  <?php $newDate = date("Y", strtotime($row_tourn1['tourn_year'])); echo $newDate; ?>
    Tournament History</td>
    <td align="right" class="greenbg"><a href="aa_tourn_index.php">Return to Tournaments</a></td>
  </tr>
</table>
    <table align="center" cellpadding="5" cellspacing="5">
      <tr>
        <td colspan="13" align="center" class="red_bold" ><?php $newDate = date("Y", strtotime($row_tourn1['tourn_year'])); echo $newDate; ?> SNOOKER TOURNAMENTS</td>
      </tr>
      <tr>
        <td align="center">Tourn ID</td>
        <td align="left">Tournament Name</td>
        <td align="left">Year</td>
        <td align="left">Class</td>
        <td>Draw Type</td>
        <td align="center">View on site</td>
        <td>Type</td>
        <td align="center" nowrap="nowrap">Vic rank type</td>
        <td>Entries</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
      <?php do { ?>
        <tr>
          <td align="center"><?php echo $row_tourn1['tourn_id']; ?></td>
          <td align="left"><?php echo $row_tourn1['tourn_name']; ?></td>
          <td align="left"><?php $newDate = date("Y", strtotime($row_tourn1['tourn_year'])); echo $newDate; ?></td>
          <td align="left"><?php echo $row_tourn1['tourn_class']; ?></td>
          <td><?php echo $row_tourn1['tourn_draw']; ?></td>
          <td align="center"><?php echo $row_tourn1['site_visible']; ?></td>
          <td><?php echo $row_tourn1['tourn_type']; ?></td>
          <td align="center"><?php echo $row_tourn1['ranking_type']; ?></td>
          <td><?php echo $row_tourn1['status']; ?></td>
          <td><a href="tournament_detail.php?tourn_id=<?php echo $row_tourn1['tourn_id']; ?>&tourn_year=<?php echo $tourn_year; ?>"><img src="../Admin_Images/detail.fw.png" width="20" height="20" title="View all entries" /></a></td>
          <td><a href="user_files/tournament_edit.php?tourn_id=<?php echo $row_tourn1['tourn_id']; ?>&tourn_year=<?php echo $tourn_year; ?>"><img src="../Admin_Images/edit_butt.png" width="20" height="20" title="Edit tournament details" /></a></td>
          <td class="page"><a href="user_files/player_edit_all.php?tourn_id=<?php echo $row_tourn1['tourn_id']; ?>&tourn_year=<?php echo $tourn_year; ?>">Edit All</a></td>
          <td><a href="x_fin_rep.php?tourn_id=<?php echo $row_tourn1['tourn_id']; ?>&tourn_year=<?php echo $tourn_year; ?>"><img src="../Admin_Images/fin_butt.png" width="20" height="20" title="View tournament financials" /></a></td>
        </tr>
        <?php } while ($row_tourn1 = mysql_fetch_assoc($tourn1)); ?>
    </table>
<table align="center" cellpadding="5" cellspacing="5">
      <tr>
        <td colspan="13" align="center" class="red_bold" ><?php $newDate = date("Y", strtotime($row_tourn2['tourn_year'])); echo $newDate; ?> BILLIARDS TOURNAMENTS</td>
      </tr>
      <tr>
        <td align="center">Tourn ID</td>
        <td align="left">Tournament Name</td>
        <td align="left">Year</td>
        <td align="left">Class</td>
        <td>Draw Type</td>
        <td align="center">View on site</td>
        <td>Type</td>
        <td align="center" nowrap="nowrap">Vic rank type</td>
        <td>Entries</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
      <?php do { ?>
      <tr>
        <td align="center"><?php echo $row_tourn2['tourn_id']; ?></td>
        <td align="left"><?php echo $row_tourn2['tourn_name']; ?></td>
        <td align="left"><?php $newDate = date("Y", strtotime($row_tourn2['tourn_year'])); echo $newDate; ?></td>
        <td align="left"><?php echo $row_tourn2['tourn_class']; ?></td>
        <td><?php echo $row_tourn2['tourn_draw']; ?></td>
        <td align="center"><?php echo $row_tourn2['site_visible']; ?></td>
        <td><?php echo $row_tourn2['tourn_type']; ?></td>
        <td align="center"><?php echo $row_tourn2['ranking_type']; ?></td>
        <td><?php echo $row_tourn2['status']; ?></td>
        <td><a href="tournament_detail.php?tourn_id=<?php echo $row_tourn2['tourn_id']; ?>&tourn_year=<?php echo $tourn_year; ?>"><img src="../Admin_Images/detail.fw.png" width="20" height="20" title="View all entries" /></a></td>
        <td><a href="user_files/tournament_edit.php?tourn_id=<?php echo $row_tourn2['tourn_id']; ?>&tourn_year=<?php echo $tourn_year; ?>"><img src="../Admin_Images/edit_butt.png" width="20" height="20" title="Edit tournament details" /></a></td>
        <td class="page"><a href="user_files/player_edit_all.php?tourn_id=<?php echo $row_tourn2['tourn_id']; ?>&tourn_year=<?php echo $tourn_year; ?>">Edit All</a></td>
        <td><a href="x_fin_rep.php?tourn_id=<?php echo $row_tourn2['tourn_id']; ?>&tourn_year=<?php echo $tourn_year; ?>"><img src="../Admin_Images/fin_butt.png" width="20" height="20" title="View tournament financials" /></a></td>
      </tr>
      <?php } while ($row_tourn2 = mysql_fetch_assoc($tourn2)); ?> 
</table>
<p>&nbsp;</p>
</body>
</html>

Link to comment
Share on other sites

When I try to define the column as YEAR I am not sure what to set the default value to as the only options are

  • NULL -  NULL would not be permitted in STRICT SQL mode
  • AS DEFINED - which I assume would then then need me to insert this via the PHP form
  • NONE - wouldn't produce the result of populating the current year
  • CURRENT_TIMESTAMP (what we have at the moment) - fails

Is there a way for the column in Strict mode to auto populate with the year as it did previously? 

 

Screenshot 2021-03-06 141552.jpg

Link to comment
Share on other sites

try

`tourn_year` year(4) NOT NULL DEFAULT '0000',

then when inserting records, always write CURDATE() to the tourn_year column.

INSERT INTO tournament (tournament_name, tourn_year) VALUES ( ?, CURDATE() );

E.G.

mysql> INSERT INTO tournament (tournament_name, tourn_year) VALUES ('Wimbledon', CURDATE());
Query OK, 1 row affected (0.05 sec)

mysql> select * from tournament;
+---------------+-----------------+------------+
| tournament_id | tournament_name | tourn_year |
+---------------+-----------------+------------+
|             1 | Wimbledon       |       2021 |
+---------------+-----------------+------------+
1 row in set (0.01 sec)

 

Link to comment
Share on other sites

Thanks, does this look correct?

I get "Parse error: syntax error, unexpected T_STRING in /home/customer/www/vbsa.org.au/public_html/Admin_Tournaments/user_files/tournament_insert.php on line 84" 

Line 84 is the first line in bold below 

`tourn_year` year(4) NOT NULL DEFAULT '0000',

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO tournaments (tourn_id, tourn_name, tourn_year, site_visible, tourn_type, ranking_type, tourn_class, how_seed, status) VALUES (%s, %s, CURDATE(), %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['tourn_id'], "int"),
                       GetSQLValueString($_POST['tourn_name'], "text"),
                       GetSQLValueString($_POST['tourn_year'], "date"),
                       GetSQLValueString($_POST['site_visible'], "text"),
                       GetSQLValueString($_POST['tourn_type'], "text"),
                       GetSQLValueString($_POST['ranking_type'], "text"),
                       GetSQLValueString($_POST['tourn_class'], "text"),
                       GetSQLValueString($_POST['how_seed'], "text"),
                       GetSQLValueString($_POST['status'], "text"));

Link to comment
Share on other sites

Sorry I realized that after I re read it. I set the values in the column as stated `tourn_year` year(4) NOT NULL DEFAULT '0000',

Then made this change to the code (below) but get this error "Column 'site_visible' cannot be null"

Site_Visible defines whether the event appears on the website. The value is either 'Yes' or 'No' and set from the drop down on the form 


if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO tournaments (tourn_id, tourn_name, tourn_year,  site_visible , tourn_type, ranking_type, tourn_class, how_seed, status) VALUES (%s, %s, CURDATE(), %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['tourn_id'], "int"),
                       GetSQLValueString($_POST['tourn_name'], "text"),
                       GetSQLValueString($_POST['tourn_year'], "date"),
                       GetSQLValueString($_POST['site_visible'], "text"),
                       GetSQLValueString($_POST['tourn_type'], "text"),
                       GetSQLValueString($_POST['ranking_type'], "text"),
                       GetSQLValueString($_POST['tourn_class'], "text"),
                       GetSQLValueString($_POST['how_seed'], "text"),
                       GetSQLValueString($_POST['status'], "text"));

image.png

Screenshot 2021-03-06 212647.jpg

Edited by AS147
Link to comment
Share on other sites

Lastly, it appears the value for the tourn_year is used to display the list of tournaments. The display of the tournaments depends on being in a format YYYY-MM-DD HH:MM:SS because when I enter just the year with the new column definition recommended the tournaments do not appear in the list. When I set the column definition back to TIMESTAMP CURRENT_TIMESTAMP and enter YYYY-MM-DD HH:MM:SS in a tournament record the tournament appears in the list of tournaments again.

 

The code that displays the tournaments is below and think with the changes you recommended in the insert_tournament we need to make a similar change needed is here but I am not sure what the change is. Appreciating your help. 

mysql_select_db($database_connvbsa, $connvbsa);
$query_tourn1 = "SELECT * FROM tournaments WHERE YEAR(tourn_year) = YEAR(CURDATE()) AND tourn_type='Snooker' AND status='Open' ORDER BY tournaments.tourn_name";
$tourn1 = mysql_query($query_tourn1, $connvbsa) or die(mysql_error());
$row_tourn1 = mysql_fetch_assoc($tourn1);
$totalRows_tourn1 = mysql_num_rows($tourn1);

Link to comment
Share on other sites

22 minutes ago, Barand said:

use



... WHERE tourn_year = YEAR(CURDATE()) ...

 

I managed to find this out by trying myself also so thanks. I am learning !🙂

 

There is one more piece of code that lists the tournaments, this page allows you to edit and add entrants and is slightly different code (below) 

When I change WHERE YEAR(tourn_year) = YEAR(CURDATE()) to WHERE tourn_year = YEAR(CURDATE()) 

I get this error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY tournaments.tourn_type DESC, tournaments.tourn_name' at line 1"

 

Is the code for Tyear causing the issue ?

 

mysql_select_db($database_connvbsa, $connvbsa);

$query_tyear = "SELECT tourn_year AS Tyear FROM tournaments WHERE tourn_year <> YEAR( CURDATE( ) ) GROUP BY Tyear ORDER BY Tyear DESC";

$tyear = mysql_query($query_tyear, $connvbsa) or die(mysql_error());

$row_tyear = mysql_fetch_assoc($tyear);

$totalRows_tyear = mysql_num_rows($tyear);

 

mysql_select_db($database_connvbsa, $connvbsa);
$query_tourn1 = "SELECT * FROM tournaments WHERE YEAR(tourn_year) = YEAR(CURDATE()) AND tourn_type='Snooker' AND status='Open' ORDER BY tournaments.tourn_name";
$tourn1 = mysql_query($query_tourn1, $connvbsa) or die(mysql_error());
$row_tourn1 = mysql_fetch_assoc($tourn1);
$totalRows_tourn1 = mysql_num_rows($tourn1);

mysql_select_db($database_connvbsa, $connvbsa);
$query_tourn2 = "SELECT * FROM tournaments WHERE YEAR(tourn_year) = YEAR(CURDATE()) AND tourn_type='Billiards' AND status='Open' ORDER BY tournaments.tourn_name";
$tourn2 = mysql_query($query_tourn2, $connvbsa) or die(mysql_error());
$row_tourn2 = mysql_fetch_assoc($tourn2);
$totalRows_tourn2 = mysql_num_rows($tourn2);

mysql_select_db($database_connvbsa, $connvbsa);
$query_tyear = "SELECT date_format( tourn_year, '%Y') AS Tyear FROM tournaments WHERE YEAR(tourn_year) <> YEAR( CURDATE( ) ) GROUP BY Tyear ORDER BY Tyear DESC";
$tyear = mysql_query($query_tyear, $connvbsa) or die(mysql_error());
$row_tyear = mysql_fetch_assoc($tyear);
$totalRows_tyear = mysql_num_rows($tyear);

mysql_select_db($database_connvbsa, $connvbsa);
$query_tourn_closed = "SELECT * FROM tournaments WHERE (YEAR(tourn_year) = YEAR(CURDATE()) AND status='Closed') ORDER BY tournaments.tourn_type DESC, tournaments.tourn_name ";
$tourn_closed = mysql_query($query_tourn_closed, $connvbsa) or die(mysql_error());
$row_tourn_closed = mysql_fetch_assoc($tourn_closed);
$totalRows_tourn_closed = mysql_num_rows($tourn_closed);

Edited by AS147
Link to comment
Share on other sites

Your tourn_year is now just a YEAR. The is no need to use any datetime functions to reformat or extract the year (in fact they will probably return NULL as it is no longer a date or datetime type)

mysql> SELECT YEAR(tourn_year) as tyear FROM tournament;
+-------+
| tyear |
+-------+
|  NULL |
+-------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT date_format(tourn_year, '%Y') as tyear FROM tournament;
+-------+
| tyear |
+-------+
| NULL  |
+-------+
1 row in set, 1 warning (0.00 sec)

 

Link to comment
Share on other sites

26 minutes ago, Barand said:

Have you checked what $_POST['site_visible'] actually contains when you get that error?


echo '<pre>' . print_r($_POST, 1) . '</pre>';

 

I get this error when I make the change as listed below

"Parse error: syntax error, unexpected T_ECHO in /home/customer/www/vbsa.org.au/public_html/Admin_Tournaments/user_files/tournament_edit.php on line 90"

 

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {

$updateSQL = sprintf("UPDATE tournaments SET tourn_name=%s, tourn_year=%s, site_visible=%s, tourn_type=%s, ranking_type=%s, tourn_class=%s, how_seed=%s, status=%s WHERE tourn_id=%s",

GetSQLValueString($_POST['tourn_name'], "text"),

GetSQLValueString($_POST['tourn_year'], "date"),

GetSQLValueString($_POST['site_visible'], "text"),

echo '<pre>' . print_r($_POST, 1) . '</pre>';

GetSQLValueString($_POST['tourn_type'], "text"),

GetSQLValueString($_POST['ranking_type'], "text"),

GetSQLValueString($_POST['tourn_class'], "text"),

GetSQLValueString($_POST['how_seed'], "text"),

GetSQLValueString($_POST['status'], "text"),

GetSQLValueString($_POST['tourn_id'], "int"));

 

Link to comment
Share on other sites

23 minutes ago, Barand said:

Your tourn_year is now just a YEAR. The is no need to use any datetime functions to reformat or extract the year (in fact they will probably return NULL as it is no longer a date or datetime type)



mysql> SELECT YEAR(tourn_year) as tyear FROM tournament;
+-------+
| tyear |
+-------+
|  NULL |
+-------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT date_format(tourn_year, '%Y') as tyear FROM tournament;
+-------+
| tyear |
+-------+
| NULL  |
+-------+
1 row in set, 1 warning (0.00 sec)

 

I am still getting "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY tournaments.tourn_type DESC, tournaments.tourn_name' at line 1"

Here is all the code I changed as I believe you suggested. I have highlighted the areas I have changed in bold black and those that I think might be causing the problem in bold red

 

$query_tourn1 = "SELECT * FROM tournaments WHERE tourn_year = YEAR(CURDATE()) AND tourn_type='Snooker' AND status='Open' ORDER BY tournaments.tourn_name";

$tourn1 = mysql_query($query_tourn1, $connvbsa) or die(mysql_error());

$row_tourn1 = mysql_fetch_assoc($tourn1);

$totalRows_tourn1 = mysql_num_rows($tourn1);

 

mysql_select_db($database_connvbsa, $connvbsa);

$query_tourn2 = "SELECT * FROM tournaments WHERE tourn_year = YEAR(CURDATE()) AND tourn_type='Billiards' AND status='Open' ORDER BY tournaments.tourn_name";

$tourn2 = mysql_query($query_tourn2, $connvbsa) or die(mysql_error());

$row_tourn2 = mysql_fetch_assoc($tourn2);

$totalRows_tourn2 = mysql_num_rows($tourn2);

 

mysql_select_db($database_connvbsa, $connvbsa);

$query_tyear = "SELECT date_format (tourn_year, '%Y') AS Tyear FROM tournaments WHERE tourn_year <> YEAR( CURDATE( ) ) GROUP BY Tyear ORDER BY Tyear DESC";

$tyear = mysql_query($query_tyear, $connvbsa) or die(mysql_error());

$row_tyear = mysql_fetch_assoc($tyear);

$totalRows_tyear = mysql_num_rows($tyear);

$tyear = mysql_query($query_tyear, $connvbsa) or die(mysql_error());

$row_tyear = mysql_fetch_assoc($tyear);

$totalRows_tyear = mysql_num_rows($tyear);

 

mysql_select_db($database_connvbsa, $connvbsa);

$query_tourn_closed = "SELECT * FROM tournaments WHERE tourn_year = YEAR(CURDATE()) AND status='Closed') ORDER BY tournaments.tourn_type DESC, tournaments.tourn_name ";

$tourn_closed = mysql_query($query_tourn_closed, $connvbsa) or die(mysql_error());

$row_tourn_closed = mysql_fetch_assoc($tourn_closed);

$totalRows_tourn_closed = mysql_num_rows($tourn_closed);

 

<tr>

<td align="center"><?php echo $row_tourn1['tourn_id']; ?></td>

<td align="left"><?php echo $row_tourn1['tourn_name']; ?></td>

<td align="left"><?php $newDate = date("Y", strtotime($row_tourn1['tourn_year'])); echo $newDate; ?></td>

 

 

<tr>

<td align="center"><?php echo $row_tourn_closed['tourn_id']; ?></td>

<td align="left"><?php echo $row_tourn_closed['tourn_name']; ?></td>

<td align="left"><?php $newDate = date("Y", strtotime($row_tourn_closed['tourn_year'])); echo $newDate; ?></td>

 

<tr>

<td>View tournaments from previous years</td>

</tr>

<?php do { ?>

<tr>

<td align="center" class="greenbg" ><a href="aa_tourn_index_history.php?tourn_year=<?php echo $row_tyear['Tyear']; ?>"><?php echo $row_tyear['Tyear']; ?></a></td>

</tr>

<?php } while ($row_tyear = mysql_fetch_assoc($tyear)); ?>

</table>

 

Edited by AS147
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

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

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

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

×   Your previous content has been restored.   Clear editor

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

×
×
  • Create New...

Important Information

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