LIMIT and OFFSET in a query

Question

I have a query as below,
I just added the last `line LIMIT ?, ?` because I'm trying to implement pagination.

After adding last line I am receiving error `Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '50''`

Any suggestion would be very helpful.

Thanks!

    $offset = 0;
    $limit = 50;

    $get_products = "SELECT p.`product_id`, p.`product_name`
               GROUP_CONCAT(COALESCE(t2.`trait_name`,t1.`trait_name`)) name 
                       FROM `products` p 
                       JOIN 
                            `product_to_traits` pt1 
                         ON pt1.`product_id` = p.`product_id` 
                       JOIN 
                           `personality_traits` t1 
                         ON t1.`trait_id` = pt1.`trait_id` 
                       JOIN 
                           `product_to_traits` pt2 
                         ON pt2.`product_id` = pt1.`product_id` 
                       JOIN 
                           `personality_traits` t2 
                         ON t2.`trait_id` = pt2.`trait_id` 
                      WHERE 
                            t1.`trait_id` = ? 
                        AND t2.`trait_id` IN ($answers) 
                        AND `is_deleted` = 0
                   GROUP BY p.`product_id` HAVING COUNT(*) > 1 
                   ORDER BY COUNT(*) DESC
                      LIMIT ? OFFSET ?";

     $db = getConnection();
     $stmt = $db->prepare($get_products);
     $params = array_merge([$main_category], $answer, [$limit], [$offset]);
     $stmt->execute($params);

problem is probably somewhere in $params = array_merge([$main_category], $answer, [$limit], [$offset]);

beacuse in simpler query with variable:

  $get_products = "SELECT *
                     FROM 
                          `products`  
                    WHERE
                          `is_deleted` = '0'
                 ORDER BY RAND()
                    LIMIT :limit
                   OFFSET :offset";

            $db = getConnection();
            $stmt = $db->prepare($get_products);
            $stmt->bindValue('limit', (int)$limit, PDO::PARAM_INT);
            $stmt->bindValue('offset', (int)$offset, PDO::PARAM_INT);
            $stmt->execute();

everything works fine
So it is not a problem (like someone in one of the posts here said) that is impossible to put limit and offset in variable

you are using aggregate function count which should come after select ..For example SELECT MIN(age) FROM users WHERE condition ORDER BY DESC LIMIT ? OFFSET ? . Here MIN is the aggregate function — , Feb 14 '21 at 17:14

score 0 · Answer 1 · answered Feb 14 '21 at 17:43

0

You are asking to order by count(*) without having it in your select.

answered Feb 14 '21 at 17:43

fancyuserid

145
13

LIMIT and OFFSET in a query

1 Answers1