SELECT *, r.ID as theID, r.date_added as theDate,
COUNT(h.RID) AS HITS
FROM tbl_recommendations r
LEFT JOIN tbl_members m ON r.UID = m.ID
LEFT JOIN tbl_recommendationshits h ON r.ID = h.RID
WHERE r.deleted = 0 AND r.approved = 1 AND m.deleted = 0
My tbl_recommendations table has more than one row, it has 4, and i am only presented with one. I am COUNTING the number of hits for each recommendation that exists in the recommendations table, the number of HITS are kept in a separate table with the UID of the user who has made the hit, hence why i JOIN tbl_members.
I am having another issue with this SELECT statement:
SELECT
r.ID as theID, r.title, r.review, r.date_added as theDate,
m.fname, m.sname,
COUNT(h.RID) AS HITS, h.UID as theUser
FROM tbl_recommendations r
LEFT JOIN tbl_members m ON r.UID = m.ID
LEFT JOIN tbl_recommendationshits h ON r.ID = h.RID
WHERE r.deleted = 0 AND r.approved = 1 AND m.deleted = 0
GROUP BY r.ID, r.title, r.review, r.date_added, m.fname, m.sname
ORDER BY r.date_added DESC
I have 2 tables, tbl_recommendations and tbl_recommendationshits…
tbl_recommendations
ID (Auto Inc)
UID (User ID)
title
review
date_added
Now, there will be multiple users who click a button saying “I like this review”, this will execute the INSERT statement into tbl_recommendationshits which makes a record of which review the user likes. Now the problem is that multiple users can like the same review…
What is happening with this SELECT is that it only returns one row for each review and with the latest (newest) UID, so if a user has already clicked on “I like this review”, they will see a message saying “You like this review”, but then as soon as someone else clicks the button it only shows the latest user…
I think his query doesn’t extract what he needs. I think he checks the userid extracted by the query (the last userid that recommended the review) against the userid of the user viewing the page. So the userid to extract isn’t the last one that recommended the review, but the one of the current user (if he recommended the review).
If a person is logged in they can click on the button “Click here to love this place”…
Upon clicking this the count with the love heart will increase which currently shows “0 people love this too”… What is happening is that when one person clicks on the button it increments the count and inserts into the database with a record of the user who likes the review. Then the user does not see the button they see a line of text saying “You like this review…”
But then when a different user clicks on the button they see the line of text, but the first user can see the button again. So it’s like the first user has not clicked on the button. The problem is that it is SELECTING the “last recommendation” which i do not want. There will be multiple users logged in, so if 10 different users have clicked on the button i need to check to see which one has and show/don’t show the button…
Currently the SELECT statement i am using gets the last recommendation, it should check to see ALL the users who have recommended the specific review…
in order to show the user who made the last recommendation hit, you have to join to a derived table subquery which selects the last recommendation hit and only then join back to the recommendation hits to find out which user it was
but this also affords you the opportunity to eliminate the GROUP BY clause in the outer query, since the grouping is done in the derived table
SELECT r.ID AS theID
, r.title
, r.review
, r.date_added AS theDate
, m.fname
, m.sname
, [COLOR="Red"]x.hits[/COLOR]
, h.UID AS theUser
FROM tbl_recommendations AS r
INNER
JOIN tbl_members AS m
ON m.ID = r.UID
AND m.deleted = 0
LEFT OUTER
JOIN [COLOR="red"]( SELECT RID
, COUNT(*) AS hits
, MAX(date_added) AS latest
FROM tbl_recommendationshits
GROUP
BY RID ) AS x
ON x.RID = r.ID[/COLOR]
LEFT OUTER
JOIN tbl_recommendationshits AS h
[COLOR="red"]ON h.RID = x.RID
AND h.date_added = x.latest[/COLOR]
WHERE r.deleted = 0
AND r.approved = 1
ORDER
BY r.date_added DESC
Actually i have found a problem with this query, if there are no rows in the tbl_recommendationshits table then i get no rows returned, although there are rows existing in the tb_recommendations table.
So i changed the
JOIN tbl_recommendationshits h ON r.ID = h.RID
to
LEFT JOIN tbl_recommendationshits h ON r.ID = h.RID
SELECT
r.ID as theID, r.title, r.review, r.date_added as theDate,
m.fname, m.sname,
COUNT(h.RID) AS HITS, h.UID as theUser
FROM tbl_recommendations r
LEFT JOIN tbl_members m ON r.UID = m.ID
JOIN tbl_recommendationshits h ON r.ID = h.RID
WHERE r.deleted = 0 AND r.approved = 1 AND m.deleted = 0
GROUP BY r.ID, r.title, r.review, r.date_added, m.fname, m.sname, h.UID
ORDER BY r.date_added DESC
So i SELECTED what i needed from each table, and then GROUPED them at the end…
I assume that when i SELECT * it mixes the columns up? Is that why my initial query was not working?