Page 4 of 4
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 18, 2014 11:25 am
by simonmlewis
So do I, but none of these queries I have tried, work.
the one I posted days ago, does not show me those only where the last entry has "updatedby = 'support'".
I keep referring back to this. Happy to use a subquery as that will work with a Group, but it's not working - it's bring back those where updatedby - "customer" as well!
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 18, 2014 11:32 am
by Celauran
Which you can then filter out in PHP. You're spending a ton of time trying to find the perfect query when a working solution was proposed weeks ago.
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 18, 2014 11:38 am
by simonmlewis
But what you are suggesting is that I write a query, that churns through 100s and soon 1000s of entries, and queries each entry based on updatedby.
Rather than filtering out what I don't want, and just - within the query - find what I DO want.
Surely that's better?
Come on, what is the actual answer you are suggesting here?
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 18, 2014 12:10 pm
by mikosiko
Maybe if you post:
- the actual query that "is not working" (the complete query please)
- some example of your test data (don't need to be your real data... just good examples to test your query)
- The expected results that you are after based on the test data. and finally,
- What are you getting from your query after run it over the test data.
Maybe then the people that is trying to help you could make sense of all your previous posts, and be able to provide you a solution.
just my 0.02 cents.
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 18, 2014 12:31 pm
by Celauran
simonmlewis wrote:But what you are suggesting is that I write a query, that churns through 100s and soon 1000s of entries, and queries each entry based on updatedby.
Not really, no. There's one query returning one set of results, the display of which is determined by a simple conditional in PHP. It shouldn't be an expensive operation.
mikosiko wrote:Maybe if you post:
- the actual query that "is not working" (the complete query please)
- some example of your test data (don't need to be your real data... just good examples to test your query)
- The expected results that you are after based on the test data. and finally,
- What are you getting from your query after run it over the test data.
Maybe then the people that is trying to help you could make sense of all your previous posts, and be able to provide you a solution.
This would be immensely helpful, yes.
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 18, 2014 3:08 pm
by Christopher
simonmlewis wrote:But what you are suggesting is that I write a query, that churns through 100s and soon 1000s of entries, and queries each entry based on updatedby.
Rather than filtering out what I don't want, and just - within the query - find what I DO want.
If you did it the way Celauran suggests and do a secondary sort by updatedby, then you would not need to churn through any additional entries -- just stop when updatedby changes.
simonmlewis wrote:Surely that's better?
Was it Voltaire who said the better is the enemy of the good...
simonmlewis wrote:Come on, what is the actual answer you are suggesting here?
I think Celauran is suggesting that if you cannot solve your problem the way you'd like, then solve it the way you can.
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 18, 2014 3:12 pm
by simonmlewis
Ok - if you would kindly quote me the query I should be using, as so far what I have used doesn't work.
Let's say I want to be able to query all fields in the database table..... and do as I suggested. How would you write it?
Re: How do I group by, but only show the latest in the DB?
Posted: Fri Jul 18, 2014 4:14 pm
by Celauran
Code: Select all
DROP TABLE IF EXISTS `tickets`;
CREATE TABLE `tickets` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ticket` int(11) unsigned NOT NULL,
`dateupdated` date NOT NULL,
`updatedby` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tickets` (`id`, `ticket`, `dateupdated`, `updatedby`)
VALUES
(1,123,'2014-07-01','customer'),
(2,123,'2014-07-02','support'),
(3,123,'2014-07-03','customer'),
(4,123,'2014-07-04','support'),
(5,123,'2014-07-05','customer'),
(6,123,'2014-07-06','support'),
(7,123,'2014-07-07','customer'),
(8,123,'2014-07-08','support'),
(9,234,'2014-07-07','customer'),
(10,234,'2014-07-07','support'),
(11,234,'2014-07-08','customer'),
(12,234,'2014-07-09','support'),
(13,234,'2014-07-13','customer');
Code: Select all
function getUnanswered(PDO $pdo, $date) {
$query = "SELECT id, ticket, dateupdated, updatedby
FROM (
SELECT id, ticket, dateupdated, updatedby
FROM tickets
ORDER BY id DESC
) AS a
GROUP BY ticket
ORDER BY dateupdated DESC";
$stmt = $pdo->prepare($query);
$exec = $stmt->execute();
$tickets = [];
if ($exec) {
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $result) {
if ($result['updatedby'] == 'support' && $result['dateupdated'] < $date) {
$tickets[] = $result;
}
}
}
return $tickets;
}
$pdo = new PDO('mysql:hostname=localhost;dbname=sandbox', 'foo', 'bar');
var_dump(getUnanswered($pdo, date('Y-m-d')));
Code: Select all
array (size=1)
0 =>
array (size=4)
'id' => string '8' (length=1)
'ticket' => string '123' (length=3)
'dateupdated' => string '2014-07-08' (length=10)
'updatedby' => string 'support' (length=7)
Not what you're looking for?
Re: How do I group by, but only show the latest in the DB?
Posted: Mon Jul 21, 2014 3:15 am
by simonmlewis
Do a straight copy paste, it does look to be working, however I don't know how to extract each "$ticket" into a variable, to then query the database.
The point of doing it as a "GROUP BY" is that is also extracts other database I need. This is why I was trying it in a "while".
I've never used "foreach" in a query like that.
So how do do I work it so I can do it as $row->tickets or $row[tickets] ?
Re: How do I group by, but only show the latest in the DB?
Posted: Mon Jul 21, 2014 6:43 am
by Celauran
simonmlewis wrote:I don't know how to extract each "$ticket" into a variable, to then query the database.
You shouldn't. If you're hitting the database inside a loop, it's time to go back to the drawing board, work out what information you ultimately need, and how to get that.
simonmlewis wrote:So how do do I work it so I can do it as $row->tickets or $row[tickets] ?
It's just returning an array. Iterate over it as you would any other array.
Code: Select all
$tickets = getUnanswered($pdo, date('Y-m-d'));
foreach ($tickets as $ticket) {
echo $ticket['dateupdated'];
etc.
}
Re: How do I group by, but only show the latest in the DB?
Posted: Mon Jul 21, 2014 8:45 am
by simonmlewis
So I can just go:
if ($ticket['dateupdated'] == .................) and so on?
Re: How do I group by, but only show the latest in the DB?
Posted: Mon Jul 21, 2014 8:47 am
by Celauran
Absolutely.
Re: How do I group by, but only show the latest in the DB?
Posted: Tue Jul 22, 2014 3:50 am
by simonmlewis
Terrific - leave it with me. I'll come back here with my findings.