Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: How to limit search with 10 rows per page

    I'm using MSAccess 2000 with the Apache server. I'm getting the following error.

    Select * FROM tablename WHERE 1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 10
    Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 10'., SQL state 37000 in SQLExecDirect in C:\xampp\htdocs\wheelofgod\search\cat\query.php on line 263
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '1=1 AND text_data LIKE '%keyword%' AND text_data LIKE '%keyword%' LIMIT 10'.
    Compare bible texts (and other tools):
    TheWheelofGod

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    msaccess doesn't understand LIMIT

    SELECT TOP 10 * FROM tablename WHERE 1=1 AND text_data LIKE '%keyword%'

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    494
    But what if I want to add the next 10 to the next page and so on?
    Compare bible texts (and other tools):
    TheWheelofGod

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to maintain a page or record count
    next time its top 20, top 30, ie top(pageno * 10).
    each time you request a page of records then you issue a new SQL statement

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I'm not sure that will work as intended healdem.
    Top X lists the top however many records. i.e. top 20 shows the top 20 records, which ISN'T what he's after I believe. What he wants is to show records 10 -> 20 not 1 -> 20.

    Thus :
    Code:
    SELECT TOP 10 FROM (
     SELECT TOP 20 FROM <table> ORDER BY <id> DESC
    )

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes but as far as I'm aware there is no easy way to tell the SQL engine that I only want rows 11..20

    so for the first hit...... top 10
    next hit top 20
    next hit top 30
    ie whatever page is required select the top (pageNo * norecords) per page
    then in a loop bin the records you don't want, untill you get to the records you do want..

    you certainly would have to go down the get all records at least once to populate the number of pages that the user can view.

    I did look at the top n, and then sort descending to pull off the required rows. but looking a the log files it was quicker to grab the top n rows each time. I think for the last website I implemented this sort of page counter it catually was quicker to to grab all the records, bring them into the server memory and then allow MySQL to fetch subsequent rows from the cache memory it maintains. Admittedly its not a very busy site so it was a reasonable assumption that the db cache would have the same records still in memory

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are two solutions

    1. select * from (select top 10 ... from (select top 20 ... from ...
    order by sortkey asc) order by sortkey desc) order by sortkey asc

    2. select top 10 * from ... where sortkey > savedvalue

    in the first solution, you get the top 20, then take them in the reverse order and get the top 10 (which are the 11th through 20th in desired order), then sort them back into desired order

    this gets progressively slower as the user gets deeper into the data... on the third call you get the top 30, then the top 10 again from the reverse order, then reverse them again, and so on

    in the second solution, you remember the highest value from the previous call (this works nicely because it's not only efficient but the saved value can be passed in the url)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2004
    Posts
    494
    I find these are too complicated.

    In ASP it was much simpler. I still don't know which of your suggestions to choose.
    Compare bible texts (and other tools):
    TheWheelofGod

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    neither do i, because i don't know if there is something built in to php that makes this easy (i don't do php, i just wanted to help you fix your sql)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Pear has a paging object, which you can use.
    But really all you're looking at is loading up the result set into an array and then using array_slice to chop out the section you are after.
    However I would always advise reducing your resultset using your database instead of your application. Thus my option and rudy's option 1 will work best I think.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually i think my 2nd option runs much better, assuming there's an index on the sort column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2004
    Posts
    494
    Quote Originally Posted by aschk
    Pear has a paging object, which you can use.
    But really all you're looking at is loading up the result set into an array and then using array_slice to chop out the section you are after.
    However I would always advise reducing your resultset using your database instead of your application. Thus my option and rudy's option 1 will work best I think.
    That seems to be a good idea.
    Compare bible texts (and other tools):
    TheWheelofGod

  13. #13
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I would read the documentation, as you're still going to have to do the dividing up of the search results. Hence coming back to the SQL

  14. #14
    Join Date
    Jul 2006
    Posts
    56
    Hey, I think my solution is much better: MS Access?! REALLY?! Get out as quick as you can! That boat is gonna sink!

Posting Permissions

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