1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SELECT DISTINCT Problem

Discussion in 'MySQL' started by Weirfire, Dec 6, 2004.

  1. #1
    I have created my query

    and want to extract each row from the table by using

    But I get this error


    Any ideas on how to fetch the data?
     
    Weirfire, Dec 6, 2004 IP
  2. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Try putting a column name into the query rather than the wildcard.
     
    mnemtsas, Dec 6, 2004 IP
  3. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #3
    Got it working

    I'm such a novice. I had DISTINCT(Referrer) instead of DISTINCT Referrer. Mixing my COUNT with my DISTINCTs.

    :eek:
     
    Weirfire, Dec 6, 2004 IP
  4. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #4
    No, the proper SQL syntax is the first one. It will return one column of all the unique values in the column referrer in the table referrer.
     
    mnemtsas, Dec 6, 2004 IP
  5. draculus

    draculus Peon

    Messages:
    63
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #5
    $resulta = mysql_query("SELECT DISTINCT(*)) FROM Refferrer");

    This line has more close brackets than open brackets. You also need to specify what fields you want to be distinct. You're probably getting an empty result set back at present.

    $resulta = mysql_query("SELECT DISTINCT <field_name, [more_fields]> FROM Refferrer");

    Would be better.
     
    draculus, Dec 6, 2004 IP
  6. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #6
    Thanks Draculas. MY next problem is how to get the count of these Referrals and display them in descending order. Sometimes I think I just like causing myself problems.
     
    Weirfire, Dec 6, 2004 IP
  7. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Try

    SELECT DISTINCT(referrer) as referrer,COUNT(referrer) as referrerCount from referrer GROUP BY referrer ORDER BY COUNT(referrer) DESC;

    This should give you a two column set of records with the referrer in the first column and the number of referrals in the second.

    Mark
     
    mnemtsas, Dec 6, 2004 IP
  8. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #8
    Should it not be something like COUNT(DISTINCT Referrer) ?

    I presume you cant do a mysql_fetch_row on a COUNT?

    What I want is the Referrer name and then the number of times it has been used displayed beside it. Might have to do some sort of multiple query.
     
    Weirfire, Dec 6, 2004 IP
  9. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #9
    The SQL I posted will give you what you want in two columns. Count(DISTINCT Referrer) will simply give you a count of the number of different referrers. Do you want the results in one column?
     
    mnemtsas, Dec 6, 2004 IP
  10. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #10
    I think I need the results in 2 columns. 1 displaying the referrer name and another displaying the count of each distinct referrer name. I believe it's my fetching of the values that is perhaps causing the problems.

    Here is my exact code

     
    Weirfire, Dec 6, 2004 IP
  11. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #11
    TO me the fetching looks OK, try doing some debug code using:
    
    <?php
    $resulta = mysql_query("SELECT DISTINCT(refferrers) as referrer,COUNT(refferrer) as referrerCount from refferrers GROUP BY referrer ORDER BY COUNT(refferrer) DESC;");
    if (!$resulta) {
       echo 'Could not run query: ' . mysql_error();
       exit;
    }
    $row = mysql_fetch_row($resulta);
    
    echo $row[0]; // the referrer name
    echo $row[1]; // the referrer count (note the array offset)
    ?> 
    
    
    Code (markup):
     
    mnemtsas, Dec 6, 2004 IP
    Weirfire likes this.
  12. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #12
    Well for one my table isnt called Refferrers, it's called Refferrer. Let me try that and see if it makes any difference.

    Ok it's displaying a list of results but the Count for each referrer is 1.

    www.weirfire.co.uk/Statistics.php

    grrr
     
    Weirfire, Dec 6, 2004 IP
  13. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #13
    lol ooops my bad I thought referrer had one f :(
     
    mnemtsas, Dec 6, 2004 IP
  14. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #14
    Yeah that was my fault in the first place though. :eek:

    Sloppy stuff from Weirfire. I really should stop hacking code together.

    Any ideas how I can get the right count for each referral?
     
    Weirfire, Dec 6, 2004 IP
  15. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #15
    Can you post the exact code here pls.
     
    mnemtsas, Dec 6, 2004 IP
  16. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #16
    I think I want to do something like SELECT DISTINCT Referrer.R1, COUNT(R1) but I'm not sure. It's a lot easier plugging away at SQL with Oracle.
     
    Weirfire, Dec 6, 2004 IP
  17. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #17
    Get rid of the DISTINCT in the COUNT brackets
     
    mnemtsas, Dec 6, 2004 IP
  18. melfan

    melfan Peon

    Messages:
    644
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    0
    #18
    I suggest use only GROUP BY statement and omit DISTINCT (You can only use one column when using DISTINCT statement)

    SELEC Referrer, Count(*) as Count from Refferrer
    GROUP BY Refferrer
     
    melfan, Dec 6, 2004 IP
  19. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #19
    Thanks melfan and mnemtsas for your help. Got it working perfectly.

    I used melfans query without any distincts. I take it that the GROUP BY Referrer takes a unique referrer?
     
    Weirfire, Dec 7, 2004 IP
  20. melfan

    melfan Peon

    Messages:
    644
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    0
    #20
    Yes, it should be.
     
    melfan, Dec 7, 2004 IP