Select the most repeated data in a column and display them

Question

Good evening, I’m on a project where I should make a site that gives users to recommend movies, and then show the most recommended ones.

I already made the system to recommend, now I need to make the list of most recommended.

That’s how the system works:

The user recommends a movie and this data goes to a column of a table, I want to make the system count the most repeated data of this column and show me the 10 most repeated. I have two doubts.

1.How to make sql count and organize the most repeated ones.

2.How to make an "ECHO" for table Rows?

Thank you in advance :)

UPDATING!!!

Although I follow the code you told me, errors appear in the result of the code, like this:

Warning: mysqli_query() expects at least 2 Parameters, 1 Given in /home/u313079178/public_html/index.php on line 62 Could not perform search Warning: mysqli_fetch_row() expects Parameter 1 to be mysqli_result, null Given in /home/u313079178/public_html/index.php on line 66

My code is like this :

$result = mysqli_query("SELECT filme, COUNT(filme) FROM dados_rec
      GROUP BY filme
      ORDER BY COUNT(filme) DESC LIMIT 10");

if(!$result) { echo "Could not perform search"; } $Row = mysqli_fetch_row($result);

echo $Row[0]; echo $Row[1];

Answer 1

If I understand your problem, for this you will have to use the functions "Count", "group by" and "order by" of sql, and preferably a function that limits the number of rows returned in the query, but it depends on which database you are using.

First you will use group by to group the repeating data in a column:

select filme from recomendacao group by filme

Then you will count the movies that repeat themselves:

select filme, count(filme) from recomendacao group by filme

At last you will sort of descending froma (from the largest to the smallest) by the number of counted films in each group:

select filme, count(filme) from recomendacao group by filme order by count(filme) desc

to limit to the first 10, it depends on each bank, if it is mysql (or mariadb) you will look for the function "limit".

I hope it helped.