Skip to main content
W
Web_Decisions
Inspiring
November 17, 2012
Answered

Date (date and time) with MySQL, register here!

  • November 17, 2012
  • 1 reply
  • 1540 views

Hi,

Summary:

I have a page (form) to capture attendance. It works, it allows user to enter their attendance details. On click, it writes to the database - table. The date is not captured on the form. I want to date stamp this by inserting a date (date and time - declared field). I will provide source code. Please pin point what code I should be inserting and wheres about within this code.

Question:

Insert date and time into database table -

Code:

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

<?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"] == "form")) {

  $insertSQL = sprintf("INSERT INTO attendlist (Windowsid, title, firstname, surname, extension, mobile, jobrole, course, cpg, mgrname, room) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

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

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

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

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

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

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

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

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

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

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

                       GetSQLValueString($_POST['select3'], "text"));

  mysql_select_db($database_dbAttendance, $dbAttendance);

  $Result1 = mysql_query($insertSQL, $dbAttendance) or die(mysql_error());

  $insertGoTo = "Thankyou.php";

  if (isset($_SERVER['QUERY_STRING'])) {

    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

    $insertGoTo .= $_SERVER['QUERY_STRING'];

  }

  header(sprintf("Location: %s", $insertGoTo));

}

mysql_select_db($database_dbAttendance, $dbAttendance);

$query_rsAttendance = "SELECT * FROM attendlist";

$rsAttendance = mysql_query($query_rsAttendance, $dbAttendance) or die(mysql_error());

$row_rsAttendance = mysql_fetch_assoc($rsAttendance);

$totalRows_rsAttendance = mysql_num_rows($rsAttendance);

?>

<!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 http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Delegate Register</title>

<link href="Registerpage.css" rel="stylesheet" type="text/css" />

<link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" />

<link href="SpryAssets/SpryValidationSelect.css" rel="stylesheet" type="text/css" />

<script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>

<script src="SpryAssets/SpryValidationSelect.js" type="text/javascript"></script>

</head>

<?php

$date = date(“format”, $timestamp);

?>

<body>

<div class="container">

  <div class="header"><img src="Cerner At Imperial2.jpg" width="960" height="150" alt="" /><!-- end .header --></div>

  <div class="content">

    <h3>Register: <?php echo "Date  - ".date("d/m/Y - H:ia")?></h3>

    <form action="<?php echo $editFormAction; ?>" method="post" name="form">

      <table width="780" border="0">

        <tr>

          <td><div align="right">User ID *</div></td>

          <td><span id="sprytextfield1">

            <input name="textfield" type="text" id="textfield" tabindex="1" size="18" maxlength="10" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="right">Title *</div></td>

          <td><span id="spryselect1">

            <select name="select" id="select" tabindex="2">

              <option value="0">Select</option>

              <option value="1">Ms</option>

              <option value="2">Mrs</option>

              <option value="3">Mdm</option>

              <option value="4">Dr</option>

              <option value="5">Mr</option>

              <option value="6">Prof</option>

            </select>

          <span class="selectRequiredMsg">Please select an item.</span></span></td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="right">First name*</div></td>

          <td><span id="sprytextfield2">

            <input name="textfield2" type="text" id="textfield2" tabindex="3" size="30" maxlength="30" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="right">Surname *</div></td>

          <td><span id="sprytextfield3">

            <input name="textfield3" type="text" id="textfield3" tabindex="4" size="30" maxlength="30" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="right">Contact / Bleep *</div></td>

          <td><span id="sprytextfield4">

            <input name="textfield4" type="text" id="textfield4" tabindex="5" size="30" maxlength="30" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="right">Mobile no: (optional)</div></td>

          <td><input name="textfield5" type="text" id="textfield5" tabindex="6" size="20" maxlength="20" /></td>

          <td> </td>

        </tr>

        <tr>

          <td> </td>

          <td> </td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="right">Job Role *</div></td>

          <td><span id="spryselect5">

            <select name="select5" id="select5" tabindex="7">

              <option value="0">Select </option>

              <option value="1">Doctor</option>

              <option value="2">Nurse IP</option>

              <option value="3">Nurse OP</option>

              <option value="4">Midwife</option>

              <option value="5">Booking Clerk</option>

              <option value="6">Medical Secretary</option>

              <option value="7">OP Receptionist</option>

              <option value="9">CNS/NP</option>

              <option value="10">Tester</option>

            </select>

          <span class="selectRequiredMsg">Please select an item.</span></span></td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="right">Manager's Name *</div></td>

          <td><span id="sprytextfield5">

            <input name="textfield7" type="text" id="textfield7" tabindex="8" size="30" maxlength="30" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

          <td> </td>

        </tr>

        <tr>

          <td> </td>

          <td> </td>

          <td> </td>

        </tr>

        <tr>

          <td> </td>

          <td> </td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="right">Course *</div></td>

          <td><span id="spryselect2">

            <select name="select2" id="select2" tabindex="9">

              <option value="0">Select</option>

              <option value="1">Demo</option>

              <option value="2">Doctors</option>

              <option value="9">CNS/NP</option>

              <option value="3">Nurse IP</option>

              <option value="4">Nurse OP</option>

              <option value="5">Midwife</option>

              <option value="6">Booking Clerk</option>

              <option value="7">Medical Secretary</option>

              <option value="8">OP Receptionist</option>

            </select>

          <span class="selectRequiredMsg">Please select an item.</span></span></td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="right">Location *</div></td>

          <td><span id="spryselect3">

            <select name="select3" id="select3" tabindex="10">

              <option value="0">Select </option>

              <option value="1">CHX 12 Floor Rm 1</option>

              <option value="2">CHX 12 Floor Rm 2</option>

              <option value="3">CHX 10 West </option>

              <option value="4">HH N207 </option>

              <option value="5">HH W12 Conference</option>

              <option value="6">SMH Rm a</option>

              <option value="7">SMH Rm b</option>

              <option value="8">SMH Ming Wing Rm 5</option>

              <option value="9">SMH Ming Wing Rm 3</option>

            </select>

          <span class="selectRequiredMsg">Please select an item.</span></span></td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="right">CPG *</div></td>

          <td><span id="spryselect4">

            <select name="select4" id="select4" tabindex="11">

              <option value="0">Select </option>

              <option value="1">CPG 1 - Medicine</option>

              <option value="2">CPG 2 - Surgery and Cancer</option>

              <option value="3">CPG 3 - Specialist Services </option>

              <option value="4">CPG 4 - Circulatory Services and Renal Medicine</option>

              <option value="5">CPG 5 - Womens and Children</option>

              <option value="6">CPG 6 - Clinical and Investigative Sciences</option>

              <option value="7">CPG 7 - Private Patients</option>

              <option value="8">Others</option>

            </select>

          <span class="selectRequiredMsg">Please select an item.</span></span></td>

          <td> </td>

        </tr>

        <tr>

          <td> </td>

          <td><input name="button" type="submit" id="button" value="Submit" /></td>

          <td> </td>

        </tr>

        <tr>

          <td><div align="center">*Mandatory fields </div></td>

          <td> </td>

          <td> </td>

        </tr>

      </table>

      <input type="hidden" name="MM_insert" value="form" />

    </form>

