Recording date as timestamp with PHP & SQL

Question

First time posting a question here so please bear with me. Below is the code of a page I am making:

<?php require_once('Connections/reps.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"] == "form1")) {

  mysql_select_db($database_reps, $reps);
  $query_bonusset = "SELECT bonus FROM staff WHERE id = '".$_POST['staffMember']."'";
  $bonusset = mysql_query($query_bonusset, $reps) or die(mysql_error());
  $row_bonusset = mysql_fetch_assoc($bonusset);
  $totalRows_bonusset = mysql_num_rows($bonusset);


  mysql_select_db($database_reps, $reps);
  $query_listprice = "SELECT Price FROM products WHERE id = '".$_POST['product']."'";
  $listprice = mysql_query($query_listprice, $reps) or die(mysql_error());
  $row_listprice = mysql_fetch_assoc($listprice);
  $totalRows_listprice = mysql_num_rows($listprice);

  $insertSQL = sprintf("INSERT INTO orders (ID, customerName, staffMember, SalesPrice, Price, AgeOfPayment, product, Quantity, orderDate, bonus) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['ID'], "int"),
                       GetSQLValueString($_POST['customerName'], "text"),
                       GetSQLValueString($_POST['staffMember'], "text"),
                       GetSQLValueString($_POST['SalesPrice'], "int"),
                       GetSQLValueString($row_listprice['Price'], "int"),
                       GetSQLValueString($_POST['AgeOfPayment'], "int"),
                       GetSQLValueString($_POST['product'], "text"),
                       GetSQLValueString($_POST['Quantity'], "int"),
                       GetSQLValueString($_POST['orderDate'], "int"),
                       GetSQLValueString($row_bonusset['bonus'], "int"));

  mysql_select_db($database_reps, $reps);
  $Result1 = mysql_query($insertSQL, $reps) or die(mysql_error());

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

mysql_select_db($database_reps, $reps);
$query_customers = "SELECT * FROM customers WHERE Status = 'Active'";
$customers = mysql_query($query_customers, $reps) or die(mysql_error());
$row_customers = mysql_fetch_assoc($customers);
$totalRows_customers = mysql_num_rows($customers);

mysql_select_db($database_reps, $reps);
$query_products = "SELECT * FROM products";
$products = mysql_query($query_products, $reps) or die(mysql_error());
$row_products = mysql_fetch_assoc($products);
$totalRows_products = mysql_num_rows($products);

mysql_select_db($database_reps, $reps);
$query_staff = "SELECT * FROM staff";
$staff = mysql_query($query_staff, $reps) or die(mysql_error());
$row_staff = mysql_fetch_assoc($staff);
$totalRows_staff = mysql_num_rows($staff);
?>
<!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>Site Title</title>
<?php


require_once("models/config.php");
if (!securePage($_SERVER['PHP_SELF'])){die();}
require_once("models/header.php");

?>

</head>

<body>

<div id='wrapper'>
<div id='top'><div id='logo'></div></div>
<div id='content'>
<h1>Site Name</h1>
<h2>Change This</h2>
<div id='left-nav'>

<?php include("left-nav.php"); ?>

</div>
<div id='main'>


<div id ="prodtable">
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
  <table align="center">
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Customer Name:</td>
      <td><select name="customerName">
        <?php 
do {  
?>
        <option value="<?php echo $row_customers['ID']?>" ><?php echo $row_customers['Name']?></option>
        <?php
} while ($row_customers = mysql_fetch_assoc($customers));
?>
      </select></td>
    </tr>
    <tr> </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Sold by: </td>
      <td><select name="staffMember">
        <?php 
do {  
?>
        <option value="<?php echo $row_staff['ID']?>" ><?php echo $row_staff['StaffName']?></option>
        <?php
} while ($row_staff = mysql_fetch_assoc($staff));
?>
      </select></td>
    </tr>
    <tr> </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Sales Price (€): </td>
      <td><input type="text" name="SalesPrice" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Product:</td>
      <td><select name="product">
        <?php 
do {  
?>
        <option value="<?php echo $row_products['ID']?>" ><?php echo $row_products['ProductName']?></option>
        <?php
} while ($row_products = mysql_fetch_assoc($products));
?>
      </select></td>
    </tr>
    <tr> </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Quantity:</td>
      <td><input type="text" name="Quantity" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">Order Date:</td>
      <td><input type="text" name="orderDate" value="" size="32" /></td>
    </tr>
    <tr valign="baseline">
      <td nowrap="nowrap" align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record" /></td>
    </tr>
  </table>
  <input type="hidden" name="ID" value="" />
  <!-- Removed list price as it was declared above -->
  <input type="hidden" name="AgeOfPayment" value="" />
  <!-- Removed bonus as it was decalred above -->
  <input type="hidden" name="MM_insert" value="form1" />
</form>
</div>
<p>&nbsp;</p>
</div>
<div id='bottom'></div>
</div>


</body>
</html><?php
mysql_free_result($customers);

mysql_free_result($products);

mysql_free_result($staff);
?>

What I am hoping to do is that once I submit the form, it will take the date that is entered and convert it into a Unix timestamp on the SQL Database.

Any help or prod in right direction would be great.

Thanks in advance

Also, please only post as much code as is necessary to illustrate your question. In this case, no code would have sufficed it seems. — deceze, Apr 10 '13 at 09:27
And you should use PDO and prepared statements, mysql_query() and friends are so 2007 ;) — Damien Legros, Apr 10 '13 at 09:41

