MySQL and PHP

[acole1]

I know I am probably making this more difficult than it needs to be, but I can't figure out how to pick out the top 10 most occurring entries from a MySQL table and put them into an HTML table using PHP.

Keep in mind I'm new to MySQL and PHP, and I'm not all that familiar with anything except the basics (simple SQL queries, loops, and variable storage).

On to the problem...

The MySQL table consists of a record number (pk), a record, and the user ID of the person who entered it.

There are multiple records in the table that are the same. I am trying to find out what the top 10 most entered records are, and display the top 10 of all records entered, and the top 10 of an individual user.

A couple ideas I had were:

1)
Using mysql_num_rows to find out how many rows there were of record X.
It would be something like this:

query1: select record from table where record_num = $n; <- ($n would need to be incremented somehow)
$record = (the returned record);
query2: select record from table where record = $record;
$num_rows = (number of rows returned);

Then store the record, and the number in an array(?) where I can refer back to it, and pick out the top 10.

The main problems I have with this method are how to code it so $n is incremented, but the search stops running when you get to the last record, and how to store the data so I can refer back to it and pick out the top 10.

2)
Take the record of row 1 and compare it to the record in row 2. If they match then add a tally. Store the tallies and records (in an array?) and pull out the top 10.


Any ideas or help would be greatly appreciated.

Like I said, I am new to MySQL and PHP, so feel free to assume I don't know anything.

Thanks!

 

[KLin]

How about doing it all in SQL?

SELECT TOP 10 Record from table
Group by Record
Order by Count(Record) DESC

 

[acole1]

I'll give that a shot.

See I didn't even know you could do that!


Edit 1:
It's not liking the syntax. I'm playing around with it now.

Edit 2:
Well I got it to stop the syntax error, and it is displaying the correct (most entered) record, but I'm not sure how to get it to display all 10. Right now I have it displaying just the top 1.

$query =
"SELECT record
FROM table
ORDER BY record DESC
LIMIT 10";

Edit 3:
It looks like that query doesn't do what I want it to do. When I expand the limit to 300 and sort it by the record num desc., it's clear that it's taking the last 300 records entered, not the 300 most common. I'll have to see if I can implement the ORDER BY COUNT(record) DESC.

Edit 4:
GROUP BY keeps duplicates from forming in my list, but I still can't get COUNT to work.

Edit 5:
I got it!

$query =
"SELECT record,COUNT(record)
FROM table
GROUP BY record
ORDER BY COUNT(record) DESC
LIMIT 11";

 

[troytime]

limit 11?

select id,count(*) as cnt
from table
group by name
having cnt > 1
ORDER BY cnt desc
LIMIT 10

 

[acole1]

Originally posted by: troytime
limit 11?

select id,count(*) as cnt
from table
group by name
having cnt > 1
ORDER BY cnt desc
LIMIT 10

It only displays 9 records if I use LIMIT 10.

I am also having a problem with it displaying the #1 most entered result, which would explain why I have to use limit 11 to get 10 records.

Any idea how to fix that?

I'll give your code a try in the meantime.

Edit:
The lack of the top result and the need for LIMIT 11 is explained by the mistake of having two "$row = mysql_fetch_assoc($result)" statements.

The first record was getting eaten up by the first statement, and then was overwritten by the second.

 

[KLin]

Well I just learned something about MySQL today 🙂. Top Clause from T-SQL = Limit in MySql

 

[acole1]

Well you're welcome!

And thanks so much for putting me on the right track. I was very lost about how to tackle the problem.