AS147

March 8, 2021

By the way Barand, I am looking for a developer to provide a website for us with the capabilities to schedule and run events. Below is a summary of what we are looking for and wondered whether you could recommend someone. It would be ideal if they were based in Australia but that is flexible.

My view is that we should default to looking at using as much off the shelf capability for our website as possible to reduce cost and complexity and time to deliver. There may be some customisations and most off the shelf products support customisations. I strongly recommend you look at the first 3 links below to see what is available off the shelf.

As we discussed there are 5 parts to our website and nothing is unusual that can’t be in the main served by off the shelf solutions.

1. Content Management - News stories, files, procedures, policies etc.

This is currently a customised home built solution. We agreed to use an off the shelf product called Wordpress

2. Ecommerce - Where we create an item to pay for such as events and membership

This is currently an off the shelf solution called ECWid. There are many solution available, as we would be using Wordpress for our website such as woocommerce as it links very well with the systems needed for the membership and events (see below)

3. Events - Competitions, Scheduling (calendar), entries, match draws, scores & results

This is currently a customised home built solution. Much like Wordpress which is probably the worlds most supported content management web system I have found Sportspress Pro from Themeboy (https://www.themeboy.com/sportspress-pro/) as it is probably the worlds largest sports system which includes all the major things we need.

Here are all the extensions that work with this including membership and ecommerce - https://www.themeboy.com/sportspress-extensions/

Here are all the sports it supports (INCLUDING SNOOKER) https://www.themeboy.com/preset-sports/

4. Membership - Player profile, rankings, player history

As above with events system, there are many membership capabilities in Sportspress Pro from Themeboy

People to self serve to create and maintain their membership accounts and to create /maintain team profiles (with players) and select competitions they want themselves and their teams to enter

Player history (playing record and ranking points)

5. Email - For sending large volumes of emails to our members

This is currently a customised home built solution. There are many bulk email solutions available - To be discussed

We also have a set of communications systems

Microsoft Office 365 which has a whole suite of other solutions including:

· Outlook - Email

· MS Teams - Video conferencing and virtual meetings

· OneDrive - document storage and sharing

· Forms – We use it for team and membership sign ups but hopefully the website will cover this finctionality

SMS - An off the shelf solution called www.SMSBroadcast.com.au

Also we have Facebook & Twitter

March 8, 2021

I am a bit confused, as the form used to show Type:Timestamp Default:Current_timestamp. Wouldn't that produce the same issue i.e. that it is the current year?

Its entirely possible that we have never entered the net years calendar until that year but I doubt it. I have only been involved in this system for a few years and never dealt with the calendar before.

March 6, 2021

We now have a problem adding new tournaments this year caused by the SQL upgrade moving to STRICT mode. Without that we have had no issues adding tournaments each year.

Are you suggesting even if the changes we have made to fix tourn_year did not throw up the site_visible error we would have a problem next year? Could you explain why if that is the case please?

I will certainly look into PDO but my main focus is to troubleshoot the site_visible error. Any other racing or suggestions you could make please?

Agaian, thankyou very much my knowledge about PHP has increased from a very low base but I know I have lots more road to travel.

March 6, 2021

20 minutes ago, Barand said:

It appears you have a hidden input field called tourn_year.

Your php code is also trying to reformat the tourn_year field as though it were still a datatime type field. You also have an update query which requires change.

Do yourself a favour and drop all the "GetSQLValueString()" stuff and use prepared query statements. While you're at it, seitch to PDO - much better.

Sorry some of this is over my head as I am not a php developer and I have inherited this code. My key goal is to get this error (site_visible) fixed and then hand the whole site over to a proper developer organisation as its over 15 years old written code by a self taught person over many years.

Are you suggesting any of the below are a likely contributor to the site_visible error message?

Is you comment about hidden input fields referring to this part of the code?

Is your comment about trying to reformat the Tourn_year field as though it were still a datetime referring to this part of the code?
GetSQLValueString($_POST['tourn_year'], "date"),

Regarding dropping all the "GetSQLValueString()" stuff do you think this a contributor to the issue?

I wouldn't know the format of prepared query statements. Is there an example or reference you can point me to?

Also do you mean SWITCH? and what is PDO? ......."While you're at it, seitch to PDO - much better.

I have listed the code for this php file below for completeness after our changes to fix tourn_year

<?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")) {
  echo '<pre>' . print_r($_POST, 1) . '</pre>';
  $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"));

  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>

March 6, 2021

Perfect! The list to view and add tournaments is now working. Thanks VERY much.

You asked me to insert the echo in the tournament_insert php (apologies for not understanding where it belonged and appreciate your patience). Here is what the echo returns. We wanted to see what the site_visible value returned was for the error Column 'site_visible' cannot be null

I get this..

Array

(

[tourn_name] => TEST ECHO

[tourn_class] => Aust Rank

[ranking_type] => No Entry

[how_seed] => NA

[site_visible] => Yes

[tourn_type] => Snooker

[tourn_id] =>

[tourn_year] => WONDER WHY THIS SHOWS UP AS NOTHING?

[status] => Open

[MM_insert] => form1

)

Column 'site_visible' cannot be null

March 6, 2021

14 minutes ago, Barand said:

This is the cause of your syntax error

Look at the rror message closely

Missing open bracket, got it thanks.

March 6, 2021

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="left"><?php $newDate = date("Y", strtotime($row_tourn1['tourn_year'])); echo $newDate; ?></td>

<tr>

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

March 6, 2021

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

March 6, 2021

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

March 6, 2021

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

March 6, 2021

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

March 6, 2021

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

March 6, 2021

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?

March 3, 2021

....Also I notice there is a column value available of YEAR, would that be more appropriate as the column definition?

March 3, 2021

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" />

</head>

<body>

<table width="800" align="center">
<tr>
<td colspan="2"> <?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"> </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> </p>
<p> </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" />

</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"> </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> </td>
<td> </td>
<td> </td>
<td> </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> </td>
<td> </td>
<td> </td>
<td> </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> </p>
</body>
</html>

March 3, 2021

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>

<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"> </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> </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"> </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> </p>
<p> </p>
</body>
</html>

March 2, 2021

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

Sign In

AS147

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by AS147

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Converting timestamp to varchar

Browse

Activity

Important Information