Inserting a date into MySql

Hatch

I'm using a form to get the date someone wants. So I will get values like these

$day = 12;
$month = 1;
$year = 2003;
$time = 3; // represents just the hour

Now once I have these...I can't quite understand how to insert them into the database yet. I want to insert them into a feild with the type "DATETIME" but not sure if I have to convert it into a different format first or what.

Basically what I want to know is if I need to convert the date into a unix timestamp and then how my query statement would look with insert. I've got this so far but it doesn't seem to work right.

$day = 12;
$month = 1;
$year = 2003;
$hour = 7;

$dateunix = mktime("7","0","0",$month,$day,$year);
echo $dateunix;
$query = "INSERT INTO calendar VALUES (NULL,$dateunix,'title','description')";
$result = mysql_query($query);
echo "Done.";

BlackCatt

Originally posted by Hatch

$query = "INSERT INTO calendar VALUES (NULL,$dateunix,'title','description')";

[/B]

You don't have to convert the date & time to unix timestamp. In fact the ANSI standard datetime syntax for inserting a column of datetime type is 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS'.

So the line above could be changed to

$query = "INSERT INTO calendar VALUES (NULL,'$year-$month-$day $hour:00:00','title','description')";

Hatch

Oh sweet thx! That was easier than I thought 🙂

Now how would you go about getting that date and converting it to PHP format? What would your mysql statement look like for retreiving the info you just helped me put in? I do have this code here that does it

 $query = "SELECT date, UNIX_TIMESTAMP(date) as unixdate FROM calendar WHERE id LIKE '$id'";
$result = mysql_query($query);

$entry = mysql_fetch_array($result);
$entryUnixDate = $entry['unixdate'];
$entryRealDate = date("l, F j", $entryUnixDate);
echo $entryRealDate;

but I've heard of another way that I think might be easier but I dont quite understand how to do it. It's using a statement like this

$result = "SELECT DATE_FORMAT(date_column, '%m %d %Y') FROM tablename";

I dont understand what to put in as "date column". Could you do an example using the info from the above post?

BlackCatt

Date_Column is just that. Whatever column in your table that contains data of the datetime data type (or any other data type that can store date data). Given your example using your calendar table this would be the column "date". (Note: In some RDBMS's date is a reserved word so you may want to consider naming such columns in the future something different like EntryDate)

So replacing date_column with date would result in:

$query = "SELECT DATE_FORMAT([b]date[/b], '%m %d %Y') as FormatedDate FROM calendar";

You may also want to add a seperator in your format string so instead of '%m %d %Y' which will return 01 12 2003 you may want to add in a "-" to make the string look like '%m-%d-%Y' and return 01-12-2003.

To display this data using php

$query = "SELECT DATE_FORMAT([b]date[/b], '%m-%d-%Y') as FormatedDate FROM calendar";

$result = mysql_query($query);
$entry = mysql_fetch_array($result);
echo = $entry['FormatedDate'];

or if you know your only getting back 1 row of data

$query = "SELECT DATE_FORMAT([b]date[/b], '%m-%d-%Y') as FormatedDate FROM calendar";

$result = mysql_query($query);
echo mysql_result($result, 0, "FormatedDate");

Hatch

Wow 🙂 Thx for explaining that. That makes much more sense now.

Hatch

echo = $entry['FormatedDate'];

didn't work because of the '=' and also the 'FormatedDate' didn't work either. Is it spelled right?

BlackCatt

oops.. :-)
Try

echo $entry["FormatedDate"];

Hatch

I forgot the "as FormatedDate" in my query statement.

Works now. Appreciate the help alot 🙂