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?
How do you find last entry in each Group?
Moderator: General Moderators
-
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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
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?
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?
I just then tap into this and run a script to handle those i want handled.
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);
?>Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do you find last entry in each Group?
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?
Do you mean this?
Not sure really how you mean.
Code: Select all
DATE_SUB()Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do you find last entry in each Group?
Yes, exactly that.
Code: Select all
...
WHERE DATE_SUB(CURDATE(), INTERVAL 3 MONTH) > dateupdated