How to order from what has more records to what has less

Asked

Viewed 44 times

2

I have a table where it will be stored id_produto and ip . I will assemble a "module" where the system will pull some of the products that have had more visits, so what I need to do is pull the products that have more records in the table, from the largest to the smallest. How can I do this using PHP / Mysql ?

php mysql codeigniter

  • What are the tables you have? The number of product views corresponds to the total number of orders to the product resource, or corresponds to the number of different users who viewed the product?

3 answers

1


You can use the count().

That way it would be like this:

SELECT *, count(ip) AS contagem
FROM tabela
GROUP BY id_produto
ORDER BY contagem DESC

That way you’ll tell the ip, declaring the name of 'count'.
The number of ip for id_produto will be ordained in DESC, in addition you will be able to show it once you are on SELECT. :)

0

In SQL, for you to bring these "some products", first you need to define how many will be, in the example below I considered the top 10:

SELECT prd.id_produto,
       rel.ip 
       COUNT(prd.id_produto) AS total_produtos
FROM TABELA_PRODUTO prd
INNER JOIN  TABELA_REL_PRODUTO_TOTAL rel on(rel.id_produto=prd.id_produto)
GROUP prd.id_produto ORDER BY total_produtos desc LIMIT 0,10;

Now if in this relational table, you have the count directly, it would have to be something like this:

SELECT prd.id_produto,
       rel.ip, 
       rel.total
FROM TABELA_PRODUTO prd
INNER JOIN TABELA_REL_PRODUTO_TOTAL rel on(rel.id_produto=prd.id_produto)
GROUP prd.id_produto ORDER BY rel.total desc LIMIT 0,10;

0

Our friend your question was a little broad, but I’ve created a select that will help you. Thinking of a structure you would have two tables

  • products (id_product, name, description, etc)
  • previewed products (id_product, ip)

This select returns the products by ordered by the number of views DESC

SELECT produtos.*, count(produtos_visualizados.*) AS visualizacao 
FROM produtos
left join produtos_visualizados ON produtos.id_produto = produtos_visualizados.id_produto
group by produtos.id_produto
order by visualizacao DESC

Now you just have to insert into this table every time a product is accessed, and create a model to recover this data.

Browser other questions tagged php mysql codeigniter

You are not signed in. Login or sign up in order to post.