Resolved displaying the date/time as today, yesterday or 'date'?

[LJackson]

Hi,

right i am trying to find out how to set the output of the date/time from my database to display today if the date = today or yesterday if the date = yesterdays date, or if other then it displays the date

i also want the time to be displayed as 11.55 am for example

now i did have some help on this before the database was reset to an older date which i am very greatful for but the code i was given wasnt working and i was told there was a simple typo in it which i cannot see

here is the code can anyone spot it
cheers

PHP Code:

$SQL = "
    SELECT
        *,
        CASE datePosted
            WHEN DATE( `datePosted` ) = DATE( NOW() ) THEN 'today'
            WHEN DATE( `datePosted` ) = DATE( DATE_SUB( NOW(), INTERVAL 1 DAY ) ) THEN 'yesterday'
            ELSE
            DATE_FORMAT( `datePosted`, '%b %D, %Y' ) END AS dateString
            FROM `posts` WHERE `secID` = '{$secID[$i]}' ORDER BY `datePosted` DESC"; 


[Fumigator]

What error are you getting?

(edit) I just noticed you need to use END CASE rather than just END. Sorry...

[LJackson]

Hi Fumigator,

i am currently getting an error with the end case piece of code

here is what i have:

PHP Code:

$SQL = "
    SELECT
        *,
        CASE 
            WHEN DATE( `datePosted` ) = DATE( NOW() ) THEN 'today'
            WHEN DATE( `datePosted` ) = DATE( DATE_SUB( NOW(), INTERVAL 1 DAY ) ) THEN 'yesterday'
            ELSE
            DATE_FORMAT( `datePosted`, '%b %D, %Y' ) END CASE AS dateString
            FROM `posts` WHERE `secID` = '{$secID[$i]}' ORDER BY `datePosted` DESC"; 


the error i get is

error 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE AS dateString FROM `posts` WHERE `secID` = 'secBO' ORDER BY `datePos' at line 7

any ideas
cheers

[Fumigator]

Whoops I was wrong about "END CASE", it should be just "END".

We'll figure this out eventually... try removing the date_format() and replacing with a simple string; maybe that's the problem.

[kbluhm]

I cannot just call up a garage and tell them my car is making a funny noise and then expect them to diagnose the problem over the phone.

Please post the error that you receive when running the original query. If you simply tell us "there is an error" then we cannot help you.

[LJackson]

hi kbluhm, sorry i thought that because you were helping me before the database was set back 10 day that you were aware of the error i was having

with the original code

PHP Code:

$SQL = "
    SELECT
        *,
        CASE 
            WHEN DATE( `datePosted` ) = DATE( NOW() ) THEN 'today'
            WHEN DATE( `datePosted` ) = DATE( DATE_SUB( NOW(), INTERVAL 1 DAY ) ) THEN 'yesterday'
            ELSE
            DATE_FORMAT( `datePosted`, '%b %D, %Y' ) 
            END AS dateString
            FROM `posts` WHERE `secID` = '{$secID[$i]}' ORDER BY `datePosted` DESC"; 


i get no date displayed at all, the forum loads up as it should but there is no date, no errors or date

cheers for your help so far kbluhm its much appreciated

[LJackson]

fumigator, i removed the DATE_FORMAT and replaced it with string and i was getting the following error

PHP Code:

error 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `datePosted`, '%b %D, %Y' ) END AS dateString FROM `posts` WHERE `' at line 7 


thanks for spending your time helping me with this i appreciate it
cheers

[kbluhm]

Originally Posted by LJackson

hi kbluhm, sorry i thought that because you were helping me before the database was set back 10 day that you were aware of the error i was having

Sorry, but I don't recall you ever posting the error back then either.

I plugged that query that I gave you yesterday into a table with a DATETIME field and it retrieves fine with no errors.

[LJackson]

oh well i Apologise if i didnt i honestly thought i did lol.

ah thats strange my datePosted field is definatly datetime.

just a thought when i set a varible to display the new date format how do i call it?

at the moment i have,

PHP Code:

            $result = mysql_query($SQL)or die("error 1: " . mysql_error());  ;
            $row = mysql_fetch_array($result);
            $date_rep = $row['formatedDate']; 


i think the formatedDate is the problem, what would i replace that with???

cheers

[kbluhm]

Where does formatedDate ever appear in any of the queries you have posted above?

[LJackson]

umm it doesnt, its still left in from the old code ive spent all this time looking at the actual sql query that i forgot about the rest, oops that'll be why it aint working then?

how would i call the new date?
cheers

[kbluhm]

It's cleverly hidden right in the query...

... AS dateString

[LJackson]

nice one mate your a legend it works,

1 other question, it now says yesterday today etc but how do i get the time displayed at the mo there is no time, i would like the time displayed as 11.55 am or pm. would i need a new query, or can i use this one but add to it

ive managed to get the time to display as i want on the 'other' dates but how do i go about adding it to the today, and yesterday fields

thanks

thanks again

[kbluhm]

PHP Code:

$SQL = "
    SELECT
        *,
        CASE
            WHEN DATE( `datePosted` ) = DATE( NOW() )
                THEN CONCAT( 'Today, ', DATE_FORMAT( `datePosted`, '%l:%i %p' ) )
            WHEN DATE( `datePosted` ) = DATE( DATE_SUB( NOW(), INTERVAL 1 DAY ) )
                THEN CONCAT( 'Yesterday, ', DATE_FORMAT( `datePosted`, '%l:%i %p' ) )
            ELSE
                DATE_FORMAT( `datePosted`, '%b %D, %Y, %l:%i %p' )
            END AS `dateString`
    FROM
        `posts`
    WHERE
        `secID` = '{$secID[$i]}'
    ORDER BY
        `datePosted` DESC
"; 


...or you could just use the original query and format `datePosted` with PHP and append it to `dateString`.

[LJackson]

thank you sooooooooo much lol that works a treat

cheers