Jump to content

formatting mysql timestamp in php


xfezz

Recommended Posts

Bare with me, im bit of a newbie with php and mysql. I would like to format the timestamp from mysql with php so the date would read something like this:

October 21, 2006

I have googled quite a bit and came up with something with the UNIX_TIMESTAMP directly from the query and php's date function but im not sure if it will do what I would like it to do. Im leaning more towards concatenating the string and manipulating it to what i want it to do after its pulled from the database. But im not sure how to go about it since im storing the results in an array and printing out the array.

As of right now this is what I have for my source code. its very plain and simple. Just for testing purposes.

[code]
<? 
/*set server and database parameters*/
    $user_name = "root";
    $password = "this is my password";
    $database = "article_db";
    $server = "localhost";

/*make connection to database*/   
    $db_handle = mysql_connect($server, $user_name, $password);
    $db_found = mysql_select_db($database, $db_handle);

/*if the database is found without error, pull data from database and store in array, then print out the results*/
if ($db_found) {
    $SQL = "SELECT * FROM articles";
    $result = mysql_query($SQL);
      while ($db_field = mysql_fetch_assoc($result)) {
        print $db_field['id'] . "<BR>";
        print $db_field['article_date'] . "<BR>";
        print $db_field['message'] . "<BR>";
}
/*close database connection*/
mysql_close($db_handle);
}

/*if database isnt found let user know*/
  else {
    print "Database NOT Found ";

/*close database connection*/
    mysql_close($db_handle);
}

?>
[/code]

As you can see im just outputing the entire database onto the screen.  so my data looks like the following

[quote]1
2006-10-20 22:15:45
Hey there
2
2006-10-20 22:27:32
testing[/quote]

eventually I would just like to display the 2 most recent entries in the database so my output would like something like this:

[quote]October 21, 2006
This is my article

October 20, 2006
This is my other article[/quote]

But I dont want to get ahead of myself right now. I just would like to format the timestamp. If anyone has any suggestions about my code let me know. Maybe theres an easier or cleaner way to go about this.

Link to comment
Share on other sites

[code]

<?php

$now_format = "2006-10-20 22:27:32"; // stored time, your $db_field['article_date']
$new_format = date('F d, Y',strtotime($now_format)); // transform
echo $new_format;  // prints out: October 20, 2006

?>

[/code]
Link to comment
Share on other sites

ahh simple syntax got me again ty much for the help. I got the date format to how I would like.

edit: any ideas on how to go about only displaying the last 2 entries in the database? having the most recent one displayed first (on top) ?

i came up with this:

[code]
//if the database is found without error, pull results from database and store in array
if ($db_found) {
    $SQL = "SELECT article_date, message FROM articles ORDER BY article_date DESC LIMIT 2";
    $result = mysql_query($SQL);
      while ($db_field = mysql_fetch_assoc($result)) {
$now_format = $db_field['article_date'];
$new_format = date('F d, Y',strtotime($now_format)); // transform
        echo $new_format ."<BR>";  // prints out: date in following format October 20, 2006
        echo $db_field['message'] . "<p>";
}

[/code]

seems to be working but looks ugly as sin. would this be the only way to go about it?
Link to comment
Share on other sites

What do you mean ? Is it sorting uncorrectly or is it the layout result that is "ugly" ?
I assume you have a unique row ID (or at least you should have - primary key, auto increment), you can sort from this

SELECT article_date, message FROM articles ORDER BY id DESC LIMIT 2
Link to comment
Share on other sites

[quote author=alpine link=topic=112209.msg455321#msg455321 date=1161413795]
What do you mean ? Is it sorting uncorrectly or is it the layout result that is "ugly" ?
I assume you have a unique row ID (or at least you should have - primary key, auto increment), you can sort from this

SELECT article_date, message FROM articles ORDER BY id DESC LIMIT 2
[/quote]

Sorry I should of been more clear. What I meant was this following line:
[code]
$SQL = "SELECT article_date, message FROM articles ORDER BY article_date DESC LIMIT 2";
[/code]

Seems to be quite cumbersome and will only get longer once I come up with new ideas on what I want to display. I was just wondering if theres a cleaner code to accomplish what I currently would like to do. And to answer your question, yes I do have a primary key, auto increment, the id field. id is shorter than articles_date. Never thought about that. ty. ;]
Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.