I have a blog and I want to extract the 8 articles with the most visits but I don't know the sentence that I should use to get what I want. I have a table called post
which is where I store the data of all the articles published on my website and I also have a table called view
which contains a field called view
which stores the number of visits to each post. The number of views of each post is shown correctly on the screen in my blog. But what I want to get now is a SQL statement to show the 8 posts with the most views.
post table:
CREATE TABLE post(
id_post int(250) AUTO_INCREMENT PRIMARY KEY,
id_post_user int(250) NOT NULL,
title varchar(200) NOT NULL,
description_post varchar(250) NOT NULL,
category varchar(50) NOT NULL,
miniatura varchar(250) NOT NULL,
img_description varchar(250),
entrada text NOT NULL,
tags varchar(250) NOT NULL,
`status` varchar(50),
browser_post varchar(200),
ip_post varchar(200),
create_at_post datetime,
CONSTRAINT fk_post_user FOREIGN KEY(id_post_user) REFERENCES users(id_user)
)ENGINE=InnoDb;
View table:
CREATE TABLE `view`(
id_view int(250) AUTO_INCREMENT PRIMARY KEY,
id_view_post int(250) NOT NULL,
id_view_user int(250),
view int(250),
browser_view varchar(200),
ip_view varchar(200),
create_at_view datetime,
CONSTRAINT fk_view_post FOREIGN KEY(id_view_post) REFERENCES post(id_post),
CONSTRAINT fk_view_user FOREIGN KEY(id_view_user) REFERENCES users(id_user)
)ENGINE=InnoDb;
I hope you can help me, if you need to know more please just let me know. Thank you
It occurs to me that you would have to order the view records by view (as I see the number of visits each post has received).
And limit the search to the first 8 with a LIMIT 8.
That is, using this query
The $mysqli variable refers to the one you are using with your database, which I assume will be MySQL.
EDIT
True, as indicated by Intruzo, to search for the 8 largest, you have to order in descending order (from highest to lowest) so the query would really be
Now the answer is complete.