How do you find last entry in each Group?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do you find last entry in each Group?

Post by simonmlewis »

So we have a database table for Tickets.
We want to be able to mark tickets as "closed" if the oldest dateupdated field for a ticketID is more than 3 months old, and dateclosed IS NULL.

ID
Ticket
dateraised
dateupdated
dateclosed
updatedby

There could be a dozen rows for the same ticket number, But of course the last one will have the highest ID number.
So I need to find the last row of a ticket number, where it has the highest ID, and where dateclosed IS NOT NULL.

Once I've done that, I can update that ticket and mark it as closed vi a cronjob.

How do I do that first stage tho... to find it?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you find last entry in each Group?

Post by simonmlewis »

It's a hefty way of doing it, but this would highlight them all, and show the days and months.
Then I can just pick out those over 3 months.

The site gets a good amount of use, but not thousands a day, so this may be ok.

Thoughts?

Code: Select all

<?php
include_once "dbconn.php";
$now = time(); // or your date as well

$query = "SELECT * FROM (SELECT ticket, id, dateupdated, updatedby FROM tickets WHERE dateupdated IS NOT NULL AND dateclosed IS NULL AND datetrashed IS NULL ORDER BY id DESC) AS tickets GROUP BY ticket";
$result = $pdo->query($query);
$num_rows = $result->rowCount();
$count = 0;
echo "$num_rows Found<table cellpadding='4'>
<tr><td>Ticket ID</td><td>Date Updated</td><td>ID</td><td>Days</td></tr>";
while ($row = $result->fetch(PDO::FETCH_OBJ)) 
{
  $your_date = strtotime("$row->dateupdated");
  $datediff = $now - $your_date;
echo "<td>$row->ticket</td><td>$row->dateupdated</td><td>$row->id</td><td>";
$days = floor($datediff/(60*60*24));
echo "$days";
$months = $days / 30;

echo " days (";
echo ceil("$months");
echo " months)</td></tr>";
}
echo "</table>";
mysql_close($sqlconn);
?>
I just then tap into this and run a script to handle those i want handled.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do you find last entry in each Group?

Post by Celauran »

You could add one more query, either wrapping the above or using the results of the above, that would use MySQL's date interval functions.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you find last entry in each Group?

Post by simonmlewis »

Do you mean this?

Code: Select all

DATE_SUB()
Not sure really how you mean.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do you find last entry in each Group?

Post by Celauran »

Yes, exactly that.

Code: Select all

...
WHERE DATE_SUB(CURDATE(), INTERVAL 3 MONTH) > dateupdated
Post Reply