SQL returns only one row

Hi,

Can anyone help me with this SQL query:


        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.

Any ideas why only one row is returned?

Thanks

Hey,

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

tbl_recommendationshits
ID (Auto Inc)
RID (Review ID)
UID (User ID)
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…

Any ideas how i can fix this?

Thanks

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).

Are you sure? Shouldn’t it check if the CURRENT user has recommended the specific review?

this, right here, suggests that you have a php problem

Hey,

Yes i need to check to see if the CURRENT user who is logged in has recommended the review…

Currently it just selects the last recommendation that was made…

Am i doing something wrong?

Thanks

Hi,

Thanks for taking the time to write that code, i think i may not have explained properly however, sorry :rolleyes:

Let’s say there are 2 reviews see this image:

http://freemanholland.com/babies/images/review.jpg

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…

I hope this makes sense?

Thanks

this is a classic mysql grouping problem

you have h.UID in the SELECT clause, but you don’t have it in the GROUP BY clause, i.e. it is “hidden” from the GROUP BY clause

read this for an explanation – GROUP BY and HAVING with Hidden Columns

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 there is one other problem with your query.
This WHERE condition


AND m.deleted = 0

turns the first LEFT JOIN into a normal JOIN. If this still gives you the results you want, then you don’t need that first join to be a LEFT JOIN.

Moved to MySQL forum

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

Thanks

Oh WOW!

I got it working with this query:


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?

try your query again, this time without the dreaded, evil “select star” but with the proper GROUP BY clause