SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Addict ketting00's Avatar
    Join Date
    Jul 2011
    Posts
    325
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    How to count a column correctly after join 2 tables

    Hi,
    I have these two tables and the following values
    Code:
    Table Popular		Table Photo
    id	user_id			id	user_id	photo			cover_image
    1	3				1	1		path_to_photo	0
    2	3				2	3		path_to_photo	0
    3	1				3	1		path_to_photo	0
    4	4				4	1		path_to_photo	0
    					5	1		path_to_photo	0
    and MySQL query
    Code:
    SELECT Popular.id
    , Popular.user_id
    , COUNT(Popular.user_id) as most_popular
    , Photo.user_id as photo_uid
    , Photo.photo
    , Photo.cover_image
    FROM Popular
    INNER JOIN Photo 
    ON Popular.user_id = Photo.user_id
    WHERE Popular.user_id != '$user_id'
    GROUP BY Popular.user_id
    ORDER BY most_popular DESC
    and my PHP query with results
    Code:
    echo "->" . $row->most_popular . "<br/>";
    
    -> 8
    -> 2
    If I don't join these two tables, the count is accurate.

    I need the cover_image column too becuase I would make it display the user selected picture. In this case, both of the users don't pick any image as cover image yet.

  2. #2
    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)
    What would the correct result be considering the data you posted?
    And what is the relationship between the two tables?

  3. #3
    SitePoint Addict ketting00's Avatar
    Join Date
    Jul 2011
    Posts
    325
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help. I resolved this with a foreach loop. It's take quite a bit long time to figure out though.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    using a FOREACH loop means you are looping through your query over and over again. This will become significantly slower over time as your table size grows and is almost never the correct solution.

  5. #5
    SitePoint Addict ketting00's Avatar
    Join Date
    Jul 2011
    Posts
    325
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks guelphdad,

    I'll keep that in mind, but I haven't got it done with MySQL query yet. Perhaps I try another method.


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
  •