If your data is as you portrayed it above, that won't happen. It will return only the row with the highest ID for each ticket. You check the value of updatedby after the fact and decide whether or not to display it.simonmlewis wrote:For example, if it finds $row->updatedby to be "admin", but the last one is actually "customer"....
How do I check hours between two date/times ?
Moderator: General Moderators
Re: How do I check hours between two date/times ?
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I check hours between two date/times ?
So is it not possible to put it in the way I did in my pseudocode then?
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 I check hours between two date/times ?
I'll stop short of saying it's not possible -- I'm sure there's a way -- but it's probably more trouble than it's worth.
Re: How do I check hours between two date/times ?
The way you've written it in your pseudocode, given this:
6 would be returned because that is the max ID for that ticket with where updatedby = customer. That's not what you want.Ticket: 123 ID: 5 Updatedby: admin
Ticket: 123 ID: 6 Updatedby: customer
Ticket: 123 ID: 7 Updatedby: admin
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I check hours between two date/times ?
So how would I get 7 ?
I thank you for being honest about personally being unsure how to do this, but surely there is a way.
"SELECT LAST ROW ONLY WHERE updatedby = 'customer' AND dateupdated IS NOT NULL".
The thing is, we will end up with thousands of entries, and will end up needing "page numbers".
So I cannot selected the lot, and use PHP to calculate it, as right now each row is a 'group'.
It shows the last time it was updated, last "updatedby" etc. Easy for New Tickets, because of how I have done that part (mentioned in another thread).
But how do I possibly query to find messages that haven't been been responded to by admin, because it was last updated by customer?
And then get that dateupdated and query it as you have suggested.
I thank you for being honest about personally being unsure how to do this, but surely there is a way.
"SELECT LAST ROW ONLY WHERE updatedby = 'customer' AND dateupdated IS NOT NULL".
The thing is, we will end up with thousands of entries, and will end up needing "page numbers".
So I cannot selected the lot, and use PHP to calculate it, as right now each row is a 'group'.
It shows the last time it was updated, last "updatedby" etc. Easy for New Tickets, because of how I have done that part (mentioned in another thread).
But how do I possibly query to find messages that haven't been been responded to by admin, because it was last updated by customer?
And then get that dateupdated and query it as you have suggested.
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 I check hours between two date/times ?
Code: Select all
$query = "SELECT subject, ticket, dateupdated, updatedby FROM tickets WHERE datetrashed IS NULL AND dateclosed IS NULL AND dateupdated IS NOT NULL ORDER BY id";
$result = $pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_OBJ))
{
$start_date = new DateTime($row->dateupdated);
$end_date = new DateTime();
$interval = $end_date->diff($start_date);
$hours = (24 * $interval->d) + $interval->h;
if ($row->updatedby == "customer")
{
echo "$row->ticket $hours<br/>";
}
}But if there are thousands of entries, this will over sometime, slow the page loading down.
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 I check hours between two date/times ?
You have more information about how things are set up than I do, obviously, but I don't see that working well. There's no MAX, no grouping, so you'll get multiple rows returned for any given ticket. Say you have a ticket with eleven entries, alternating between the customer and the admin; the first one will be ignored, all the entries from admin will be returned but not displayed, and all the remaining entries from the client -- five of them -- will be displayed.
Re: How do I check hours between two date/times ?
What about this?
Code: Select all
SELECT MAX(id) AS id, ticket, subject, dateupdated, updatedby
FROM tickets
WHERE datetrashed IS NULL
AND dateclosed IS NULL
AND dateupdated IS NOT NULL
GROUP BY ticket
HAVING updatedby = 'customer'-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I check hours between two date/times ?
What about:
As won't yours show Customer as updatedby, even if that last row of 3, 4, 5, has customer as only 4 and admin as 5 ??
Code: Select all
$query = "SELECT subject, ticket, dateupdated, updatedby FROM (SELECT * FROM tickets WHERE datetrashed IS NULL AND dateclosed IS NULL AND dateupdated IS NOT NULL AND openingquestion IS NULL ORDER BY id DESC) AS tickets GROUP BY ticket";
$result = $pdo->query($query);
$count = 0;
while ($row = $result->fetch(PDO::FETCH_OBJ))
{
$start_date = new DateTime($row->dateupdated);
$end_date = new DateTime();
$interval = $end_date->diff($start_date);
$hours = (24 * $interval->d) + $interval->h;
if ($row->updatedby == "customer" && $hours >= "48")
{
$count ++;
}
}
if ($count > 0)
{
do script
}
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 I check hours between two date/times ?
Ohhh what is that "HAVING..." bit of the code???
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 I check hours between two date/times ?
That code isn't producing anything, yet I have one ticket with three rows for it. and the MAX ID of it is the one where updatedby = 'customer'.
Surely that should be showing??
[text]MySQL returned an empty result set (i.e. zero rows). (Query took 0.0019 sec)[/text]
Surely that should be showing??
Code: Select all
SELECT MAX( id ) AS id, ticket, subject, dateupdated, updatedby
FROM tickets
WHERE datetrashed IS NULL
AND dateclosed IS NULL
AND dateupdated IS NOT NULL
GROUP BY ticket
HAVING updatedby = 'customer'
LIMIT 0 , 30Love 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 I check hours between two date/times ?
Stranger and stranger.
So this code:
I thought would show the very last entry, whatever it may be.
But it shows the last ID (97), but not the contents of updatedby in that id - it shows the updatedby as 'admin' from 96.
Work that one out!!!!!
The version you game me, doesn't pull up anything.
So this code:
Code: Select all
SELECT MAX( id ) AS id, ticket, subject, dateupdated, updatedby
FROM tickets
WHERE datetrashed IS NULL
AND dateclosed IS NULL
AND dateupdated IS NOT NULL
GROUP BY ticket
LIMIT 0 , 30But it shows the last ID (97), but not the contents of updatedby in that id - it shows the updatedby as 'admin' from 96.
Work that one out!!!!!
The version you game me, doesn't pull up anything.
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 I check hours between two date/times ?
I'm out of ideas, then. I still maintain it's likely easier to do as I described much earlier and filter admin/customer in PHP. I'll leave this for someone else.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I check hours between two date/times ?
Why would your code produce nothing, when the highest ID in that ticket group, 97, is "customer"??
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 I check hours between two date/times ?
ticket 16115
95 > dateupdated is not null > datetrashed is null > dateclosed is null > updatedby = 'customer'.
96 > dateupdated is not null > datetrashed is null > dateclosed is null > updatedby = 'admin'.
97 > dateupdated is not null > datetrashed is null > dateclosed is null > updatedby = 'customer'.
Yes it's producing nothing at all. surely it should be producing it, based on row 97?!
Even this produces nothing, and I am giving it the answer of the ticket!!
I should apologise as this ticket is now more based on another thread on I have put on here - not about hours between times.
95 > dateupdated is not null > datetrashed is null > dateclosed is null > updatedby = 'customer'.
96 > dateupdated is not null > datetrashed is null > dateclosed is null > updatedby = 'admin'.
97 > dateupdated is not null > datetrashed is null > dateclosed is null > updatedby = 'customer'.
Yes it's producing nothing at all. surely it should be producing it, based on row 97?!
Even this produces nothing, and I am giving it the answer of the ticket!!
Code: Select all
SELECT MAX(id) AS id, ticket, subject, dateupdated, updatedby, datetrashed, dateclosed
FROM tickets
WHERE datetrashed IS NULL
AND ticket = '16115'
AND dateclosed IS NULL
AND dateupdated IS NOT NULL
GROUP BY ticket
HAVING updatedby = 'customer'Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.