Page 2 of 4

Re: How do I check hours between two date/times ?

Posted: Wed May 28, 2014 11:53 am
by Celauran
simonmlewis wrote:For example, if it finds $row->updatedby to be "admin", but the last one is actually "customer"....
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.

Re: How do I check hours between two date/times ?

Posted: Wed May 28, 2014 12:14 pm
by simonmlewis
So is it not possible to put it in the way I did in my pseudocode then?

Re: How do I check hours between two date/times ?

Posted: Wed May 28, 2014 12:18 pm
by Celauran
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 ?

Posted: Wed May 28, 2014 12:20 pm
by Celauran
The way you've written it in your pseudocode, given this:
Ticket: 123 ID: 5 Updatedby: admin
Ticket: 123 ID: 6 Updatedby: customer
Ticket: 123 ID: 7 Updatedby: admin
6 would be returned because that is the max ID for that ticket with where updatedby = customer. That's not what you want.

Re: How do I check hours between two date/times ?

Posted: Thu May 29, 2014 3:09 am
by simonmlewis
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.

Re: How do I check hours between two date/times ?

Posted: Thu May 29, 2014 3:25 am
by simonmlewis

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/>";
        }
}
This works. I changed a date and it shows it correctly.
But if there are thousands of entries, this will over sometime, slow the page loading down.

Re: How do I check hours between two date/times ?

Posted: Thu May 29, 2014 6:35 am
by Celauran
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 ?

Posted: Thu May 29, 2014 6:52 am
by Celauran
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'

Re: How do I check hours between two date/times ?

Posted: Thu May 29, 2014 7:18 am
by simonmlewis
What about:

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

Re: How do I check hours between two date/times ?

Posted: Thu May 29, 2014 7:20 am
by simonmlewis
Ohhh what is that "HAVING..." bit of the code???

Re: How do I check hours between two date/times ?

Posted: Thu May 29, 2014 7:26 am
by simonmlewis
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??

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 , 30
[text]MySQL returned an empty result set (i.e. zero rows). (Query took 0.0019 sec)[/text]

Re: How do I check hours between two date/times ?

Posted: Thu May 29, 2014 7:36 am
by simonmlewis
Stranger and stranger.

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 , 30
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.

Re: How do I check hours between two date/times ?

Posted: Thu May 29, 2014 8:10 am
by Celauran
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.

Re: How do I check hours between two date/times ?

Posted: Thu May 29, 2014 8:14 am
by simonmlewis
Why would your code produce nothing, when the highest ID in that ticket group, 97, is "customer"??

Re: How do I check hours between two date/times ?

Posted: Thu May 29, 2014 8:18 am
by simonmlewis
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!!

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'
I should apologise as this ticket is now more based on another thread on I have put on here - not about hours between times.