Select the most repeated data in a column and display them

Navigation
1

Good evening, I'm on a project where I have to make a site that allows users to recommend movies, and then show them the most recommended ones.

I've already done the recommend system, now I need to make the most recommended list.

The system works like this:

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 that column and show me the 10 most repeated.I have two doubts.

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

2. How to do an "ECHO" for table rows?

Thank you in advance:)

UPDATE !!!

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

Warning: mysqli_query () expects at least 2 parameters, 1 given in /home/u313079178/public_html/index.php on line 62 Could not fetch 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 looks 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 fetch"; } $ row = mysqli_fetch_row ($ result);

echo $ row [0]; echo $ row [1];

    
asked by anonymous 27.09.2016 / 04:23

1 answer

1

If I understand your problem, you will have to use sql's "count", "group by" and "order by" functions, and preferably a function that limits the number of rows returned in the query, depends on which bank you are using.

First you will use group by to group the data that is repeated in a column: 

select filme from recomendacao group by filme

Then you will tell the movies that are repeated: 

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

Lastly, you will sort from descending (from highest to lowest) by the number of movies counted 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 "limit" function.

I hope you have helped.

    
27.09.2016 / 06:50
Self-relationship in mysql Improving script perfomance