Page 1 of 1

How do you find last entry in each Group?

Posted: Mon Jan 19, 2015 9:45 am
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?

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

Posted: Mon Jan 19, 2015 10:08 am
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.

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

Posted: Mon Jan 19, 2015 10:32 am
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.

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

Posted: Mon Jan 19, 2015 11:17 am
by simonmlewis
Do you mean this?

Code: Select all

DATE_SUB()
Not sure really how you mean.

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

Posted: Mon Jan 19, 2015 11:35 am
by Celauran
Yes, exactly that.

Code: Select all

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