[SOLVED] Editing date output from MySQL

[Andy17]

Hey guys!

 

I am currently storing my dates in a VARCHAR field in my MySQL database, which is bad because it slows down my queries. I would like my dates to output in the following format:

 

[Month], [Date] [Year]

Example: October 14, 2008

 

So, I will change my column to DATE instead of VARCHAR. If anyone could write the few lines of code on how to do this (PHP date code + modifying the MySQL output), I would very much appreciate it.

 

Thank you in advance!

[MasterACE14]

you can keep the database column as VARCHAR, and use the PHP time(); function to insert the current time and date into the column, then select that column and use the PHP date(); function to display it how you want.

[Andy17]

Sorry, I forgot to mention that I do not want the current date, but the date when some content was submitted. So, I want the date to stay the same forever.

[PFMaBiSmAd]

No slow parsed/tokenized/interpreted php code is necessary. Use the mysql DATE_FORMAT() function in your SELECT query to retrieve a DATE data type in any format you want - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

 

 

[Andy17]

I saw that before but I understand absolutely NOTHING of that (that is why I asked for code). I have the following stored in a DATE field in my database:

 

2008-10-14

 

Let's say I want to make that display like this:

 

October 14, 2008

 

With the following query:

 

<?php

$sql1 = "SELECT * FROM jokes WHERE category = 'Adult' LIMIT {$number}, 1";
$result = mysql_query($sql1);

if ($result)

{

$row = mysql_fetch_array($result);
   // I want to echo the date here

}

?>

 

How would I do that?

 

Thank you.

[Maq]

I think this will work:

 

$row['date'] = date("F j, Y");
echo $row['date'];

[GingerRobot]

I saw that before but I understand absolutely NOTHING of that (that is why I asked for code).

 

Did you even try? There's examples given on that page too.

 

 

[Maq]

It's pretty cut and dry.  If you go to PHP.net it shows/explains plenty of examples and snippets of code you can use...  Anyway, does it work now?

[Andy17]

I think this will work:

 

<?php
$row['date'] = date("F j, Y");
echo $row['date'];

 

That always sets the date to the present date. I have dates stored in my database that are in the past that I would still like shown like that. For example, if I have 2008-10-11 stored, that would result in the following output with your code:

 

October 14, 2008

 

When it should be: "October 11, 2008".

 

Did you even try? There's examples given on that page too.

 

I actually did, I just think I completely misunderstood how to do it.

[Maq]

Right sorry, try this:

 

$output = date("F j, Y", strtotime($row['date']));
echo $output;

[discomatt]

Andy, you want something like this

 

2008-10-14

 

Let's say I want to make that display like this:

 

October 14, 2008

 

$sql1 = "SELECT DATE_FORMAT(`dateColumn`, '%M %e, %Y') as `dateColumn` FROM jokes WHERE category = 'Adult' LIMIT {$number}, 1";

[PFMaBiSmAd]

strtodate() and date() are two of the slower php functions. Strtodate() parses the date string and then uses the mktime() function internally to convert that date in the current time zone into a Unix timestamp. The date() function then takes the converted Unix timestamp and converts it back into a date string taking into account the current time zone. Why do all of that when you can just format the DATE directly in the SELECT query using the mysql DATE_FORMAT() function, which simply takes the fields of the DATE and formats them, without performing two time consuming conversions to get the same result.

[discomatt]

strtotime()*** Other than that... yes

[Maq]

I don't know to tell you the truth.  I'm still learning myself.  I posted a suggestion that worked.  I know he may use this in the future with more than 1 record but with 1 record you won't be able to tell the difference.

[Andy17]

Andy, you want something like this

 

2008-10-14

 

Let's say I want to make that display like this:

 

October 14, 2008

 

$sql1 = "SELECT DATE_FORMAT(`dateColumn`, '%M %e, %Y') as `dateColumn` FROM jokes WHERE category = 'Adult' LIMIT {$number}, 1";

 

I am just wondering where the "SELECT * FROM" went. I mean, I'm getting no result with that query and it just looks strange to me, cause what is it actually selecting? Isn't it just changing the date format? Sorry if I'm missing something here.

 

