[RESOLVED] Result from a Query / Array

[Mr_Mako]

I need some help figuring out how to go about getting the results I need from a query. I know there is an easy solution but I can't seem to find it either in the PHP manual or in a search here.

My example database :

id	name		email		status
------------------------------------------------------
1	Bob		[e-mail]	  1
2	Richard		[e-mail]	  1
3	Richard		[e-mail]	  0
4	Bob		[e-mail]	  1
5	Bob		[e-mail]	  0
6	Mark		[e-mail]	  1
7	Mark		[e-mail]	  1
8	Bob		[e-mail]	  1
9	Steve		[e-mail]	  0

The query I'm using to extract data :

$sql = "SELECT * FROM table WHERE status = '1' ORDER BY id";
$result = @mysql_query($sql, $connect) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$name = $row['name'];
}

echo "$name";

This query yeilds the following result :

Bob
Bob
Bob
Richard
Mark
Mark

What I need to do is to just get a result that displays the name of the person with the most records in the database who have a status of 1 (in this case, Bob) ONLY... and only once. Would a "LIMIT 1" work in this case?

I'm sure this is a simple solution, but again I've not located anything that suggests a fix.

[sneakyimp]

hm....somethinng like

SELECT COUNT(id) as total_records, name FROM table GROUP BY name ORDER BY total_records DESC LIMIT 1

i'm not sure if that'll work.

[bpat1434]

I think a "UNIQUE" would be in your best interest here. Using the "LIMIT" actually tells mySQL to get the first and only X rows of the limit. So LIMIT 1 returns 1 row, LIMIT 5 returns 5, etc. etc.

Using a Unique, you'd get each person once. Read up on it in the mySQL manual for your version.

http://dev.mysql.com/doc/refman/4.1/en/index.html

~Brett

[sneakyimp]

oops...forgot status='1'

SELECT COUNT(id) as total_records, name FROM table WHERE status='1' GROUP BY name ORDER BY total_records DESC LIMIT 1

[Mr_Mako]

Thanks... sneakyimp I followed your example and it worked.

Brett, I think the UNIQUE deal will work for what I need, but I'll have to read up on it more before I can apply it.

Thanks again to you both.

[SpockBert]

Hi sorry for butting in but shouldn't that echo $name statement in the first post be inside that while statement?

Otherwise it'll simply just give the very last name no?

[bpat1434]

He only wanted the 1 name to be echoed. Unfortunately his query was not giving him what he expected. Normally, if you wanted to see all the results, the echo would go inside the while() loop (so you would be correct) but if he just wants the one name, then his code would work, it gives one name, but it's the wrong one because of his query.

~Brett