Page 1 of 1
Formatting Dates
Posted: Thu Aug 07, 2008 8:37 pm
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'].'
Re: Formatting Dates
Posted: Thu Aug 07, 2008 9:01 pm
by CoolAsCarlito
Forgot to add what I put in the SELECT field.
$query = "SELECT *, DATE_FORMAT('datewon', '%M %e, %Y') FROM titles";
Re: Formatting Dates
Posted: Fri Aug 08, 2008 6:18 am
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".
Re: Formatting Dates
Posted: Fri Aug 08, 2008 11:17 am
by CoolAsCarlito
I'm lost.
Re: Formatting Dates
Posted: Fri Aug 08, 2008 11:42 am
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?
Re: Formatting Dates
Posted: Fri Aug 08, 2008 11:56 am
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
Re: Formatting Dates
Posted: Fri Aug 08, 2008 12:04 pm
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
Re: Formatting Dates
Posted: Fri Aug 08, 2008 12:10 pm
by CoolAsCarlito
Still didn't work.
Re: Formatting Dates
Posted: Fri Aug 08, 2008 12:38 pm
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.
Re: Formatting Dates
Posted: Fri Aug 08, 2008 12:38 pm
by ghurtado
Can you share the code for the whole page?
Re: Formatting Dates
Posted: Fri Aug 08, 2008 12:41 pm
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> <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>
Re: Formatting Dates
Posted: Fri Aug 08, 2008 12:45 pm
by ghurtado
Do as EverLearning proposed, you need to change your query a little more.
Re: Formatting Dates
Posted: Fri Aug 08, 2008 12:51 pm
by CoolAsCarlito
Thank you EverLearning what you told me to do did work. I appreciate your help.