PHP Dealing with MySQL DATETIME

Question

I have a table where I am displaying the latest administrator notes. I am only showing the last 3 notes. I am storing the DATETIME in the database. What I want to do is be able to say, if this datetime is less than 3 days old then display this NEW text.

Now if all 3 notes are in the same day, I want only the latest one to show the NEW text, not all 3.

Here is my PHP:

<?php
    if ($stmt = $mysqli->prepare("SELECT note, date_posted, admins.first_name, admins.last_name FROM admin_notes INNER JOIN admins ON admin_notes.admin_id = admins.admin_id ORDER BY date_posted DESC LIMIT 3")) {

        $stmt->execute();
        $stmt->bind_result($note_text, $note_date, $note_fname, $note_lname);
        $stmt->store_result();

        while ($stmt->fetch()) {
            echo "<tr><td class=\"full\">$note_text<br /><span class=\"from\">".timesince($note_date)." by <strong>$note_fname $note_lname</strong></span></td></tr>";
        }
    }
?>

Can anyone help me achieve this?

Does timesince() return New or are you wanting to output this? You're exact question is unclear. — Jason McCreary
– Jason McCreary, Commented Jan 4, 2012 at 21:30

KingCrunch · Accepted Answer · 2012-01-05 07:15:57Z

3

$past = new DateTime('-3 days');

// later
$note_datetime = new DateTime($note_date);

if ($note_datetime >= $past) echo 'new';

Inspired by the (in the meantime deleted) answer of rkosegi: You can change the query to

SELECT note, date_posted, admins.first_name, admins.last_name, (DATEDIFF(NOW(),`date_posted`) < 3) AS isNew FROM [..]

Now you get a new "row" isNew, that is either 1, oder 0.

edited Jan 5, 2012 at 7:15

answered Jan 4, 2012 at 21:31

KingCrunch

133k22 gold badges158 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Drew Over a year ago

Thanks for your answer. I couldn't get the top PHP part to work, it was showing "new" for the last one which was 3 days old, not for the other two which were 1 and 2 days old.

Drew Over a year ago

As for the query, is there a way to only make the latest one show NEW? I don't want more than 1 NEW to show at all times even if they are all less than 3 days old.

KingCrunch Over a year ago

First Comment: obviously it must be >= and not <=. My fault. Second comment: without UNION (which is slightly oversized here) I don't think.

Collectives™ on Stack Overflow

PHP Dealing with MySQL DATETIME

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related