Converting Dates Between MySQL and PHP


Hello Again.

This time I would like assistance in trying to find a good way to convert a date in one format from a MySQL database into another format in PHP.

To put this into context, this is for use in submitting comments. A user is on my page and they enter the information to comment on a page on the web site. They press submit and it goes to this script here. I haven’t finished it because I don’t understand at all how to implement what I want.

For example, I want to store the username of the person who is submitting the comment, the time they submitted it (which will include the date and the time), the title they wish for the comment to be, and the actual comment itself. The subject is optional for the user to enter, and by default will remain empty if a user does not enter anything.

The question is how do I recall the DATETIME datatype from the database that I’ve stored and convert it from the YYYY-MM-DD HH:MM:SS format to something like April 7th, 2012 At 10:44 PM for display using PHP?


<?php // Just a note. This Code is Incomplete //This is the current code on the "production" server I have. The incomplete code is commented out below the header function. /* Redirect to a different page in the current directory that was requested */ $host = $_SERVER['HTTP_HOST']; $uri = "/DD"; $extra = 'dd-cmt.php'; echo "Comment Submissions are NOT AVAILABLE at this time. Redirecting you in 3 seconds to Comment Page. Or click here.
"; header("Refresh: 3; URL=http://$host$uri/$extra"); //This is where the actual script would start when completed. /* Start a new session or load an existing one from users computer */ session_start(); // Require A User To Be Logged In via Login Include Script include "auth/empauth.php"; //Include Connection to Database So Insertions, Checks, and Deletions can be made to the applicable tables $con = mysql_connect('localhost', 'username', 'password') or die(mysql_error()); $db = mysql_select_db('database', $con) or die(mysql_error()); //Include the protect() function to attempt securing input from rouge SQL Injection Attacks. require "../functions.php"; //NOTE: This Is the included /functions.php function protect($string) { $string = trim(strip_tags(addslashes($string))); return $string; } //NOTE: Back to the original coded file. // Start Checking all Variables // Check to see if the form has been submitted if($_POST['submit']) { //If The Form HAS been submitted, then Start Processing Formwork //Protect ALL input from SQL Injections $title = protect($_POST['title']); $subject = protect($_POST['subject']); $comment = protect($_POST['comment']); $username = protect($_SESSION['username']); } else{ // Now Start Checking to see if Required Fields Have Been Filled In // Check to See If A Username Is Present in the Session Array if($_SESSION['username'] != "" || $_SESSION['username'] = NULL ) { //If The Session Username (Is Empty OR Null), Display an Error echo "An Error Was Encountered Posting Your Comment. No username is present. Please report to an administrator."; } else{ //If a username is present, then check all user fields if(!$title || !$comment) { //If No Title Or Comment are present, display an error echo "You MUST enter a Title and a Comment!" } else{ //We have the required materials to make a minimal submission. Insert Data into the database... At least I think. Lol. ?>


This is just a very simple test file so I could learn the date() function and how it worked.


<?php $datetime = date('F jS, Y \a\t g:i A'); echo $datetime; ?>



I use this format for a timestamp in my sites…

date(‘Y-m-d H:i:s’)

But, you can convert a database datetemp formatted field in any manner you want.
Just pull it from the database and use the data() function to format it however you want.

Here is the link to the various formats that can be used. Look down at the posts for other possibles… Good luck…

Description [php]string date ( string $format [, int $timestamp = time() ] )[/php]

Returns a string formatted according to the given format string using the given integer timestamp or the current time if no timestamp is given. In other words, timestamp is optional and defaults to the value of time().

I stumbled around trying to figure out a way to avoid using Unix Timestamps in the date() function. In other words, avoiding conversions from a date to a unix timestamp and back. After looking around at the various other date functions, I saw it as a dimwitted attempt. So instead I looked back at the date() function page.

Then I saw the strtotime() function. That would probably handle my problem well, because strtotime() accepts MySQL DATETIME formats, and turns them into Unix Timestamps, which the date() function accepts.

I guess if I wasn’t so stubborn to NOT use Unix Timestamps at all costs, I would not be posting about such a problem now.

In any case, for anyone looking at this thread, here is how to convert a MySQL DATETIME value (or object, is that proper terminology?) into any other format using date().


<?php //Database Time (Now) $dbdatetime = date('Y-m-d H:i:s'); //Print (Well, echo) the faux database value echo $dbdatetime; ?>

//Attempt to format using date(), and converting the MySQL datetime into a Unix Timestamp using strtotime().
$cmt_datetime = date(‘F jS, Y \a\t g:i A’, strtotime( $dbdatetime ));

//Print (Well, echo) the formatted value to the screen.
echo $cmt_datetime;


By the way Ernie, I believe you have a mistake in your post.

Just pull it from the database and use the data() function to format it however you want.

I believe you meant that as date(). ;D


LOL, yep! I plan to upgrade my fingers to at least an i7 so they can keep up with my mind! Hee!

Glad you solved it… CYA in the bitstream…