Formatting Dates

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
CoolAsCarlito
Forum Contributor
Posts: 192
Joined: Sat May 31, 2008 3:27 pm
Contact:

Formatting Dates

Post by CoolAsCarlito »

I'm trying to format my date in a database table. Is there a way to do it in the mysqladmin or how do you have to do? It appears right now like 2008-08-08 but I want it to appear like July 8, 2008

I have it in my php code as:

<p><b>Date Won</b><br>'.$row['datewon'].'
CoolAsCarlito
Forum Contributor
Posts: 192
Joined: Sat May 31, 2008 3:27 pm
Contact:

Re: Formatting Dates

Post by CoolAsCarlito »

Forgot to add what I put in the SELECT field.

$query = "SELECT *, DATE_FORMAT('datewon', '%M %e, %Y') FROM titles";
pkbruker
Forum Commoner
Posts: 32
Joined: Sun Aug 03, 2008 9:36 am
Location: Oslo, Norway

Re: Formatting Dates

Post by pkbruker »

Don't format the date in the table. Format it in the output. Either:

1. Use PHP's time()
Use PHP's time() function to get the time and store it as a BIGINT value in the database. Use PHP's date() function to format the date/time when it's outputted. Using this makes it really easy to do queries including time (you can use < > - + and so on the dates in the queries).

2. User DATETIME
Use SQL's DATETIME.

If you absolutely wanna see the date as "July 8, 2008", use VARCHAR, and store a string representation (i.e. return value of PHP's date() function) in addition to the BIGINT value. You'll have one value for you to read and another value for your scripts to use. Your script don't wanna work on dates stored as "July 8, 2008".
CoolAsCarlito
Forum Contributor
Posts: 192
Joined: Sat May 31, 2008 3:27 pm
Contact:

Re: Formatting Dates

Post by CoolAsCarlito »

I'm lost.
User avatar
ghurtado
Forum Contributor
Posts: 334
Joined: Wed Jul 23, 2008 12:19 pm

Re: Formatting Dates

Post by ghurtado »

A BIGINT column is a very ill conceived solution for this. Please only use DATETIME, TIMESTAMP, TIME or DATE for a column which is supposed to store a date.

You are on the right track with formatting the date as it comes out, in your select statement. What happens when you run the query you posted?
CoolAsCarlito
Forum Contributor
Posts: 192
Joined: Sat May 31, 2008 3:27 pm
Contact:

Re: Formatting Dates

Post by CoolAsCarlito »

Okay I changed it to DATE for my format in my phpmyadmin and when I go to the page where it shows the date it looks like this:

Date Won
2008-08-08

I want it to be July 8, 2008 but if that's not at all possible then I'll be okay with just 08-08-2008
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Formatting Dates

Post by EverLearning »

CoolAsCarlito wrote:$query = "SELECT *, DATE_FORMAT('datewon', '%M %e, %Y') FROM titles";
Your query should give you what you want, just don't enclose the field name in single quotes, MySql interprets it as a string, not a field name. Use backticks ``.

Code: Select all

$query = "SELECT *, DATE_FORMAT(`datewon`, '%M %e, %Y') FROM titles
 
CoolAsCarlito
Forum Contributor
Posts: 192
Joined: Sat May 31, 2008 3:27 pm
Contact:

Re: Formatting Dates

Post by CoolAsCarlito »

Still didn't work.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Formatting Dates

Post by EverLearning »

I guess you're still echoing the $row['datewon']. That will display the unformatted date. Try this

Code: Select all

$query = "SELECT *, DATE_FORMAT(`datewon`, '%M %e, %Y') as formatted_date FROM titles";
//... and later in the code echo it
echo $row['formatted_date'];
 
If you still have trouble, you'll need to post a little more of your code for us to understand what the problem is.
User avatar
ghurtado
Forum Contributor
Posts: 334
Joined: Wed Jul 23, 2008 12:19 pm

Re: Formatting Dates

Post by ghurtado »

Can you share the code for the whole page?
CoolAsCarlito
Forum Contributor
Posts: 192
Joined: Sat May 31, 2008 3:27 pm
Contact:

Re: Formatting Dates

Post by CoolAsCarlito »

Code: Select all

 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Outlaw Title History</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
 
<body bgcolor="black" text="white" link="red" vlink="red">
 
<?php
// Connects to your Database
$link = mysql_connect("?", "?", "?") or die(mysql_error());
mysql_select_db("?",$link) or die(mysql_error());
 
if (!mysql_select_db("?", $link)) {
echo 'Could not select database';
exit;
}
//Define the query
$query = "SELECT *, DATE_FORMAT(`datewon`, '%M %e, %Y') FROM titles";
 
if ($r = mysql_query ($query)){ // Run the query. 
 
// Retrieve and print every record
while ($row = mysql_fetch_array ($r)){
print '<table border=0 width=575>';
print '<tr><td background="images/bg_bar3.gif" height=35 colspan=3>&nbsp;<a href="title.php?id=">'.$row['titlename'].'</td></tr>';
print '<tr><td valign=top width=200><a href=titlehistories.php?id=' . $row['id'] . ' title="View KOW '.$row['titlename'].' History"><img src="/images/' . $row['titleimage'] . '" width=200 height=200 border=0 alt="View KOW '.$row['titlename'].' History"></a></td>';
print '<td valign=top width=160><a href="bio.php?' . $row['champion'] . '" title="View History for ' . $row['champion'] . '"><img src=/images/' . $row['champion'] . ' height=200 width=160 border=0></a></td>';
print '<td valign=top><b>Current Champion</b><br><a href="bio.php?' . $row['champion'] . '"><b>' . $row['champion'] . '</b></a><p><b>Date Won</b><br>'.$row['datewon'].'<p><b>Contenders<br>1. <a href="bio.php?' . $row['contender1'] . '">' . $row['contender1'] . '</a><br>2. <a href="bio.php?' . $row['contender2'] . '">' . $row['contender2'] . '</a><br>3. <a href="bio.php?' . $row['contender3'] . '">' . $row['contender3'] . '</a><br></b></td></tr>';
print '</table>';
print '<img src=images/spacer.gif>';
print '</table>';
}
 
} else {
die ('<p>Could not retrieve the data because <b>' . mysql_error() . '</b>. The query was $query.</p>');
} //End of query IF 
?>
</body>
</html>
 
 
User avatar
ghurtado
Forum Contributor
Posts: 334
Joined: Wed Jul 23, 2008 12:19 pm

Re: Formatting Dates

Post by ghurtado »

Do as EverLearning proposed, you need to change your query a little more.
CoolAsCarlito
Forum Contributor
Posts: 192
Joined: Sat May 31, 2008 3:27 pm
Contact:

Re: Formatting Dates

Post by CoolAsCarlito »

Thank you EverLearning what you told me to do did work. I appreciate your help.
Post Reply