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?
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