Page 1 of 1

LIMIT result from a query with Multiple WHERE value

Posted: Wed Nov 25, 2009 2:12 am
by codewalkz
I can't seem to limit the result to a desired number. I have this code:

Code: Select all

//GET THE TOTAL OF LEVEL 2
$result = mysql_query("SELECT COUNT(*) FROM agents WHERE (sponsor = '1' or sponsor =  '2' or sponsor = '3') LIMIT 0,1")or die(mysql_error());  
while($row = mysql_fetch_array( $result )) {
$lvl2parent = $row['COUNT(*)'];
echo "<b>Total Level 2 </b> : ";
echo "$lvl2parent <br><br>";
On the code above, the sponsors 1, 2, 3 has 2 records each. And as I see it, the LIMIT 0,1 applies to each instead of giving me ONLY 1 RESULT from the overall. I thought of using an array to echo all results and then choose 1 from that but I am not too familiar with it.

Hope you can help me. this is the final function to finish my project. Thanks!

Re: LIMIT result from a query with Multiple WHERE value

Posted: Wed Nov 25, 2009 3:14 am
by requinix
codewalkz wrote:And as I see it, the LIMIT 0,1 applies to each instead of giving me ONLY 1 RESULT from the overall.
Which explains why it's not doing what you want. LIMIT applies to the entire query. MySQL will do the search, come up with stuff, and then limit the results.

I think

Code: Select all

SELECT sponsor, COUNT(*) FROM agents GROUP BY sponsor
is what you're trying to do: get a list of each sponsor and count the number of agents it has.