• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1625
  • Last Modified:

What is the proper php/mySQL syntax to insert a date/timestamp into a text column of mysql table using insert command

I've been trying to insert a record into a table using some values from a form, which was working until
i tried adding a timestamp field, it only seems to work if i put a static string in it. I though the code below should work, but it doesn't. Here is an example string that is contained in the variable today
"November 14, 2008, 8:46 am"
I figured maybe my problem is on the comma's or the colon, but don't know what to do about it.

$today = date("F j, Y, g:i a");

$query = "insert into myTable values (NULL,$today)";

 $result = $db->query($query);


I also tried using  SELECT NOW(); which works on the command line to mySQL, but again don't know how to syntax it correctly to be used as an input to the insert statement

The column i'm trying to insert it into is defined as char(30)
0
mitchguy
Asked:
mitchguy
1 Solution
 
ZeroWorksCommented:
Hi!
Try this
November 14, 2008, 8:46 am
 $query = "insert into myTable values (NULL, DATE_FORMAT(NOW(), '%M %e, %Y, %h:%i %p'))";
More info on
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
Hope it helps!
0
 
NoiSCommented:
To insert the current datetime

USE

$sql = "INSERT INTO table (datetime_timestamp_field) VALUES (NOW())";

for your code the best way is use YEAR-MONTH-DAY HOUR:MIN:SEC

$today = date("Y-m-d H:i:s");

$query = "insert into myTable values (NULL,$today)";

 $result = $db->query($query);
0
 
Roger BaklundCommented:
The problem with your query is that you have omitted the required apostrophes. This is wrong:

$query = "insert into myTable values (NULL,$today)";

It should be:

$query = "insert into myTable values (NULL,'$today')";
0
 
mitchguyAuthor Commented:
The apostrophes worked!  
A rookie mistake, I had tried that before, but forgot to try it again after changing my field from char(20) to char(30) after i noticed my string was longer than 20.

Thanks for all of your inputs.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now