Perl quoting issue in CGI script (MySQL program)

[iamhere]

Hi,
I have this line in my CGI Perl script:

$sth=$dbh->prepare (qq {INSERT INTO reviews VALUES ('$ary[0]','$reviewername','$topic','$review','DATE_FORMAT( CURDATE(),"%D %M, %Y"') } ) ;

, but I keep getting "0000-00-00" in the "date" field in the database. How must I quote it so that the 'DATE_FORMAT' part is interpolated?

Thanks.

[borrob]

this is probebly because the default format in mysql is
YYYY-MM-DD

i would try:
$sth=$dbh->prepare (qq {INSERT INTO reviews VALUES ('$ary[0]','$reviewername','$topic','$review','CURDATE()' } ) ;

[simplistik]

You don't do anything to it, you would just do
CURDATE() in place of
'DATE_FORMAT( CURDATE(),"%D %M, %Y"')

A date field is meant to be formatted in YYYY-MM-DD you can't change it, so by you doing %D %M %Y you're trying to insert say:


14th January 2007

when it has to be


2007-01-14

if you want it to display as the first version you need to convert it on output

[iamhere]

You don't do anything to it, you would just do
CURDATE() in place of
'DATE_FORMAT( CURDATE(),"%D %M, %Y"')A date field is meant to be formatted in YYYY-MM-DD you can't change it, so by you doing %D %M %Y you're trying to insert say:


14th January 2007
when it has to be


2007-01-14
if you want it to display as the first version you need to convert it on output


Yes yes, I want my MySQL database to have "14th January 2007" inside it - how do I do that?

[ajcates]

The best way to store time formats in a database is easily the epoch (http://en.wikipedia.org/wiki/Epoch_(reference_date)) format. You then have a function called something like myDateFormat and use only that function for formating dates, this way you are able to change the way dates are displayed super easy.

With the epoch format it makes sorting dates a lot easier and simpler and you can add and subtract times from them in an easy way. Also now you are more exact then just what day it was posted.

epoch ftw!

[iamhere]

this is probebly because the default format in mysql is
YYYY-MM-DD

i would try:
$sth=$dbh->prepare (qq {INSERT INTO reviews VALUES ('$ary[0]','$reviewername','$topic','$review','CURDATE()' } ) ;

You made a mistake there - there'd be another ) after the 'CURDATE()', to match up with the ( before the '$ary[0]' - but even with that, it doesn't seem to be working - I still get "0000-00-00".

[simplistik]

remove the quotes from around CURDATE() on another note, you shouldn't store it as 14th January 2007 as it's not really a usable format by mysql either store it as DATE (which is what your datatype is currently set to), TIME, DATETIME or as a unixtimestamp. That way you can sort, by dates and what nots. You would just convert the data on output if you wanted the 14th January 2007 to show.


The best way to store time formats in a database is easily the epoch (http://en.wikipedia.org/wiki/Epoch_(reference_date)) format. You then have a function called something like myDateFormat and use only that function for formating dates, this way you are able to change the way dates are displayed super easy.

With the epoch format it makes sorting dates a lot easier and simpler and you can add and subtract times from them in an easy way. Also now you are more exact then just what day it was posted.

epoch ftw!
:lol: not very helpful at all

[iamhere]

You would just convert the data on output if you wanted the 14th January 2007 to show.



How would I do that?

[simplistik]

I don't know how to do it in Perl, Perl date format

[ramie]

Look at POSIX strftime or the CPAN module Date (I think) to do this in perl.

If I remember correctly it would be something like... print POSIX::strftime('%e %B %Y', yourtime)

Been a long time since I done any perl hacking.