[SOLVED] DATE_FORMATing Help

Taorluath · May 22, 2008

So here's the deal:

I have a blog database that I made myself. The post date is included as a field in my database. It's stored in the usual YYY-MM-DD format. I want it to be displayed easier to read, like: Jan 2nd, 2008. I'm trying to get DATE_FORMAT() to make it look better, but I can't seem to get it to work.

Here's my code:

$sql = "SELECT id, poster, DATE_FORMAT(post_date, '%b %D, %Y'), post_name, post_text, current_location FROM blog_posts ORDER BY post_date";
$res = mysqli_query($mysqli, $sql);

post_date is the field that has the date stored in it, btw.

WHat's going wrong?

Barand · May 22, 2008

Better to use a column alias

SELECT id, poster, DATE_FORMAT(post_date, '%b %D, %Y') as displayDate, ...

then

echo $row['displayDate'];

Taorluath · May 23, 2008

Wow! Thanks, that works great!

I'm a just novice Mysql user, could you tell me where can i learn tricks like that?

cowboysdude · May 31, 2008

Better to use a column alias

SELECT id, poster, DATE_FORMAT(post_date, '%b %D, %Y') as displayDate, ...

then

echo $row['displayDate'];

I think all of this is awesome and I've been trying to get something...ANYTHING to work..lol and I gotta tell you I'm about at the end of my rope..

PLEASE tell me..

Date_Format($registerDate, ''%b %D, %Y') as displayDate

echo $row['displayDate'];

for some reason that or anything else i try to do is working it just gives me errors and/or displays dates like this -> 2008-05-05 20:23:32

I cannot get it to work ... Question

would this work-

$registerDate="Date_Format($registerDate, ''%b %D, %Y') as displayDate"

echo $row['displayDate'];

...

Thank you!!!

AndyB · May 31, 2008

@cowboydude

SELECT id, poster, DATE_FORMAT(the_name_of_your_post_date_field, '%b %D, %Y') as displayDate, ...

Notice there's no dollar symbol in the name of the posst date field (whatever it's called)

Barand · May 31, 2008

Question

would this work-

$registerDate="Date_Format($registerDate, ''%b %D, %Y') as displayDate"

echo $row['displayDate'];

...

Thank you!!!

No.

DATE_FORMAT() is a MySQL function and is only going to work inside a MySQL query.

If you want to format a date in PHP use date() function (www.php.net/date)

cowboysdude · May 31, 2008

Question

would this work-

$registerDate="Date_Format($registerDate, ''%b %D, %Y') as displayDate"

echo $row['displayDate'];

...

Thank you!!!

No.

DATE_FORMAT() is a MySQL function and is only going to work inside a MySQL query.

If you want to format a date in PHP use date() function (www.php.net/date)

This is why I'm having a great deal of trouble...

I need to format the date from 2008-05-05 23:23:12 to 05/05/2008

I have tried the $query format but it will not return correctly...

Can you give me a hint or clue here...

Here is what I'm using for output in php

User Since: <?php echo $registerDate;?>

I can get the $registerDate info but for the life of me cannot figure out how to write the correct mysql query to format it..believe me I've been trying for 3 days and looking at everything I can find to read and still I just cannot get this..

Barand · May 31, 2008

I need to format the date from 2008-05-05 23:23:12 to 05/05/2008

is that mm/dd/yyyy or dd/mm/yyyy ?

cowboysdude · May 31, 2008

I need to format the date from 2008-05-05 23:23:12 to 05/05/2008

is that mm/dd/yyyy or dd/mm/yyyy ?

I would like to show it as dd/mm/yyyy

Barand · May 31, 2008

METHOD A

<?php
$sql = "SELECT DATE_FORMAT(registerDate, '%d/%m/%Y') as registerDate FROM tablename";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res))
{
echo $row['registerDate'], '<br />';
}
?>

METHOD B

<?php 
$sql = "SELECT registerDate FROM tablename";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res))
{
echo date('d/m/Y', strtotime($row['registerDate'])), '<br />';
}

?>

cowboysdude · May 31, 2008

METHOD A

<?php
$sql = "SELECT DATE_FORMAT(registerDate, '%d/%m/%Y') as registerDate FROM tablename";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res))
{
echo $row['registerDate'], '<br />';
}
?>

METHOD B

<?php 
$sql = "SELECT registerDate FROM tablename";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res))
{
echo date('d/m/Y', strtotime($row['registerDate'])), '<br />';
}

?>

