getting most records by count

[DrTrans]

<?php 
    
     $connect = mysql_connect("localhost","damgears_evil","damgears");
     mysql_select_db("damgears_evil");
     $queryreg = mysql_query("SELECT COUNT(*) FROM titles WHERE fname = '$fname' && lname = '$lname' ");
     	 while($row = mysql_fetch_assoc($queryreg))
	{
		$amountitles = $row[0];
		$dbname = $row['fname'];
         echo "$amoutitles $dbname";
         }
    ?>

 

Basically what i want to do is if ther is a record

 

Jeff Smith

Jeff Smith

Jeff Smith

John Adams

John Adams

Susan Adams

Jeff Smith

 

I want it to count it and order it by count so the person with the highest amount of records then displays

 

$amounttitles - $dbname

 

and limit to top 10.

 

thanks

 

 

[teynon]

SELECT count(*) FROM titles WHERE fname = '$fname' && lname = '$lame' ORDER BY count(*) GROUP BY fname,lname

 

I haven't tested this. I dont remember if group has to be before or after order or if it matters.

[Kaboom]

would array() work if you set it to show limit (10)? i know how if i reference my files but this is off the top of my bean

[teynon]

Didn't see the limit part of the request:

 

SELECT count(*) FROM titles WHERE fname = '$fname' && lname = '$lame' ORDER BY count(*) GROUP BY fname,lname LIMIT 0, 10

[gizmola]

SELECT count(*) FROM titles WHERE fname = '$fname' && lname = '$lame' ORDER BY count(*) GROUP BY fname,lname

 

I haven't tested this. I dont remember if group has to be before or after order or if it matters.

 

Group by is before.  Also these queries will display a number, but nothing else.  If you actually want to see a name, you need it in the result set. Something like this:

 

SELECT fname, lname, count(*) as countOf FROM titles WHERE fname = '$fname' AND lname = '$lname' GROUP BY fname, lname ORDER BY countOf DESC LIMIT 0, 10

[DrTrans]

$connect = mysql_connect("localhost","damgears_evil","damgears");
     mysql_select_db("damgears_evil");
     $queryreg = mysql_query("SELECT fname, lname, count(*) as countOf FROM titles WHERE fname = '$fname' AND lname = '$lname' GROUP BY fname, lname ORDER BY countOf DESC LIMIT 0, 10");
     	 while($row = mysql_fetch_assoc($queryreg))
	{
		$amountitles = $row[0];
		$dbname = $row['fname'];
         echo "$amountitles $dbname";
         }

 

I did that, and nothign happens. I am very confused.

[cags]

If *nothing* is happening, then it's likely the query is failing or there just are no valid results. You should add some error checking to find out which it is. This...

 

$sql = "SELECT fname, lname, count(*) as countOf FROM titles WHERE fname = '$fname' AND lname = '$lname' GROUP BY fname, lname ORDER BY countOf DESC LIMIT 0, 10";
$queryreg = mysql_query($sql) or trigger_error("SQL: $sql, ERROR: " . mysql_error(), E_USER_ERROR);

... will show you if theres any errors. If it doesn't come up with any errors then try doing...

 

echo mysql_num_rows($queryreg);

... to see if there are actually any rows found.

[DrTrans]

Ok, I got the results to return the names however:

 

while($row = mysql_fetch_assoc($queryreg))
	{
		$amountitles = $row[0];
		$dbname = $row['fname'];
		$dblname = $row['lname'];
         	print "<center><b><a href=\"profile.php?fname=$dbname&lname=$dblname\">$dbname $dblname</a></b><br>$amountitles<br>";
}

 

How do i get the count number so basically $amountitles = Amount of records grouped together.

 

 

[cags]

$row['countOf'];