How do I group by, but only show the latest in the DB?
Moderator: General Moderators
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
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!
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!
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 group by, but only show the latest in the DB?
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.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
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?
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?
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 group by, but only show the latest in the DB?
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.
- 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?
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.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.
This would be immensely helpful, yes.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.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: How do I group by, but only show the latest in the DB?
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: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.
Was it Voltaire who said the better is the enemy of the good...simonmlewis wrote:Surely that's better?
I think Celauran is suggesting that if you cannot solve your problem the way you'd like, then solve it the way you can.simonmlewis wrote:Come on, what is the actual answer you are suggesting here?
(#10850)
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
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?
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?
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 group by, but only show the latest in the DB?
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)-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
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] ?
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] ?
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 group by, but only show the latest in the DB?
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:I don't know how to extract each "$ticket" into a variable, to then query the database.
It's just returning an array. Iterate over it as you would any other array.simonmlewis wrote:So how do do I work it so I can do it as $row->tickets or $row[tickets] ?
Code: Select all
$tickets = getUnanswered($pdo, date('Y-m-d'));
foreach ($tickets as $ticket) {
echo $ticket['dateupdated'];
etc.
}-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do I group by, but only show the latest in the DB?
So I can just go:
if ($ticket['dateupdated'] == .................) and so on?
if ($ticket['dateupdated'] == .................) and so on?
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 group by, but only show the latest in the DB?
Terrific - leave it with me. I'll come back here with my findings.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.