Grow your CSS skills. Land your dream job.

Pagination for searched results help

  • # March 15, 2009 at 10:13 am

    Hi all,
    Its been a while since I have had to mess with PHP but I can’t get pagination to work on a page of searched results. Can anybody help?

    Here is my code, I have tried different things but it don’t seem to work, so here is working code of the page showing all results on a page.

    Thanks Damian

    <?
    mysql_connect("host", "user", "password");
    mysql_select_db("database");

    $search=$_POST["search"];

    $result = mysql_query("SELECT * FROM Raids WHERE Date LIKE ‘%$search%’ OR Target LIKE ‘%$search%’ OR Plane LIKE ‘%$search%’ OR Callsign LIKE ‘%$search%’ OR Pilotsurname LIKE ‘%$search%”");

    while($r=mysql_fetch_array($result))
    {
    $Date=$r["Date"];
    $Plane=$r["Plane"];
    $Callsign=$r["Callsign"];
    $Target=$r["Target"];
    $Pilotsurname=$r["Pilotsurname"];
    $Pilotrank=$r["Pilotrank"];
    $Pilotinitials=$r["Pilotinitials"];
    $Comments=$r["Comments"];

    echo "<table cellpadding=0 border=1>";
    echo "<tr>";
    echo "<th>";
    echo "Date";
    echo "</th>";
    echo "<th>";
    echo "Aircraft";
    echo "</th>";
    echo "<th>";
    echo "Call Sign";
    echo "</th>";
    echo "<th>";
    echo "Target";
    echo "</th>";
    echo "</tr>";
    echo "<tr>";
    echo "<td>$Date</td>";
    echo "<td>$Plane</td>";
    echo "<td>$Callsign</td>";
    echo "<td>$Target</td>";
    echo "<tr>";
    echo "<th>";
    echo "Pilot Surname";
    echo "</th>";
    echo "<th>";
    echo "Pilot Rank";
    echo "</th>";
    echo "<th>";
    echo "Pilot Initials";
    echo "</th>";
    echo "</tr>";
    echo "<tr>";
    Print "<td>$Pilotsurname</td>";
    Print "<td>$Pilotrank</td>";
    Print "<td>$Pilotinitials</td>";
    echo "<tr>";
    echo "<th>";
    echo "<tr>";
    echo "<th>";
    echo "Notes";
    echo "</th>";
    echo "<th>";
    echo "</tr>";
    echo "<tr>";
    Print "<td>$Comments</td>";
    echo "<br>";
    echo "</tr>";
    echo "</table>";
    }
    ?>

    # March 21, 2009 at 2:22 pm

    Ah, pagination, Personally I think its more fun to figure this out for yourself, so if you want to read the first part, and attempt it, go ahead, but, if you want to use the source, feel free to. This kinda came to me in the shower one day while I was thinking abut my state tech fair project. It was a blog that needed paginated posts…

    You’re going to want to decide how many results per page you want to display. For now, I’d go with 5.

    The theory behind pagination is that if you are on page one, you want to have the first result displayed the page number times the number of results per page minus the results per page. For example.

    If you are on page one, and you are displaying 10 results per page your first result would be the 1st in the database.

    1 (page number) * 10 (posts per page) – 10 (posts per page) = 1 (first result on page)
    2 (page number) * 10 (posts per page) – 10 = 10 (first result on page)

    Thats the theory behind it (If my theory is stupid, someone please correct me :P ).

    Some basic data you’re going to need for this to work. How many results were returned, how many results per page you would like, and what page you are on.

    To find out how many results were returned in your array you should use the PHP mysql_num_rows() function.
    To find out what page you are on, you need to use the GET array, $_GET;
    To know how many results per page, simply set a variable, I recommend a config file with database and others settings inside of it.

    Ok on with your script, remember, you can stop using PHP at any time, and write HTML. Its really really unnecessary (and slow) to use like 30 lines of echos.

    config.php

    Code:
    < ?php

    $_config['username'] = "root";
    $_config['password'] = "root";
    $_config['host'] = "localhost";
    $_config['database'] = "pagination";
    $_config['baseurl'] = "localhost";

    $_config['results_per_page'] = 5;

    ?>

    index.php

    Code:
    < ?php

    //getting our configuration, this is a good way to store values that may be changed later, just good habit
    require_once('config.php');

    //connecting to our database
    mysql_connect($_config['host'], $_config['username'], $_config['password']);

    //selecting our database
    mysql_select_db("pagination");

    //getting our search query string
    $search = $_GET['search'];

    //getting our page
    $page = $_GET['page'];

    //getting how many results to display per page
    $results_per_page = $_config['results_per_page'];

    //what result is our first? Used in the Limit
    $first_result = $page * $results_per_page - $results_per_page;

    $result = mysql_query("SELECT * FROM CountryLanguage WHERE Language LIKE "$search"");

    //knowing the number of results will know how many pages we have
    $numresults = mysql_num_rows($result);

    //query our country language table
    $result = mysql_query("SELECT * FROM CountryLanguage WHERE Language LIKE "$search" ORDER by CountryCode DESC LIMIT $first_result, $results_per_page");

    ?>

    Search

    Countries by continent

    ” method=”get” >

    Countries that speak < ?php echo $search; ?>


      ?>

    1. < ?php echo $countryresult["Name"]; ?>
    2. < ?php endwhile; ?>

    < ?php if($page != 1): ?>


    &page=< ?php echo $page-1; ?>“>Previous Page

    < ?php endif;?>

    < ?php if($page * $results_per_page < $numresults): ?>




    &page=< ?php echo $page+1; ?>“>Next Page

    < ?php endif;?>

    The code looks really really really ugly so, you can download/view it here http://www.mattegan.net/code.txt

    I hope that helps :D Btw, my PHP may not be the best, as in, it works, but I’m not sure if this is how most people would go about it. I’ve only been programming PHP for about a year, and there are plenty of people way better than me.

    # March 21, 2009 at 2:51 pm

    You should always escape user inputted strings that are being used in database queries. If you don’t follow this advice malicious users could get unrestricted access to your data.

    Code:
    //getting our search query string
    $search = mysql_real_escape_string($_GET[‘search’]);
    # March 21, 2009 at 2:57 pm

    Woops, forgot to mention that. Thanks Dave :D

    Also, the database I was using was the world.sql file you can get off of the mySQL site.

    # March 24, 2009 at 7:12 pm

    Please tell me I didn’t go through the trouble of writing and commenting through all that code for nothing…. :?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".