<!-- end .content --></div>

  <div class="footer">

<p align="center">

    <a href="http://validator.w3.org/check?uri=referer"><img

      src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0 Transitional" height="31" width="88" /></a>

      <p align="center"> Cerner@Imperial ICT Training Team </p>

</p>

 

  <!-- end .footer --></div>

  <!-- end .container --></div>

<script type="text/javascript">

var sprytextfield1 = new Spry.Widget.ValidationTextField("sprytextfield1", "none", {validateOn:["blur", "change"]});

var spryselect1 = new Spry.Widget.ValidationSelect("spryselect1", {validateOn:["change", "blur"]});

var sprytextfield2 = new Spry.Widget.ValidationTextField("sprytextfield2", "none", {validateOn:["blur", "change"]});

var sprytextfield3 = new Spry.Widget.ValidationTextField("sprytextfield3", "none", {validateOn:["blur", "change"]});

var sprytextfield5 = new Spry.Widget.ValidationTextField("sprytextfield5", "none", {validateOn:["blur", "change"]});

var spryselect2 = new Spry.Widget.ValidationSelect("spryselect2", {validateOn:["change", "blur"]});

var spryselect3 = new Spry.Widget.ValidationSelect("spryselect3", {validateOn:["change", "blur"]});

var spryselect4 = new Spry.Widget.ValidationSelect("spryselect4", {validateOn:["change", "blur"]});

var sprytextfield4 = new Spry.Widget.ValidationTextField("sprytextfield4", "none", {validateOn:["blur", "change"]});

var spryselect5 = new Spry.Widget.ValidationSelect("spryselect5", {validateOn:["blur", "change"]});

</script>

</body>

</html>

<?php

mysql_free_result($rsAttendance);

?>

This topic has been closed for replies.
Correct answer bregent

There are several ways. One is to get the date from the php time() function and use that in your insert statement. Another is to use MySQL CURDATE() function. But probably the easiest and best is to simply define the default value for the date/time column as the current date/time and exclude it from the insert statement. No code changes necessary.

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

1 reply

B
bregentCorrect answer
Participating Frequently
November 17, 2012

There are several ways. One is to get the date from the php time() function and use that in your insert statement. Another is to use MySQL CURDATE() function. But probably the easiest and best is to simply define the default value for the date/time column as the current date/time and exclude it from the insert statement. No code changes necessary.

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

W
Web_DecisionsAuthor
Inspiring
November 17, 2012

Hi,

Are you saying to set it up as stamp as it is written to in the database table? So if i specify in the table structure, they will be an option on the structure to do this? This mean I wont have to alter the source code, and include any additional code, I just affect the database structure table definition, ?

I have just changed the table definition from DATETIME to TIMESTAMP. It worked. Thanks.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices