Hi I have some code shown below, which inserts date_time and date in to the mysql fields using the now() command.
However the date_time field updates but the date field doesn't and I can not seem to work out why?
my mysql fields are datetime and date
INSERT INTO vistordetails1
(ipaddress, client_id, type, date_time, company_name, location, search_term, trafic_source, no_of_pages, date, country_code) VALUES('$ip_address', '$client_id', '$type', now(),'$fields[11]','$fields[6]', '$keyword', '$referer','1', now(), '$country_code')
Table Structure
`id` int(11) NOT NULL AUTO_INCREMENT,
`client_id` int(11) NOT NULL,
`master_id` int(11) NOT NULL,
`type` tinyint(1) NOT NULL ,
`date_time` datetime NOT NULL,
`company_name` varchar(250) NOT NULL,
`location` varchar(250) NOT NULL,
`no_of_pages` int(11) NOT NULL,
`trafic_source` varchar(250) NOT NULL,
`search_term` varchar(250) NOT NULL,
`is_repeater` tinyint(1) NOT NULL,
`classification` int(11) NOT NULL,
`owner` int(11) NOT NULL,
`alert_for_repeat_visit` tinyint(1) NOT NULL DEFAULT '0',
`is_hot_list` enum('0','1') NOT NULL DEFAULT '0',
`ipaddress` varchar(50) NOT NULL,
`country_code` varchar(10) NOT NULL,
`date` date NOT NULL,
Try using CURDATE() instead of NOW() for the date field.
$INSERT = "INSERT INTO vistordetails1 (ipaddress, client_id, type, date_time, company_name, location, search_term, trafic_source, no_of_pages, date, country_code) VALUES( '".mysql_real_escape_string(trim($ip_address))."', '".mysql_real_escape_string(trim($client_id))."',
'".mysql_real_escape_string(trim($type))."', now(), '".mysql_real_escape_string(trim($fields[11]))."', '".mysql_real_escape_string(trim($fields[6]))."', '".mysql_real_escape_string(trim($keyword))."', '".mysql_real_escape_string(trim($referer))."', '".mysql_real_escape_string(trim(1))."', CURDATE(), '".mysql_real_escape_string(trim($country_code'))."')";
Also as a previous user mentioned you seriously need to make sure your data is escaped, or use mysql prepare.
If you need to convert a PHP date into a valid MySql date, then the only thing you need to do is ensure that it is formatted correctly.
The default date/datetime format for Mysql is: yyyy-mm-dd and yyy-mm-dd hh:mm:ss
Something like this will work.
$phpDate = date('Y-m-d H:i:s', time());
However
Because you are creating a new date, why use PHP at all? Within MySql you can include the current date using NOW() function.
$sql = "INSERT INTO xyz (a, b, c, datetime) VALUES ('a', 'b', 'c', NOW());"
Additionally
You could apply a default value of CURRENT_TIMESTAMP to the table column. Meaning you wouldn't even need to pass any date as the database will take care of it.
i.e ALTER TABLE test CHANGE datecolumn datecolumn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;