I can’t seem to figure this one out. I think it should be possible in a single mysql query, but I don’t have it yet.
The main table contains rows of things I am fetching as objects via PDO.
The second table contains a list of dates when those things have been updated.
I want to select columns from the main table, and the latest date for each row in the main table, from the second table.
The initial query looked something like this:-
SELECT area, seamarks.id, name, type, description, seamarks.lat, seamarks.lon, date
FROM seamarks
LEFT JOIN changes on seamarks.id = mid
WHERE active = '1'
This of course gave me duplicates where any row from the first table has more than one change in the changes table. I only want one with the newest date.
If I try:-
SELECT area, seamarks.id, name, type, description, seamarks.lat, seamarks.lon, date
FROM seamarks
LEFT JOIN changes on seamarks.id = mid
WHERE active = '1'
GROUP BY mid ORDER BY date DESC
…I lose the duplicates, but the dates are not the latest.
The other issue I have with using GROUP BY
is that I am also using the PDO::FETCH_GROUP
mode (targeting ‘area’ to group by), which causes a conflict, though they are not the same thing.
$this->dbdata = $this->dbread->query($query)->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_CLASS, 'seamark');
Any idea how to make this work, without a second query, or weeding through results to remove duplicates?