webmaster1 Posted November 21, 2008 Share Posted November 21, 2008 Hi All, I've attempted to write the following variable into my database: $now_datetime = date('D Y-m-d h:i:s a'); It writes back as 0000-00-00 00:00:00 which obviously indicates something has gone wrong. I've used the following successfully: $now_datetime = date('Y-m-d h:i:s'); Any ideas why the former isn't inserting correctly? I suspect it's to do with my string. I've checked the manual already. Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/ Share on other sites More sharing options...
DarkWater Posted November 21, 2008 Share Posted November 21, 2008 Just use the MySQL NOW() function. Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695137 Share on other sites More sharing options...
webmaster1 Posted November 21, 2008 Author Share Posted November 21, 2008 Just checked that out here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_now It doesn't seem to include AM, PM or the day of the week. I'd like to know how to do this through PHP (or is this bad practice?). Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695143 Share on other sites More sharing options...
redarrow Posted November 21, 2008 Share Posted November 21, 2008 // set the database field to a int....... Use a timestamp then you can use any php date format you want... date formats link. http://uk3.php.net/date can add this for database protection aswell $date=mysql_real_escape_string(time()); <?php if(isset($_POST['submit'])){ $date=$_POST['date']; $date=time(); $sql="insert into table (date) values ('$date')"; $res=mysql_query($sql)or die(mysql_error()); if(mysql_affected_rows($res)){ $sql1="select dates from table where dates='$date'"; $res2=mysql_query($res1)or die(mysql_error()); while($data=mysql_fetch_assoc($res2)){ echo " ".date('d-m-y h:i:S' ,$data['dates'])." "; } } } ?> Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695146 Share on other sites More sharing options...
PFMaBiSmAd Posted November 21, 2008 Share Posted November 21, 2008 now() does not include a day of the week or am/pm because it does not need to. The DATETIME data type contains all the information necessary to determine day of the week from the date and am/pm from the 24hour figure - http://dev.mysql.com/doc/refman/5.0/en/datetime.html A Unix timestamp requires a slow conversion to be used for anything except comparisons and sorting. It also is not usable for storing birth dates or for calculating future events beyond 2038 like loan tables due to the lower and upper limits on its value. The mysql DATE_FORMAT() function will quickly format a standard DATETIME value into any format you want. No slow parsed/tokenized/interpreted php code is necessary. Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695147 Share on other sites More sharing options...
webmaster1 Posted November 21, 2008 Author Share Posted November 21, 2008 Here's my code btw though I'm not too sure how its going to tie in with redarrows method: <?php //PICK UP AND DEFINE INPUT AS INDIVIDUAL VARIABLES $path1= "upload/".$HTTP_POST_FILES['ufile']['name'][0]; $path2= "upload/".$HTTP_POST_FILES['ufile']['name'][1]; $path3= "upload/".$HTTP_POST_FILES['ufile']['name'][2]; $make = $_POST['make']; $model = $_POST['model']; $price = $_POST['price']; $engine = $_POST['engine']; $body = $_POST['body']; $transmission = $_POST['transmission']; $year = $_POST['year']; $colour = $_POST['colour']; $mileagem = $_POST['mileagem']; $mileagekm = $_POST['mileagekm']; $owners = $_POST['owners']; $doors = $_POST['doors']; $location = $_POST['location']; //DEFINE ADDITIONAL VARIABLES $now_datetime = date('Y-m-d h:i:s'); $ipaddress = getenv('REMOTE_ADDR'); //CONNECT TO RELEVANT DATABASE include("dbinfo.php"); mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to establish a connection to the relevant database."); //INSERT THE INPUT INTO DATABASE $query = "INSERT INTO test VALUES ('','$make','$model','$price','$engine','$body','$transmission','$year','$colour','$mileagem','$mileagekm','$owners','$doors','$location','$info','$now_datetime','$ipaddress','$path1','$path2','$path1')"; mysql_query($query); ?> Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695148 Share on other sites More sharing options...
redarrow Posted November 21, 2008 Share Posted November 21, 2008 I no your insert works but you need to read a tutoral to learn the format properly to name the varables your inserting mate........... <?php $query = "INSERT INTO test VALUES ('','$make','$model','$price','$engine','$body','$transmission','$year','$colour','$mileagem','$mileagekm','$owners','$doors','$location','$info','$now_datetime','$ipaddress','$path1','$path2','$path1')"; mysql_query($query)or die(mysql_error()); ?> <?php //PICK UP AND DEFINE INPUT AS INDIVIDUAL VARIABLES $path1= "upload/".$HTTP_POST_FILES['ufile']['name'][0]; $path2= "upload/".$HTTP_POST_FILES['ufile']['name'][1]; $path3= "upload/".$HTTP_POST_FILES['ufile']['name'][2]; $make = mysql_real_escape_string($_POST['make']); $model = mysql_real_escape_string($_POST['model']); $price = mysql_real_escape_string($_POST['price']); $engine = mysql_real_escape_string($_POST['engine']); $body = mysql_real_escape_string($_POST['body']); $transmission = mysql_real_escape_string($_POST['transmission']); $year =mysql_real_escape_string($_POST['year']); $colour =mysql_real_escape_string($_POST['colour']); $mileagem = mysql_real_escape_string($_POST['mileagem']); $mileagekm = mysql_real_escape_string($_POST['mileagekm']); $owners = mysql_real_escape_string($_POST['owners']); $doors = mysql_real_escape_string($_POST['doors']); $location = mysql_real_escape_string($_POST['location']); //DEFINE ADDITIONAL VARIABLES $now_datetime = time(); //set the database field to a int. $ipaddress = getenv('REMOTE_ADDR'); //CONNECT TO RELEVANT DATABASE include("dbinfo.php"); mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to establish a connection to the relevant database."); //INSERT THE INPUT INTO DATABASE //name the button name to your name of the submit button. if(isset($_POST['submit']){ $query = "INSERT INTO test VALUES ('','$make','$model','$price','$engine','$body','$transmission','$year','$colour','$mileagem','$mileagekm','$owners','$doors','$location','$info','$now_datetime','$ipaddress','$path1','$path2','$path1')"; mysql_query($query); } ?> Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695151 Share on other sites More sharing options...
webmaster1 Posted November 21, 2008 Author Share Posted November 21, 2008 Thanks for the code redarrow. When you 'format properly' are you refering to my lack of mysql_real_escape_string (I'm checking this out in the manual now) or something else? Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695163 Share on other sites More sharing options...
webmaster1 Posted November 21, 2008 Author Share Posted November 21, 2008 Hi All, I've studied the above suggestions and provided links. Redarrows approach results in inserts like 1227300237 ??? : <?php $now_datetime = time(); //set the database field to a int. ?> I've also tried PFMaBiSmAd approach but I get an error saying the now() function is undefined: <?php if(isset($_POST['submit'])) { $query = "INSERT INTO test VALUES ('','','','','','','','','','','','','','','','now()','$ipaddress','','','')"; mysql_query($query); } ?> I get the same error for: <?php $now_datetime = now(); if(isset($_POST['submit'])) { $query = "INSERT INTO test VALUES ('','','','','','','','','','','','','','','','$now_datetime','$ipaddress','','','')"; mysql_query($query); } ?> If I try the following with the mySQL column set as INT(100) only '2008' is inserted: <?php $now_datetime = date('Y-m-d h:i:s'); if(isset($_POST['submit'])) { $query = "INSERT INTO test VALUES ('','','','','','','','','','','','','','','','$now_datetime','$ipaddress','','','')"; mysql_query($query); } ?> Finally the following (column set as datetime) results in a 12 hour time format so I cannot tell whether I'm dealing with AM or PM: <?php $now_datetime = date('Y-m-d h:i:s'); if(isset($_POST['submit'])) { $query = "INSERT INTO test VALUES ('','','','','','','','','','','','','','','','$now_datetime','$ipaddress','','','')"; mysql_query($query); } ?> Can anybody offer some guidance as to what I should be looking at next? Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695879 Share on other sites More sharing options...
revraz Posted November 21, 2008 Share Posted November 21, 2008 It's not 'now()' its just NOW(), no single quotes. Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695885 Share on other sites More sharing options...
webmaster1 Posted November 21, 2008 Author Share Posted November 21, 2008 It's not 'now()' its just NOW(), no single quotes. That's it, thanks! Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695892 Share on other sites More sharing options...
PFMaBiSmAd Posted November 21, 2008 Share Posted November 21, 2008 For the first query shown - now() is a mysql function. It returns a string, so it does not get enclosed in single-quotes. For the second query shown, since this is not a php function, php code using it will result in an error. For the third query shown, $now_datetime is a formatted string, not a number and certainly not an integer. When you put a string in to a integer field, only the numeric part up to the first non-numeric character is treated as a number. For the last query shown, please read the date() function in the manual again - h 12-hour format of an hour with leading zeros 01 through 12 H 24-hour format of an hour with leading zeros 00 through 23 If you remove the single-quotes around the now() in the first query, it will work with the minimum amount of coding and quickest execution time. Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695893 Share on other sites More sharing options...
webmaster1 Posted November 21, 2008 Author Share Posted November 21, 2008 For the last query shown, please read the date() function in the manual again Overlooked this despite reading it. I'll stick with NOW() based on your reccommendation: it will work with the minimum amount of coding and quickest execution time. Quote Link to comment https://forums.phpfreaks.com/topic/133615-solved-date-and-time-function-not-writing-to-database/#findComment-695899 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.