score 1 · Accepted Answer · edited May 23 '17 at 11:56

There is no way to convert data from arbitrary format. There is no magic. Even if someone told you there is. So, first of all you have to determine date format first.
There is no point in converting date into unix timestamp. a timestamp for the date would be ambiguous and toilsome to use. Why bother then? Why not to use a DATE field type to store a date. Sound sensible, eh?

What you really need is to verify the entered date and convert it to 2013-04-10 format and then insert it.

What I'd suggest.

Use whatever date-picker you can find on google to fill the form.
verify entered value using explode()+checkdate() or strtotime().
insert value as string into field of DATE type

By the way, there is a better way to build queries, without GetSQLValueString function.

First, you can use the power of sprintf itself:

paraQuery("INSERT INTO orders VALUES (%d,%s,%s,%d,%d,%d,%s,%d,%d,%d",
           $_POST['ID'], 
           $_POST['customerName'], 
           $_POST['staffMember'],
           $_POST['SalesPrice'], 
           $row_listprice['Price'], 
           $_POST['AgeOfPayment'], 
           $_POST['product'], 
           $_POST['Quantity'],
           $_POST['orderDate'], 
           $row_bonusset['bonus']
           );

(paraQuery is a function from this answer)

Next, for the long inserts it's better to use a helper function instead of writing them manually:

$fields = explode(",","ID,customerName,staffMember,SalesPrice,Price,AgeOfPayment,product,Quantity,orderDate,bonus");
$query  = "INSERT INTO orders SET ".dbSet($fields);

(dbSet is a function from this question)

Thanks, worked perfectly :) – Richie Anderson Apr 10 '13 at 11:05 — Richie Anderson, Apr 10 '13 at 11:05

score 0 · Answer 2 · answered Apr 10 '13 at 09:14

0

I think strtotime() could help you out here:

http://php.net/manual/en/function.strtotime.php

answered Apr 10 '13 at 09:14

Borniet

3,544
4
24
33

score -1 · Answer 3 · answered Apr 10 '13 at 09:17

-1

Use the date_parse_from_format() to convert your posted date to an array... See php.net

Then use the mktime() to generate a unix timestamp See php.net

answered Apr 10 '13 at 09:17

Damien Legros

519
3
7

Recording date as timestamp with PHP & SQL

3 Answers3