Well BINGO... that worked.. now I have to 'tweak' to just show the record for the user logging in.. right now it's showing me all the registration dates for all my users..lol

cowboysdude · June 1, 2008

METHOD A
<?php
$sql = "SELECT DATE_FORMAT(registerDate, '%d/%m/%Y') as registerDate FROM tablename";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res))
{
echo $row['registerDate'], ' ';
}
?>
METHOD B
<?php 
$sql = "SELECT registerDate FROM tablename";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res))
{
echo date('d/m/Y', strtotime($row['registerDate'])), ' ';
}

?>
Well BINGO... that worked.. now I have to 'tweak' to just show the record for the user logging in.. right now it's showing me all the registration dates for all my users..lol

NOW.. how about this?

$sql = "SELECT DATE_FORMAT(registerDate, '%m/%d/%Y') as registerDate FROM jos_users";

$res = mysql_query($sql);

while ($row = mysql_fetch_assoc($res))

{

echo $row['registerDate'], ' ';

}

adding this to the end of $sql - WHERE user=$usr_id";

I am able to capture the userid now but so I can show ONLY the record for the user logging in... I'm gonna lose my mind here.. learning is VERY hard..lol

The date is formatting correctly Thanks to the MAJOR help with the above script but it is grabbing all the registerdates in the database and printing them out...

I'm think using the $USER_ID would help me isolate only the user who is logged in...

am I on the right track here?

Barand · June 1, 2008

if $usr_id contains the id you want to query then, yes...

<?php
$sql = "SELECT DATE_FORMAT(registerDate, '%m/%d/%Y') as registerDate 
          FROM jos_users
          WHERE user = '$usr_id' ";

EDIT If $usr_id is a number then the single quotes are optional

cowboysdude · June 1, 2008

if $usr_id contains the id you want to query then, yes...
<?php
$sql = "SELECT DATE_FORMAT(registerDate, '%m/%d/%Y') as registerDate 
          FROM jos_users
          WHERE user = '$usr_id' ";
EDIT If $usr_id is a number then the single quotes are optional

Ok now I think I'm looking at this all wrong... your script did exactly what it was told to do.. get $registerDate and it does.. now when I try to narrow it down by adding the $usr_id it gives me an error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

I tried it both with quotes and without quotes... I think I'm making this WAY to hard and that's why I can't figure it out...

I need to get the user..then get his/her register date format it and show it. The correct format for user is $user in the database...

WOW...

cowboysdude · June 1, 2008

if $usr_id contains the id you want to query then, yes...
<?php
$sql = "SELECT DATE_FORMAT(registerDate, '%m/%d/%Y') as registerDate 
          FROM jos_users
          WHERE user = '$usr_id' ";
EDIT If $usr_id is a number then the single quotes are optional
Ok now I think I'm looking at this all wrong... your script did exactly what it was told to do.. get $registerDate and it does.. now when I try to narrow it down by adding the $usr_id it gives me an error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

I tried it both with quotes and without quotes... I think I'm making this WAY to hard and that's why I can't figure it out...

I need to get the user..then get his/her register date format it and show it. The correct format for user is $user in the database...

WOW...

I'm sorry here is the entire package...

<?php

$user =& JFactory::getUser();

$usr_id = $user->get('id');

echo $usr_id;

$sql = "SELECT DATE_FORMAT(registerDate, '%m/%d/%Y') as registerDate

FROM jos_users

WHERE user = $usr_id";

$res = mysql_query($sql);

while ($row = mysql_fetch_assoc($res))

{

echo $row['registerDate'], ' ';

}

?>

Barand · June 1, 2008

Find out what the error is. Use

$res = mysql_query($sql) or die(mysql_error());

cowboysdude · June 1, 2008

Find out what the error is. Use
$res = mysql_query($sql) or die(mysql_error());

$sqlc = 'SELECT `data` FROM `jos_session`';

$resc = mysql_query($sqlc) or die(mysql_error());

while ($row = mysql_fetch_assoc($resc))

{

echo $row['data'], ' ';

}

I am trying to do this all wrong I believe.. there's a table called jos_session and it stores the info for the logged in user...

I ran the above but used 'data' and I got this in return:

s:2:"id";s:2:"62";s:4:"name";s:11:"Cowboysdude";s:8:"username";s:11:"cowboysdude";s:5:"email";s:23:"hidden@hidden;s:12:"registerDate";s:19:"2008-05-05 20:28:22";

I believe the numbers are id numbers in that field... That's the info I'm after..

Many Thanks!!!

Sign In

[SOLVED] DATE_FORMATing Help

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information