Results 1 to 11 of 11
  1. #1

    SQL Search - More Results

    Greetings,

    I'd like to have a search from my MySQL database and show 10 results per page. So, at the bottom of the page there needs to be that common link that says, "Next 10 Results". How do you go about doing this though? I know I can use LIMIT in my sql to only show 10, but how do I write the php to have it show the next 10?

    Any help would be greatly appreciated!

  2. #2
    you probably want something like this

    Code:
    $start=$_GET['start'];
    $end=$start+10;
    
    $sql='SQL code here LIMIT '.$start.','.$end;
    then on the links at the bottom next page will be

    Code:
    <a href='page.php?start=<?=$end?>'>
    having said that, now i cannot remember if it should be LIMIT $start,$end or LIMIT $start, 10 hmmmm
    Note to self: Add something funny!
    Search is your friend!

  3. #3
    Join Date
    Sep 2005
    Location
    Cleveland, Ohio
    Posts
    51
    If you want to skip 10 records and then take the next 10, you would use LIMIT 10,10. If you wanted to skip 5 and take 20, LIMIT 5,20. Note that if you ever plan to port to MS SQL, this will not work, as they do not have the LIMIT feature.
    Jim Lundeen
    Edgar Technologies

    Professional Small Business IT Management
    http://edgar-tech.com

  4. #4
    Have no fear... I would never run my site on an MS platform.

  5. Hi :

    Just remember the SQL poisoning ... never passing posted values directly to the QUERY.

    If some one put you something like start='1, 1; DELETE * from users;'

    ?????

    ( Just an example, maybe this is not the case )

    Regards
    Affordable Canadian Web Hosting - Hosting . 1 domain name, unlimited Ip address. Servers in USA and London.
    Add URL - Links interchange - Links interchange with any PR page.

  6. #6
    Join Date
    Sep 2005
    Location
    Cleveland, Ohio
    Posts
    51
    This is actually a very good point. It's something that many people (myself included) don't pay enough attention to.

    If you know of any good write-ups regarding things of this nature, please share with us.
    Jim Lundeen
    Edgar Technologies

    Professional Small Business IT Management
    http://edgar-tech.com

  7. #7
    Join Date
    Apr 2005
    Posts
    42
    The best thing to do is use the Pager in Pear. There is a website on this but bacially you want to try something like:

    PHP Code:
    <?php
    require_once ("DB.php");
    require_once (
    "DB/Pager.php");


    $dsn ""//whatever it may be
        
    $db DB::connect$dsn, array('persistent' => true) );
        
    // With DB::isError you can differentiate between an error or
    // a valid connection.
    if (DB::isError($db)) {
        die (
    $db->getMessage());
    }


    $from $_GET['from']; 
    if (!
    is_numeric($from))

        
    $from 0



    $limit 10

    $maxpages 10

    $query "select * from users";

    $nrows =& $db->getRow($query); 
    $data DB_Pager::getData($from$limit$nrows[0], maxpages); 

    echo 
    '<B> Page '$data['current'] . ' of ' $data['numpages'] . '</B>'
    // Unless we're on the first page, show a link to the previous page 
    if ($data['current'] != $data['firstpage'])

         echo 
    '<a href="'"$PHP_SELF?from=" $data['prev'] . '">Previous Page</a>'


    $res $db->limitQuery($query$from$limit); 

    //DISPLAY YOUR INFO
    //You should use HTML/Table.php in pear.

    if ($data['current'] != $data['lastpage'])

            echo 
    '<a href="'"$PHP_SELF?from=" $data['next'] . '">Next Page</a>'

    echo 
    "<hr>"

    echo 
    $data['numrows'] . ' Results found -- '
    echo 
    'displaying '$data['limit'] . ' results per page<br><br>'
    echo 
    "Jump to page: "
        
    foreach (
    $data['pages'] as $page => $start_row

             echo 
    "[ <a href='$PHP_SELF?from=$start_row'>$page</a> ] "


    ?>
    Most of this code was copied from another site which explains it much better. Do a search for pager and pear on google and you will find it.

  8. #8
    Good point but I think I'll just store what page of the results they're on in a session variable that can get passed around.

  9. #9
    eons ago I found a great tutorial on this when I was trying to grok multi page browsing for queries.

    http://evolt.org/article/Multiple_Pa..._PHP/17/19340/

    there were a couple buggy things about this script that needed to be tweaked, but overall, it worked well, and I've been using a derivative of it for a couple years now. Of course you'll need to re-edit the vars for register_globals off too! I also do a quick var check that requires the var to be an int or returns the var as FALSE and sets the page to 0. The code looks longer than it actually is, there are just several ways to output the result, and it covers a few.

    Hope this information helps.
    Best Hosting Experiences: Voxtreme.Com (Shared Reseller / 5yrs / Former), Serverpoint.Com (Dedicated & VPS / 13+ yrs / Current)

  10. #10
    Thanks, ochiba. That is precisely the type of article I've been looking for!

  11. #11
    Join Date
    Sep 2005
    Location
    Glendale, AZ
    Posts
    84
    A good read if you are not familar with SQL injection:

    http://www.unixwiz.net/techtips/sql-injection.html

Posting Permissions

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