Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Unanswered: Simple mySQL syntax and results

    Okay, here's my situation:

    I have a affiliate type system going on. I know little about mySQL, but learning. I need to know how to make a PHP page that displays information from a database. What is the correct syntax and code for the following query.

    First, connect to database. In resellers (table) there is a row called referrer. This field shows who the person was referred by. I need the query to organize the referrer field, and count how many referrer "groups" there are. For example, the row "referrer" may have several results of the number 9. I want the query to count how many 9's there are, and organize it to the top of the list and display the first and lastname that corresponds with 9 (it's in the same table under firstname and lastname).

    So, it's going to be a top ten leader board. the results should organize the top leaders and display the results in order from most referrals, to least referrals.

    Make sense?

    Help if you can. Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your table design sounds very questionable to me. That's Ok as long as it works for you, but it may lead you to many problems down the road. Check out the concept of "database normalization" in a database book or on the web.

    With that said, the SQL for your request is pretty simple. I'm pretty sure that you want:
    PHP Code:
    SELECT Count(*), referrerlastnamefirstname
       FROM myTable
       GROUP BY referrer
    lastnamefirstname
       ORDER BY Count
    (*) DESCreferrer DESC 
    The PHP side is another story. I'm not strong there, so I'll let you post those questions in the PHP Forum where you can get some PHP opinions!

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    5
    Sorry for my confusion, but did you mean I'll have problems because of the query, or because of the system I'm using for an affiliate site.

    BTW, thanks for the sql code, I'm writing PHP right after this post.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I meant that the table organization is likely to cause you problems. For example, what happens when you turn up a referrer id with 2 (or 5) last names associated with it?!? Over time, this will happen if your tables aren't in at least the third normal form.

    -PatP

Posting Permissions

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