SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    offset limit not working

    OK, so I am querying a table and trying to paginate the results.

    I get an error about the limit clause and I can;t understand it when, I have seen several examples/suggestions in blogs and such like, which as far as I can see, do exactly what I have tried.

    Code mysql:
    SELECT
           BD.business_id
           , BD.business
           , ADDR.name_no
           , ADDR.address_1
           , ADDR.address_2
           , ADDR.address_3
           , ADDR.town_or_townland
           , ADDR.city_or_county_name
           , ADDR.post_code
           , ADDR.country_name 
            FROM business_details BD
            INNER 
            JOIN address ADDR
            ON BD.business_id = ADDR.business_id
            INNER 
            JOIN touristInformationCentres TIC
            ON BD.tic = TIC.tic
            INNER
            JOIN business_type BT
            ON BD.business_type_id = BT.business_type_id
            AND ADDR.city_or_county_name = ?
            AND ADDR.town_or_townland = ?
            AND BT.business_type = ?
            AND BT.business_sub_type = ?
            AND BT.business_category = ?
            LIMIT ?,?
            GROUP BY BD.business_id
            ORDER BY RAND()

    the ? are placeholders and the values are set in the $sth->execute().

    any pointers will be most welcome as this pretty much has me stumped.

    I have tried limit at the end as well.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    this is really the same query as your other thread, so please read that for my suggestions on how to reformulate your AND conditions, and next time please keep all questions about a single query in a single thread

    as for the limit, it is part of the ORDER BY clause

    you're probably not supplying the correct values

    the first parameter is the number of rows to skip over, and the second parameter is the number of rows to return

    LIMIT 0,20 returns rows 1 through 20
    LIMIT 20,20 returns rows 21 through 40
    LIMIT 40,20 returns rows 41 through 60

    i'm guessing you were entering different numbers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry about that Rudy. I thought it was different enough to have a different thread.

    the values I am putting in are 0,10

    here is the error msg.

    Code:
    DBD::mysql::st execute failed: 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','10'
                                       GROUP BY BD.business_id
                 ' at line 27 at ../cgi-bin/business_search line 158.
    which suggests to me that the values are being sent. now I shall check the other thread to see if my AND's are messing it up.
    bazz

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    from the error message, it looks to me like the 0,10 is sitting in front of the GROUP BY clause

    i did mention that the LIMIT is part of the ORDER BY clause, didn't i?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes you did mention that

    I'll await your response in the other thread so I can reply there and stick with that one.

    bazz


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •