SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL returns only one row

    Hi,

    Can anyone help me with this SQL query:

    PHP Code:
            SELECT *, r.ID as theIDr.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 AND r.approved AND m.deleted 
    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
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    try your query again, this time without the dreaded, evil "select star" but with the proper GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh WOW!

    I got it working with this query:

    PHP Code:
    SELECT 
            r
    .ID as theIDr.titler.reviewr.date_added as theDate
            
    m.fnamem.sname
            
    COUNT(h.RID) AS HITSh.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 AND r.approved AND m.deleted 0
            GROUP BY r
    .IDr.titler.reviewr.date_addedm.fnamem.snameh.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?
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  4. #4
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  5. #5
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Moved to MySQL forum
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Actually there is one other problem with your query.
    This WHERE condition
    Code MySQL:
    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.

  7. #7
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    I am having another issue with this SELECT statement:

    PHP Code:
    SELECT
            r
    .ID as theIDr.titler.reviewr.date_added as theDate
            
    m.fnamem.sname
            
    COUNT(h.RID) AS HITSh.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 AND r.approved AND m.deleted 0
            GROUP BY r
    .IDr.titler.reviewr.date_addedm.fnamem.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
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    SELECT r.ID AS theID
         , r.title
         , r.review
         , r.date_added AS theDate
         , m.fname
         , m.sname
         , x.hits
         , 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 ( SELECT RID
                  , COUNT(*) AS hits
                  , MAX(date_added) AS latest
               FROM tbl_recommendationshits
             GROUP
                 BY RID ) AS x
        ON x.RID = r.ID
    LEFT OUTER
      JOIN tbl_recommendationshits AS h 
        ON h.RID = x.RID
       AND h.date_added = x.latest
     WHERE r.deleted = 0 
       AND r.approved = 1 
    ORDER 
        BY r.date_added DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

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

    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
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by billy_111 View Post
    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.
    this, right here, suggests that you have a php problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by billy_111 View Post
    it should check to see ALL the users who have recommended the specific review..
    Are you sure? Shouldn't it check if the CURRENT user has recommended the specific review?

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    this, right here, suggests that you have a php problem
    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).

  13. #13
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •