Converting timestamp to varchar

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

Barand · March 6, 2021

"site_visible" clearly contains "Yes" and not null, so don't know why it objects.

"tourn_year" is empty because there is no input field on the form for it (and there shouldn't be if you always insert the current year)

Barand · March 6, 2021

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.

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

Edited March 6, 2021 by Barand

Barand · March 6, 2021

Out of curiosity, how are you planning to insert events for next years schedule?

* * * * *

3 minutes ago, AS147 said:

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

That's one, but I was referring to things like this (line 396)

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

* * * * *

8 minutes ago, AS147 said:

what is PDO?

It is an alternative code library to mysqli. Much easier and more streamlined.

* * * * *

10 minutes ago, AS147 said:

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

Prepared statements remove the user-provided values from the query. Instead placeholders are placed in the query and the values passed as parameters. Here's a PDO example of the code I posted earlier. (The tournament name is passed as a parameter)

$stmt = $pdo->prepare("INSERT INTO tournament (tournament_name, tourn_year) VALUES ( ? , CURDATE() )");
$stmt->execute( [ $_POST['tourn_name'] ] );

or you can have named placeholders

$stmt = $pdo->prepare("INSERT INTO tournament (tournament_name, tourn_year) VALUES ( :name , CURDATE() )");
$stmt->execute( [ 'name' => $_POST['tourn_name'] ] );

AS147 · 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.

Barand · March 6, 2021

13 minutes ago, AS147 said:

Could you explain why if that is the case please?

Because your form shows the fixed current year (2021) and the sql inserts the currrent year. How will you enter fixtures planned for 2022 (without waiting until next January)?

AS147 · 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.

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

Barand · March 8, 2021

10 hours ago, AS147 said:

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.

I mentioned next year beacause the VBSA site lists events for 2022

image.png.adfaffae9c8f04463c64b5ef7489aa5b.png

Your posted form is only capable of entering events for the current year. It also doesn't contain any dates information (ie start, finish, closing date). If it had, the tourn_year column would be redundant as the year component of the start date would dictate the tournament year. This raises the question of "How did the above fixtures get into the system?"

Barand · March 8, 2021

4 hours ago, AS147 said:

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.

I prefer to keep WordPress at least a barge-pole's length away.

There is is a "Job Offerings" section these forums. You should post your requirements there, along with contact details so prospective developers can reply direct to you.

Sign In

Converting timestamp to varchar

Recommended Posts

AS147

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

AS147

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

AS147

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

AS147

Link to comment

Share on other sites

AS147

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information