PHP - error when insert date into MySQL

Question

Hello everyone:

I've got a typical problem when trying to insert a date into MySQL.

The column defined in MySQL is of type DATE. My PHP version is 5.3.0

Apart from this date-related issue, the rest of my code works just fine.

And this is my PHP script to do this:

$tablename = BOOKS_TABLE;
    $insert = mysql_query("INSERT INTO $tablename (barcode, book_name, volume_num,".
    " author, publisher, item_type, buy_price, buy_date) VALUES ".
    "(".
        "'" . $barcode      . "', ".
        "'" . $bookname     . "', ".
        "'" . $volumenum    . "', ".
        "'" . $author       . "', ".
        "'" . $publisher    . "', ".
        "'" . $itemtype     . "', ".
        "'" . $buyprice     . "', ".
        "'" . getMySQLDateString($buydate). "'".
        //"'STR_TO_DATE('".$buydate ."', '%d/%m/%Y'))'". //nothing changes in MySQL
    ")");

And this is the faulty function :

function getMySQLDateString($buydate) //typical buydate : 04/21/2009
{
    $mysqlDateString = date('Y-m-d H:i:s', $strtotime($buydate)); 

    return $mysqlDateString;
}

The first commented out line wouldn't do anything, the script is executed with no error, however, there is nothing changed in datebase after this.

The current approach will cause a Fatal error saying function name must be a string in this line.

Actually I followed this thread on SO, but just cannot pass the date into MySQL...

Can anyone help me figure out which part is not right?

How would you do it, in this case, to get it right?

Sorry about such a journeyman-like question, thanks a lot in advance.

Updated:

Thanks for reminding me this, and here is the exact error message as the html output :

Fatal error: Function name must be a string in C:\xampp\htdocs\comic\app\insertBookIntoDB.php on line 85

which point to the line starts with

$mysqlDateString = date('Y-m-d H:i:s', $strtotime($buydate));

Answer 1

In my opinion, storing dates using explicit date formats is annoying, and all the string conversion irritates me. I'd recommend using a BIGINT for the date field, and storing an epoch time value in the database.

You can get epoch time with PHP:

time();

Or, with more precision:

microtime();

Plus, you can manipulate them easily. To change the date to next day:

$myTime + 86400;

This epoch time format is very easy to store and use and isn't bugged with all the string conversion nonsense. To get a neat string from the value:

date("FORMAT STRING", $myTime);

Answer 2

Not sure if that the cause of your problem, but you seem to be missing the closing here :

 "'" . getMySQLDateString($buydate).

should be

 "'" . getMySQLDateString($buydate)."'"

Answer 3

In the following line:

$mysqlDateString = date('Y-m-d H:i:s', $strtotime($buydate)); 

Should this be:

$mysqlDateString = date('Y-m-d H:i:s', strtotime($buydate)); 

(eg. remove the dollar on the function) ?

Answer 4

This must be a comment but in sake of code formatting.

Despite of the fancy formatting, your code helps you nothing is such a case.
To make it much more useful, you have to add some debugging features in it.
Sensible variable names also helps
As well as removing function call from the string building

$sqldate = getMySQLDateString($buydate);
$sqldate = mysql_real_escape_string($sqldate);
//we name this variable $sql because it contains an SQL query
$sql = "INSERT INTO $tablename (barcode, book_name, volume_num,
        author, publisher, item_type, buy_price, buy_date) VALUES 
        (
        '$barcode',
        '$bookname',
        '$volumenum',
        '$author',
        '$publisher',
        '$itemtype',
        '$buyprice',
        '$sqldate'
        )";
//we name this variable $res because it's a resource 
//type variable contains query result
$res = mysql_query($sql) or trigger_error(mysql_error().htmlspecialchars($sql));

upon execution, this code will tell you comprehensive information on the error, if any occurred.