adding parameters to LIMIT clause of a SELECT

Question

Hello all! I have the following prepared statement:

$sql =
     "PREPARE stmt_name FROM 
    'SELECT I.item_id, I.name , I.price, I.discounted_price, I.thumbnail_photo
    FROM item I
    JOIN sub_category SC
    ON I.sub_category_id = SC.sub_category_id
    JOIN category C
    ON C.category_id = SC.category_id
    WHERE C.category_id = ?
    LIMIT ?,? ' ; 

    SET @p1 = categoryId;
    SET @p2 = firstItem;
    SET @p3 = items_per_page;

    EXECUTE stmt_name USING @p1,@p2,@p3; "

which i changed to the following(wihtout using prepared statement)

$sql =

    'SELECT I.item_id, I.name , I.price, I.discounted_price, I.thumbnail_photo
    FROM item I
    JOIN sub_category SC
    ON I.sub_category_id = SC.sub_category_id
    JOIN category C
    ON C.category_id = SC.category_id
    WHERE C.category_id =' . (int)$categoryId;

I want to add parameters to the LIMIT clause Ive gone through some sites, and it seems that adding parameters to the LIMIT clause in a select statement can be done only by using prepared statements. Can I have your opinions and suggestions please?

Thanks!

Answer 1

I want to add parameters to the LIMIT clause Ive gone through some sites, and it seems that adding parameters to the LIMIT clause in a select statement can be done only by using prepared statements.

So add the LIMIT. Why would a prepared statement only allow LIMITS? Its a SQL construct not a PHP construct.

Answer 2

It looks like you're using concatenation, rather than parameters, to build your new SELECT query. If you'd like to continue down this road, you'd just append to your code:

. ' LIMIT ' . (int)$start . ', ' . (int)$number;

If you'd prefer to used a parameterized query, you'll need to use PHP's built-in PDO or mysqli extensions, or choose an abstraction layer with support for parameterized queries such as ADOdb. Parameterized queries are often viewed as safer and cleaner than simply building queries through string concatenation.

Answer 3

You don't have to use prepared statements to use limit. just add Limit... for example

$sql='SELECT I.item_id, I.name , I.price, I.discounted_price, I.thumbnail_photo
    FROM item I
    JOIN sub_category SC
    ON I.sub_category_id = SC.sub_category_id
    JOIN category C
    ON C.category_id = SC.category_id
    WHERE C.category_id =' . (int)$categoryId.' LIMIT '.(int)$start.','.(int)$limit;

http://dev.mysql.com/doc/refman/5.1/en/select.html