Link to home
Start Free TrialLog in
Avatar of dsgfw
dsgfw

asked on

PHP/MySQL search results w/ pagination & results as hyperlinks

I'm working on a project for a medical practice. I have a patient database and am trying to setup a search that displays a listing of patient records in a MySQL database. (Ex. lastname="Smith") The results would then display all of the "Smiths" listed in the database and would link to each specific "Smith" record. I also need to have the results display pages, if there are 500 "Smiths" then have 25 per page w/ links to the remaining pages.

1) Results as links

2) pagination of results

System running apache 1.3 on WIN2000 SP4, PHP 4.3.6, Mysql 4.0.18
Avatar of snoyes_jw
snoyes_jw
Flag of United States of America image

This is untested, but should point you in the right direction.

// searchpage.php
if(!isset($_GET['pageValue'])
    $pageValue = 0;
else
    $pageValue = $_GET['pageValue'];
$result = mysql_query("SELECT * FROM table WHERE lastname = 'Smith' LIMIT $pageValue, 25");

// while row = each result...
    echo "<a href='lookuppage.php?id=" . $row['id'] . "'>" . $row['firstname'] . "</a>";
echo "<a href='searchpage.php?pageValue=" . $pageValue+25 . "'>Next page</a>";

// lookuppage.php
SELECT * FROM table WHERE id = $_GET[id]

i assume that you have coding for entering the text string to be searched....Or else, use something like the following:

.....................
$fldname = $_GET['name']
 <form method="GET" action="<?= thepage.php ?>">
    <table >
     <tr>
      <td>Name</td>
      <td>
         <input type="text" name="name" maxlength="30" value="<?= $fldname ?>" size="20" ></td>
     <td ><input type="submit" value="Search">
     </td>    
    </tr>
     
   </table>
   </form>
.......................................
// Then, comes our DISPLAY and PAGINATION part.....

<?php
    // get the pager input values
    $page = $_GET['page'];
    $limit = 25;   // 25 records per page...
    $result = mysql_query("select count(lastname) from myTable");
    $total = mysql_result($result, 0, 0);

    // Get the value of search string....
   $search_string = $_GET['name']

    // work out the pager values
    $pager  = Pager::getPagerData($total, $limit, $page);  // See the function defined seperately below
    $offset = $pager->offset;
    $limit  = $pager->limit;
    $page   = $pager->page;

    // use pager values to fetch data
    $query = "select lastname from myTable where lastname like '".$search_string%."' order by someField limit $offset, $limit";
    $result = mysql_query($query);

    // use $result here to output page content

    // while $result = each result...
    echo "<a href='personpage.php?id=" . $result['id'] . "'>" . $result['lastname'] . "</a>";

    // output paging system (could also do it before we output the page content)
    if ($page == 1) // this is the first page - there is no previous page
        echo "Previous";
    else            // not the first page, link to the previous page
        echo "<a href=\"thepage.php?page=" . ($page - 1) . "\">Previous</a>";

    for ($i = 1; $i <= $pager->numPages; $i++) {
        echo " | ";
        if ($i == $pager->page)
            echo "Page $i";
        else
            echo "<a href=\"thepage.php?page=$i\">$i</a>";
    }

    if ($page == $pager->numPages) // this is the last page - there is no next page
        echo "Next";
    else            // not the last page, link to the next page
        echo "<a href=\"thepage.php?page=" . ($page + 1) . "\">Next</a>";
?>
========================================================================================

The Helper class:

<?php
   class Pager
   {
       function getPagerData($numHits, $limit, $page)
       {
           $numHits  = (int) $numHits;
           $limit    = max((int) $limit, 1);
           $page     = (int) $page;
           $numPages = ceil($numHits / $limit);

           $page = max($page, 1);
           $page = min($page, $numPages);

           $offset = ($page - 1) * $limit;

           $ret = new stdClass;

           $ret->offset   = $offset;
           $ret->limit    = $limit;
           $ret->numPages = $numPages;
           $ret->page     = $page;

           return $ret;
       }
   }
?>
=================================================================================================

I have modified the PHP Pagination code from
http://www.phpnoise.com/tutorials/9/3
according to your requirements...If you have questions regarding the code, u can refer the site and see the tutorial given there ...

=================================================================================================

Also, there is a very nice code + tutorial in
http://www.webdevtips.com/webdevtips/php/paging.php
where you can find your requirement....

=================================================================================================
Hope this helps you...For further questions, Dont hesitate to contact me....

All the Best.
ASKER CERTIFIED SOLUTION
Avatar of ldbkutty
ldbkutty
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dsgfw
dsgfw

ASKER

ldbkutty - thanks for the help. I'll check it out.