php date to mysql datetime record update

3 posts by 2 authors in: Forums > CMS Builder
Last Post: December 8, 2011   (RSS)

By ht1080z - December 6, 2011

Hello,

I try to update in my table the mysql datetime field but no luck.

i managed to convert my 26/05/2011 php date format to 2011-05-26 00:00:00 with:

$time = strtotime( @$_REQUEST['from'] );
$from = date("Y-m-d H:i:s", $time);
..... and $to as well


but cannot update the field 'from' and 'to' in my db:

$query = "UPDATE `{$TABLE_PREFIX}accoms` SET
title = '".mysql_escape( $_REQUEST['title'] )."',
type = '".mysql_escape( $_REQUEST['type'] )."',
category = '".mysql_escape( $_REQUEST['category'] )."',
from = '".mysql_escape($from)."',
to = '".mysql_escape($to)."',
desc = '".mysql_escape( $_REQUEST['desc'] )."',
info = '".mysql_escape( $_REQUEST['info'] )."',

updatedByUserNum = '".mysql_escape( $CURRENT_USER['num'] )."',
updatedDate = NOW()
WHERE createdByUserNum = '".mysql_escape( $CURRENT_USER['num'] )."'";
mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");


for date update getting error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from = '2011-01-04 00:00:00', to = '2011-01-10 00:00' at line 6

Please advise!
Karls

Re: [ht1080z] php date to mysql datetime record update

By Jason - December 8, 2011

Hi Karls,

I think the problem here is that two of your field names (from and to) are reserved words in MySQL (http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html)

You can get around this by adding ` around your column names like this:

$query = "UPDATE `{$TABLE_PREFIX}accoms` SET
`title` = '".mysql_escape( $_REQUEST['title'] )."',
`type` = '".mysql_escape( $_REQUEST['type'] )."',
`category` = '".mysql_escape( $_REQUEST['category'] )."',
`from` = '".mysql_escape($from)."',
`to` = '".mysql_escape($to)."',
`desc` = '".mysql_escape( $_REQUEST['desc'] )."',
`info` = '".mysql_escape( $_REQUEST['info'] )."',

updatedByUserNum = '".mysql_escape( $CURRENT_USER['num'] )."',
updatedDate = NOW()

WHERE createdByUserNum = '".mysql_escape( $CURRENT_USER['num'] )."'";


Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/