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.
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:
SELECT Count(*), referrer, lastname, firstname
GROUP BY referrer, lastname, firstname
ORDER BY Count(*) DESC, referrer 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!
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.