Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post

    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'.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I'm using MSAccess 2000
    You have my sympathies.

    Anyway, I Googled "ms access +limit" and the second result has an answer:

    http://lists.evolt.org/archive/Week-...28/140040.html

    I dunno if it actually works. If not, then maybe one of the other million search results will have an answer that works.

  • #3
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    MS Access
    "SELECT TOP N WHERE ..."
    This works but if I want to post pages of 10 records per page ...how would that work?

  • #4
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    the easy way to do pagination is along the following theory:

    in your url you have a variable like: page

    Code:
    http://www.yoursite.com/search.php?page=1
    then in your php, you have something along the lines of:

    PHP Code:
    $page $_GET['page'];
    $resultspertpage 10;

    $end $page*$resultsperpage;
    $start = ($page*$resultsperpage)-$resultsperpage
    then you will fetch the resulting rows of your query that are between $start and $end.

    With mysql, the query you would use would be something like:

    PHP Code:
    $query "SELECT * FROM tbl LIMIT ".$start.",".$end

  • #5
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    Where do I put page=1? in the action of the previous page? I tried that but it ignores that.

  • #6
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    okay, lets say you have a search box, the first time you go to search.php:

    PHP Code:
    <form action="search.php?page=1" method="post" />
    blah blah blah
    </form
    then you have all your processing and page 1 shows up.

    or you can just do like
    <a href="http://www.mysite.com/search.php?page=1" />
    <a href="http://www.mysite.com/search.php?page=2" />
    <a href="http://www.mysite.com/search.php?page=3" />
    <a href="http://www.mysite.com/search.php?page=4" />

  • #7
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    Quote Originally Posted by thesavior View Post
    the easy way to do pagination is along the following theory:

    in your url you have a variable like: page

    Code:
    http://www.yoursite.com/search.php?page=1
    then in your php, you have something along the lines of:

    PHP Code:
    $page $_GET['page'];
    $resultspertpage 10;

    $end $page*$resultsperpage;
    $start = ($page*$resultsperpage)-$resultsperpage
    then you will fetch the resulting rows of your query that are between $start and $end.

    With mysql, the query you would use would be something like:

    PHP Code:
    $query "SELECT * FROM tbl LIMIT ".$start.",".$end
    Oh yeah MS Access 2000 doesn't allow the LIMIT. It allows TOP 10 before the * after SELECT.

  • #8
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    this will be slower, but what if you fetch all of them, put them into an array, then have php do the pagination without msaccess. Like have php take the results from keys 10-20.

  • #9
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    Why bother with that when there already is a far more efficient solution?

  • #10
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    Because I am trying to be helpful. We don't know of any more efficient solution with msaccess, that is the problem that we are trying to address.

  • #11
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    The second post had a perfect solution posted.

  • #12
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    I changed to mysql ( from MS Access ).

    I have this so far:
    PHP Code:
    $perpage 10;
    $start = (!empty($_GET['start'])) ? $_GET['start'] : 0;

    $sql.= " LIMIT " $start "," $perpage
    But I still don't understand. What am I missing to go on to page 2?
    Last edited by gilgalbiblewhee; 12-19-2007 at 10:58 PM.

  • #13
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    any ideas? I didn't get a response.

  • #14
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Quote Originally Posted by gilgalbiblewhee View Post
    I changed to mysql ( from MS Access ).

    I have this so far:
    PHP Code:
    $perpage 10;
    $start = (!empty($_GET['start'])) ? $_GET['start'] : 0;

    $sql.= " LIMIT " $start "," $perpage
    But I still don't understand. What am I missing to go on to page 2?
    <a href="http://www.mysite.com/search.php?page=2" />
    Or you can check the Post a PHP Snippet forum for Paging Class by Firepages.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #15
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    How am I supposed to insert page=1 in the url? If the page =1 isn't there I can't use the get method.

    I have:
    PHP Code:
    echo "<a href='http://" $_SERVER['SERVER_NAME'] . $baseurl $urlsnip "'>Page 2</a><br />";
        echo 
    "<a href='http://" $_SERVER['SERVER_NAME'] . $baseurl $urlsnip "'>Page 3</a><br />";
        echo 
    "<a href='http://" $_SERVER['SERVER_NAME'] . $baseurl $urlsnip "'>Page 4</a><br />";
        echo 
    "<a href='http://" $_SERVER['SERVER_NAME'] . $baseurl $urlsnip "'>Page 5</a><br />"
    where $baseurl is:
    PHP Code:
    $baseurl "/*******/search/cat/tableformat.php?"
    and the $urlsnip is:
    PHP Code:
    keyword1=john&keyword2=smith&submit=+Search+&optAction=on 


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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