Jump to content


Photo

formatting mysql timestamp in php


  • This topic is locked This topic is locked
6 replies to this topic

#1 xfezz

xfezz
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 21 October 2006 - 05:05 AM

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.

<?  
/*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);
}

?>

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

1
2006-10-20 22:15:45
Hey there
2
2006-10-20 22:27:32
testing


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

October 21, 2006
This is my article

October 20, 2006
This is my other article


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.



#2 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 21 October 2006 - 05:29 AM


<?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

?>



#3 xfezz

xfezz
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 21 October 2006 - 05:43 AM

thanks for replying. how would i set $now_format = to my date in the array?

$now_format="$db_field['article_date']"  ???

#4 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 21 October 2006 - 05:49 AM

$now_format = $db_field['article_date'];

#5 xfezz

xfezz
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 21 October 2006 - 05:51 AM

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:

//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>";
}


seems to be working but looks ugly as sin. would this be the only way to go about it?

#6 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 21 October 2006 - 06:56 AM

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

#7 xfezz

xfezz
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 21 October 2006 - 07:07 AM

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


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

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. ;]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users