using order by and limit gives error

[omerbutt]

hi i have written a query and it is giving me th following error

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 ' 2' at line 6
SELECT * FROM pretzel_product s P , pretzel_product s_description PD WHERE (P.prod_id=PD.p rod_id and prod_status<>'0 ') ORDER BY prod_date_added DESC LIMIT , 2

here is my query ,
any suggesstions

$q="SELECT * FROM 
                    ".PRODUCT." P , 
                    ".PRODUCT_DESCRIPTION." PD
                WHERE     
                    (P.prod_id=PD.prod_id and prod_status<>'0') 
                    ORDER BY prod_date_added DESC AND LIMIT $offset , $limit ";
            $r=execute($q);
 

[Atli]

Hey.

You are missing the offset in your LIMIT clause.
The manual lists it as:

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

-- Yours is like:
... LIMIT , 2
 
-- It needs to be either
... LIMIT 1, 2
 
-- Or just
... LIMIT 2
 
-- Can also be
... LIMIT 2 OFFSET 1

[kovik]

So basically, the error is that your variable, $offset, is empty.

Also, there is an inconsistency between the queries being shown. In your code, you have an "AND" between the ORDER BY clause and the LIMIT clause... That is invalid syntax.

[omerbutt]

yeah that AND was a typo mistake and basically the error was that the offset variable was empty
due to which the error was occuring
hanks
regards,
Omer Aslam