phpmyadmin Question posted in
The official documentation can be found here.

MySQL LIMIT doesn't work in Prestashop module – Phpmyadmin

I have written a query for use in a Prestashop 1.6 module and was surprised to see that LIMIT doesn’t work in the query. The plan is to get the top 10 best seller products.

I have tested it in phpMyAdmin and it works perfectly, but when in the application it doesn’t work. Following is the query.

$sql = 'SELECT id_product, sale_nbr 
       FROM '._DB_PREFIX_.'product_sale 
       WHERE id_product = '.(int)$id_product.' 
       ORDER BY sale_nbr DESC LIMIT 10';

return Db::getInstance()->executeS($sql);

I have also tried LIMIT 0, 10

There are no error messages the LIMIT is simply not working. In my test example 15 of the 50 products in my list are top sellers, but I want only 10 of them to show, but it always comes out as 15.

Tags:

3

Answers


  1. 0

    perhaps try nesting:

    sql = 'SELECT id_product, sale_nbr FROM (
       SELECT id_product, sale_nbr 
       FROM '._DB_PREFIX_.'product_sale 
       WHERE id_product = '.(int)$id_product.' 
       ORDER BY sale_nbr DESC LIMIT 10 ) t1';
    Login or Signup to reply.
  2. 0

    It all depends on what you use to execute your query.
    Using:

    - Db::getInstance()->getValue($sql) => Returns 1 result
- Db::getInstance()-> getRow($sql) => Returns an array with the first result line
- Db::getInstance()-> executes($sql) => Returns an array of results

    With the 3rd way you should be able to use the limit; More info here : https://www.prestashop.com/fr/blog/les-bonnes-pratiques-de-la-classe-db-sur-prestashop-1-5

    Regards,

    Login or Signup to reply.
  3. 0

    To get top 10 best seller products :

     $sql = new DbQuery();
 $sql->select('SELECT id_product, sale_nbr');
 $sql->from('product_sale', 'ps');
 $sql->orderBy('`sale_nbr` DESC');
 $sql->limit(10);
 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executes($sql);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search