Passing date() data to Mysql

jchapa

Hello to all,

I am using the following code to capture stats from my login page to a Mysql database table log_file.
<?PHP
// Logs the user IP
$ip = $REMOTE_ADDR;

// Logs the time accsessed

$time = date("Y m j h i s");

$u="$username";
//Logs the referer
$referer = $HTTP_REFERER; //$http_referer is PHP variable to get referer
//Logs the browser
$browser = $HTTP_USER_AGENT; //$http_user_agent is PHP variable for browser

$result=MYSQL_QUERY("INSERT INTO log_files (userbrowser,username,userip,accesstime) ".
"VALUES ('$browser','$u','$ip','$time')");

// Close the file
//fclose($fp);
?>

I can store the date as a string which is what it is doing, however, that does not do me any good when I query based on date because it is a string. I have changed the accesstime from Varchar to datetime. However, the value is just 0000000000. When I call my log file using Log.php it displays all my other fields and 00000000 for accesstime field. If I switch the field back to varchar it displays the date and time correctly. However, I want to be able to store a date and time in datetime field and retrive using my log.php file i created to display the date/time as I wish.

Any feeback is appreciated.

jchapa aka theranchhand

devinemke

the native date/time format for mySQL's DATETIME field type is YYYY-MM-DD HH:MM:SS (24 hour time). you should store all of your date/time this way. you can always reformat the dates in mySQL using DATE_FORMAT or in PHP using [man]date[/man]

jchapa

Originally posted by devinemke
the native date/time format for mySQL's DATETIME field type is YYYY-MM-DD HH:MM:SS (24 hour time). you should store all of your date/time this way. you can always reformat the dates in mySQL using DATE_FORMAT or in PHP using [man]date[/man]

devinemke,
if you take a look at the code I am using I have the date in that format($time=date(). However, when it is passed from the $time to Mysql it is being passed as a string into a datetime field named accesstime. The question is how do I convert the string before posting to Mysql so that I do not get 00000000000 instead of an actual date and time when I retrieve that info.

Thanks.

devinemke

Originally posted by jchapa
if you take a look at the code I am using I have the date in that format($time=date().

no you are not. in your code posted above you are passing the date to mySQL in this format...

$time = date("Y m j h i s");

...which not the standard mySQL date format. if you pass a string like that to a mySQL DATE or DATETIME field mySQL will consider it invalid and fill the date entry with 0's. you need to format your date/times like this...

$date_time = date('Y-m-d H:i:s');

again, you can always reformat for display using the functions i described above.

jchapa

Originally posted by devinemke
no you are not. in your code posted above you are passing the date to mySQL in this format...
$time = date("Y m j h i s");
...which not the standard mySQL date format. if you pass a string like that to a mySQL DATE or DATETIME field mySQL will consider it invalid and fill the date entry with 0's. you need to format your date/times like this...
$date_time = date('Y-m-d H:i:s');
again, you can always reformat for display using the functions i described above. [/B]

Thanks it worked. Now I just need to refomat the output. My log file will extract in Mysql format using my log.php file. I'm gonna try and figure out how to reformat the output using the functions you provided.

Have a great one! :-)