Getting a Date from a Database and displaying in PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Slyvampy
Forum Newbie
Posts: 23
Joined: Thu Nov 28, 2002 2:03 am
Location: Yorkshire, England
Contact:

Getting a Date from a Database and displaying in PHP

Post by Slyvampy »

I can't find anything anywhere and its really confusing.
Im still fairly new, but should have already worked this one out.

This is my last resort, before i shoot myself. (spend nearly 3 hours surfing the net to find out why this dont work, some one had a simular problem, but it was never soved.)

right, here goes

i have a mySQL database with a table called news.

here is one of the fields :-

Code: Select all

date_of_news  timestamp(14)
Therefore i am using timestamp to store dates.
Before i go on, the documentation states this is how many seconds have passed or something from 1970 ? am i right? I have to fill this many seconds 00000000000000 (14 to be right).

fair enough it screws up all my dates.

next, i try to get a date out of the database using the following :-

Code: Select all

$returned = mysql_query("SELECT * FROM news ORDER BY date_of_news DESC LIMIT 0, 3", $connection) or die("Search Failed, please try again later");

$returned_rows = mysql_num_rows($returned);

$handled = 0;

if ($returned_rows != 0)
{
while ($handled < $returned_rows)
&#123;
$row = mysql_fetch_row($returned);
$news_title = $row&#1111;0];
$db_date_of_news = $row&#1111;1];
$details   = $row&#1111;2];
$details_large   = $row&#1111;3];
See Below

Code: Select all

$handled++;
&#125;
&#125;
This was on the php home page about getdate
$today = getdate($db_date_of_news);
$month = $today['mon'];
$mday = $today['mday'];
$year = $today['year'];
All i want is to store a date in the mySql database that i can sort from past to present, and a way of displaying them in a format of my choice.

Anyone help ?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

MySQL's TIMESTAMP field type does not use UNIX timestamp format, that is it stores the dates in the format YYYYMMDDHHMMSS instead as the number of seconds since 1970. This means you can't use PHP's date functions to manipulate this timestamp as though it was a UNIX timestamp.
http://www.mysql.com/doc/en/DATETIME.html

The best thing to do would be to use MySQL's date and time functions to format the timestamp so that when you do a SELECT you can decide then how you would like to get the data.
http://www.mysql.com/doc/en/Date_and_ti ... tions.html

For instance you could use DATE_FORMAT() or if you wanted to get a UNIX timestamp from the MySQL timestamp you could use UNIX_TIMESTAMP().

For example try something like:

Code: Select all

SELECT DATE_FORMAT(date_of_news, '%d/%m/%Y') AS date_of_news, news_title, details, details_large FROM news ORDER BY date_of_news DESC LIMIT 0, 3
You may also want to consider using mysql_fetch_assoc() instead of mysql_fetch_row() in order to be able to reference the different results by their field name instead of having to know for sure which order the fields are in the database in order to reference them by number, e.g. you could change:

Code: Select all

while ($handled < $returned_rows) 
{ 
$row = mysql_fetch_row($returned); 
$news_title = $row[0]; 
$db_date_of_news = $row[1]; 
$details   = $row[2]; 
$details_large   = $row[3];
// more code
$handled++; 
}
to

Code: Select all

while ($row = mysql_fetch_assoc($returned)) { 
    $news_title = $row['news_title']; 
    $db_date_of_news = $row['date_of_news']; 
    $details   = $row['details']; 
    $details_large   = $row['details_large'];
    // more code
}
of course I am guessing the names of your database fields.

Mac
User avatar
Slyvampy
Forum Newbie
Posts: 23
Joined: Thu Nov 28, 2002 2:03 am
Location: Yorkshire, England
Contact:

A very big thank you.

Post by Slyvampy »

You dont know how long i spent on doing this.

cheers,

what i did was :lol:

Code: Select all

$returned = mysql_query("SELECT DATE_FORMAT(date_of_news, '%d-%m-%Y') AS date_of_news, news_title, details, details_large FROM news ORDER BY date_of_news DESC LIMIT 0, 3", $connection) or die("Search Failed, please try again later");
and then just echoed it out.

you are a star!

:D Thanks again.

-SteJ.
Post Reply