Offset not working with Limit in mysql query in php

Discussion in 'PHP' started by hope2life, Aug 9, 2012.

0
  1. #1
    Hello,

    $query="SELECT * FROM products WHERE cat1='$cat1' AND cat2='$cat2' Limit 20 OFFSET 3";

    this query when executed gives 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 ' OFFSET 3' at line 1

    can anyone help with it?
     
    hope2life, Aug 9, 2012 IP
  2. MarPlo

    MarPlo Member

    Messages:
    97
    Likes Received:
    2
    Best Answers:
    2
    Trophy Points:
    48
    #2
    Hi,
    From what i found on the net: Instead of the OFFSET keyword you can use 2 parameters in LIMIT:
    $query="SELECT * FROM products WHERE cat1='$cat1' AND cat2='$cat2' Limit 20,3";
    Code (markup):
     
    MarPlo, Aug 9, 2012 IP
  3. khalonn

    khalonn Greenhorn

    Messages:
    33
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    16
    #3
    The offset argument is the first one, so, the correct query is like limit 3, 20 ( it will return the next 20 rows starting from the 4 )
     
    khalonn, Aug 9, 2012 IP
  4. afstanislav

    afstanislav Greenhorn

    Messages:
    31
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    16
    #4
    limit - not really good with very big data sets. If you need use in future use LIMIT 100000, 50 mysql need find all 100000+50 rows and only then provide to you 50 rows.
    You can solve this problem another way. Use something like this - SELECT * FROM table WHERE id > previous_id LIMIT 50
     
    afstanislav, Aug 14, 2012 IP