PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
1. You can calculate the time difference in SQL with TIMESTAMPDIFF().
2. Regarding GROUP BY, imagine how you would do it manually. Given a list of items to be grouped by K, you would take the each item in order and see if its K matches one you have already found. If K is new, you keep the item. If K is known, you discard the item. So it's the first item found that will be included in the grouped list. To get the last found item, you need to put the list in reverse order before comparing the items. In SQL, you do that with a subquery.
This is quite something - I've added it to the system, just altering minor things, and making the results sortable, and it's very clever indeed.
Using this method, how would I do this sort of query, without the date checks, just to find those where "t.updatedby = 'customer'" ?
ie. Do a GROUP BY, but only where the final row of the "rows in each group" actually have customer in them?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
$query = "
SELECT t.*, TIMESTAMPDIFF(HOUR, t.dateupdated, CURRENT_TIMESTAMP) AS age_hours
FROM (SELECT id FROM tickets WHERE dateclosed IS NULL AND datetrashed IS NULL AND website =:website ORDER BY dateupdated DESC) s
JOIN tickets t ON t.id = s.id
GROUP BY ticket
HAVING t.updatedby = 'customer'
AND age_hours > 48";
$result = $pdo->prepare($query);
$result->execute(array(':website' => $rowweb->website));
$num_rows = $result->rowCount();
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
SELECT t.*, TIMESTAMPDIFF(HOUR, t.dateupdated, CURRENT_TIMESTAMP) AS age_hours
FROM (SELECT * FROM tickets WHERE dateclosed IS NULL AND datetrashed IS NULL ORDER BY dateupdated DESC) s
JOIN tickets t ON t.id = s.id
GROUP BY ticket
HAVING t.updatedby = 'support'
AND age_hours > 168
Now trying to established those tickets where support was the last to update it, but the customer hasn't responded .... 7 days or more old.
There are definitely some in my local system like that, but this query isn't showing them, nor is it erroring.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
It's for a callback service.
Someone enters "15-30 minutes" into a box, I want to add 15 minutes to the time they raised their query ($row->dateraised).
And then calculate the time remaining until the call is due.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
I'm using $minutes_to_add to add that amount of minutes to $row->dateraised into the $stamp time.
And then asking how many between between $stamp and NOW.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
How do I tell if it is a "minus" figure?
I can query "$minutes" to adjust the display accordingly, but how do I say "if it's a minus... enter 'time has now passed' ??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.