SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Advanced PHP/MySql Query?

    I don't have much experience with advanced queries, but this is what I am trying to accomplish:

    a table in my database has a field for "referrer" which records the name of the person who referred that user to the site. I need to write a query that will, for each user in the table, count how many referrals they have, and output their information with the referral count.

    example of table:

    FNAME LNAME EMAIL REFERRER
    --------- -------- --------------------- --------------
    John Doe jdoe@email.com janedoe@yahoo.com
    Cary Grant cgrant@email.com janedoe@yahoo.com
    Mary Smith msmith@email.com jdoe@email.com

    Example of query output:

    FNAME LNAME EMAIL REFERRALS
    -------- -------- ------------------------ -------------
    Jane Doe janedoe@yahoo.com 2
    John Doe jdoe@email.com 1


    I can write a query to count how many referrals a user has if I pass the user's info in the query (WHERE referrer = 'example'), but I don't know how to count AND output data for all of the users in the database in one query. I am thinking this is a subquery situation, but I don't know where to begin. Any help is greatly appreciated!

  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)
    Code:
    SELECT 
        t1.FIRSTNAME
      , t1.LASTNAME
      , t1.EMAIL
      , COALESCE(t2.REFERRALS, 0) AS REFERRALS
    FROM tablename AS t1
    LEFT OUTER JOIN
      (SELECT 
           EMAIL
         , COUNT(*) AS REFERRALS
       FROM tablename
       GROUP BY EMAIL
      ) AS t2
    ON t1.EMAIL = t2.EMAIL

  3. #3
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Thank you! That's exactly what I was looking for. I had forgotten all about how to do what you did there (so much so in fact I can't even remember what it is called at the moment). Time to brush up on queries again.

    Again, really appreciate it.

  4. #4
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Okay, well I ran the code and the only problem is that for every user that has referrals, it is displaying that user one time for each referral with a count of 1 on each row.

    What can I add that will show each person only one time with their total referral count?

  5. #5
    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)
    Code:
    SELECT 
        t1.FIRSTNAME
      , t1.LASTNAME
      , t1.EMAIL
      , COALESCE(t2.REFERRALS, 0) AS REFERRALS
    FROM tablename AS t1
    LEFT OUTER JOIN
      (SELECT 
           REFERRER
         , COUNT(*) AS REFERRALS
       FROM tablename
       GROUP BY REFERRER
      ) AS t2
    ON t1.EMAIL = t2.REFERRER
    I counted the emails instead of the referrers in the subquery.


Tags for this Thread

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
  •