but with 1 record you won't be able to tell the difference.

 

I will also need to use this query to get a lot more records. But it's the same, just by using a while loop.

[Maq]

I will also need to use this query to get a lot more records. But it's the same, just by using a while loop.

 

It will be the same result but a lot more expensive.

[discomatt]

Using several lines of code over the MySQL solution adds clutter and unnecessary code.

There's no reason to code inefficiently -> If you don't want to expand and learn, why bother coding?

It doesn't matter if its a single iteration, or a million... bad code is bad code

[Maq]

There's no reason to code inefficiently -> If you don't want to expand and learn, why bother coding?

It doesn't matter if its a single iteration, or a million... bad code is bad code

 

Yes, I understand it's bad code, anyone else want to rub it in?   I guarantee that I will never make that mistake again.

[discomatt]

I am just wondering where the "SELECT * FROM" went. I mean, I'm getting no result with that query and it just looks strange to me, cause what is it actually selecting? Isn't it just changing the date format? Sorry if I'm missing something here.

 

It's okay, I'm not doing to bite your head off for asking questions... but I do expect you to do a little hunting on your own. I'll guide you, but I'm not gonig to hold your hand

 

First, it's never recommended to use SELECT *... this forces the MySQL engine to grab a list of columns before querying, slowing things down a little. You also RARELY use every column in a table, so using SELECT * returns quite a bit of data that never gets used.

 

Now, to DATE_FORMAT. First, read this below

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

 

That's the syntax for DATE_FORMAT... it's very similar to date() in PHP, only you insert the MySQL timestamp as the first argument, instead of the second.

 

In the snippet I gave you, replace `dateColumn` with the name of your date column the 'as' part returns it as if it had that column name... which makes life easier when throwing it into an associative array

[discomatt]

Yes, I understand it's bad code, anyone else want to rub it in?   I guarantee that I will never make that mistake again.

 

I didn't mean to make fun... you just tried to justify using it I argue that there's no justification

 

It's not a mistake... it's just not the ideal way to do it. That's the point of asking questions in a forum like this. With collective responses, the 'best' solution can be worked out I don't expect anyone to code perfectly, but I do expect them to have an open mind and be ready to learn

[Maq]

I didn't mean to make fun... you just tried to justify using it I argue that there's no justification

 

It's not a mistake... it's just not the ideal way to do it. That's the point of asking questions in a forum like this. With collective responses, the 'best' solution can be worked out I don't expect anyone to code perfectly, but I do expect them to have an open mind and be ready to learn

 

Point taken... 

[Andy17]

First, it's never recommended to use SELECT *... this forces the MySQL engine to grab a list of columns before querying, slowing things down a little. You also RARELY use every column in a table, so using SELECT * returns quite a bit of data that never gets used.

 

Yeah, I have figured this out lately so I optimized all of my queries; just in this case, I actually do need all of the columns.

 

Now, to DATE_FORMAT. First, read this below

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

 

I have looked it over several times but I still fail to understand it. It looks like Russian to me. I have done a lot of reading that have solved many of my problems, but sometimes I learn way more from asking here rather than reading a manual (I don't just c/p the code I am provided here).

 

In the snippet I gave you, replace `dateColumn` with the name of your date column the 'as' part returns it as if it had that column name... which makes life easier when throwing it into an associative array

 

Okay, now I'm "slightly" embarrassed that I didn't even think of that. The date is converted successfully and it is displayed on the page, but now my problem is how to also select the rest of the columns (submitter, title, joke).

[discomatt]

You could use

 

SELECT *, DATE_FORMAT(`dateColumn`, '%M %e, %Y') as `dateColumn` FROM...

 

Though this will return redundant data, because you're never actually using your MySQL timestamp.

 

You're best off listing every column you need in your query... but the above solution will work.

[Andy17]

Smooth, simply smooth. It works perfectly now.

 

Thank you so much for your help; learned a few tricks from it. Also thanks to Maq.

 

I very much appreciate it!

[Maq]

Thank you so much for your help; learned a few tricks from it. Also thanks to Maq.

 

No, thank you...