[RESOLVED] Inserting date values into MySQL

[lcook]

My date values are all getting inserted as 0's ... Can someone point out why?

Here is my query in PHP:

 $insertSQL = sprintf("INSERT INTO quals (username, cid, type, passportnum, datequal, dateemployed, datehundred) VALUES (%s,%s,%s,%s,%s,%s,%s)",
                       GetSQLValueString($_SESSION['MM_Username'], "text"),
					   GetSQLValueString("ADS", "text"),
					   GetSQLValueString($_POST['type'], "text"),
                       GetSQLValueString($_POST['passportnum'], "int"),
					   GetSQLValueString($_POST['datequal'], "date"),
					   GetSQLValueString($_POST['dateemployed'], "date"),
                       GetSQLValueString($_POST['datehundredthdive'], "date"));

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

And here is my table structure: (note the corresponding 'date' fields, not all of which are applicable for this script but are set as dates, nonetheless)

CREATE TABLE `quals` (
 `uqid` int(11) NOT NULL auto_increment,
 `username` varchar(15) character set utf8 NOT NULL,
 `cid` enum('ADS','BDS','ALST') NOT NULL,
 `type` varchar(150) default NULL,
 `passportnum` int(15) NOT NULL,
 `datequal` date NOT NULL,
 `dateemployed` date default NULL,
 `dateoffshore` date default NULL,
 `datehundred` date default NULL,
 `datecomp` date default NULL,
 `datebelldiver` date default NULL,
 `datefourhundred` date default NULL,
 PRIMARY KEY  (`uqid`,`username`,`cid`),
 KEY `username` (`username`),
 CONSTRAINT `quals_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
 

[Roger_Ramjet]

What on earth is GetSQLValueString ? It is not a PHP function that I know of so I've no idea why it does not like dates.

What I do know is that the mysql extension (mysql_query etc) is well out of date. The manual strongly recommends using mysqli instead: http://php.net/manual/en/mysqli.overview.php.

Personally I can't see why anyone would not just use PDO or another library that makes Prepared Statements easy to use. Save all this fussing about escaping user input etc since the library does it for you. PDO and prepared statements also make sprintf etc redundant.

As to the original question: what format is the date in the user input? MySQL stores dates as yyyy-mm-dd and so expects them in that format (or yy-mm-dd, yyyymmdd, yymmdd). So if you are not passing one of those format in it will not be accepted.
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

[lcook]

GetSQLValueString is beyond my newbie comprehension, but was generated by Dreamweaver as:

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;

}

}

Reformatted the date entries using jquery and voila! Will definitely be using mysqli going forward, but for this application it is easier to just work with what Dreamweaver spits out.

Many thanks!