public function selectAll(){
$sql = "SELECT *,i.ID as theID, i.date_added as theDate
FROM c2_glo.tbl_courses i
LEFT JOIN c2_glo.tbl_coursecategories c on c.ID = i.catID
WHERE i.deleted = 0 AND c.deleted=0 GROUP BY RID ORDER BY date DESC";
$result = mysql_query($sql) or die(mysql_error());
return $result;
}
Now in my table i have a number of rows which i group by the RID. And from the GROUPED rows that i return i want to show the one where the date is the most recent.
So for example if ‘date’ had the following rows for example:
You will definitely have issues just as soon as you have two people trying to do an add at the same time with them both getting the same RID for what should be different values.
Basically there multiple rows with the same RID, for example 23. Now in the admin section i only want to pull out one of those records by the most recent ‘date’…
My insert statement adds multiple rows but hold a different ‘date’, see below:
SELECT i.ID as theID
, i.locID
, i.RID
, i.weeks
, i.date
, i.startdate
, i.enddate
, i.title
, i.body
, i.email
, i.practitioner
, i.phone
, i.image
, i.date_added as theDate
, c.foo
, c.bar
FROM ( SELECT RID
, MAX(date_added) AS latest_date
FROM c2_glo.tbl_courses
WHERE deleted = 0
GROUP
BY RID ) AS m
INNER
JOIN c2_glo.tbl_courses AS i
ON i.RID = m.RID
AND i.date_added = m.latest_date
INNER
JOIN c2_glo.tbl_coursecategories AS c
ON c.ID = i.catID
AND c.deleted = 0
WHERE i.deleted = 0
please note you are asking for a world of hurt by doing the MAX(ID)+1 calculation yourself – any reason you’re not using an auto_increment?
So 23, 23, and 22 is the RID. So if i was to add multiple instances of the same row but with different days, would there have been a different method to grouping these together?
The GROUP BY RID in the select is what is causing the problem. With that there the rest of the query only sees one value for each field for each RID. If that is supposed to be there then you should also include all of the values except for RID that are being returned inside of functions that define which of the possible values it should select to return such as MAX()
