Paging SQL results from form input

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ebroo
Forum Newbie
Posts: 3
Joined: Wed Jul 05, 2006 9:40 am

Paging SQL results from form input

Post by ebroo »

Hi All,

I am trying to setup a PHP page that queries a SQL database and pages the results. I can setup any number of static queries, but when I introduce a form where the query is based on the form input, I lose the variable after the first page of results.

I have successfully broken this page into two pieces, where one page collects the query and passes it, but now I am trying to combine everything into one page (the form and the result display)

With the code below for a single page, any thoughts on how to get/keep the variables in place?

Code: Select all

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Xstop Search by Category</title>
</head>
<body>
<h1>
Search Xstop Blocks by Category
</h1>
<form name="form" action="search.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>
</body>

<?php

$rowsPerPage = 20;
$pageNum = 1;
$yesterday = strftime ("%Y/%m/%d", strtotime("-1 day"));
$pretty = strftime ("%A %B %d", strtotime("-1 day"));

  // Get the search variable from URL
  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable
 $host = gethostbyname($trimmed);

// rows to return
$limit=20;

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please search by category abbreviation ...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }


if(isset($_GET['page']))
{
        $pageNum = $_GET['page'];
}
$offset = ($pageNum - 1) * $rowsPerPage;
mysql_connect("localhost","user","pass");
mysql_select_db("mydb") or die("Unable to select database");
$query1 = "select * from `hit` where `category` like '%$trimmed%' LIMIT $offset, $rowsPerPage  ";
$numresults=mysql_query($query1);
$numrows=mysql_num_rows($numresults);
$result = mysql_query($query1) or die('Error, query failed');

print $query1;
print "<table border=1><tr><th>IP Address<th>Group<th>Date<th>Time<th>Category<th align=left>Link</tr>\n";

for($i = 0; $i < $numrows; $i++) {
$row = mysql_fetch_array($result); 
$ip2 = $row['IP'];
$host = gethostbyaddr($ip2);
    if(($i % 2) == 0) {
        echo "<TR bgcolor=\"#0099FF\">\n";
    } else {
        echo "<TR bgcolor=\"white\">\n";
    }
echo "<FONT FACE=\"sans-serif, Arial, Helvetica, Geneva\"><TD>$host</TD></font>
<FONT FACE=\"sans-serif, Arial, Helvetica, Geneva\"><TD>".$row['DEFAULT']."</TD></font>
<FONT FACE=\"sans-serif, Arial, Helvetica, Geneva\"><TD>".$row['DATE']."</TD></font>
<FONT FACE=\"sans-serif, Arial, Helvetica, Geneva\"><TD>".$row['TIME']."</TD></font>
<FONT FACE=\"sans-serif, Arial, Helvetica, Geneva\"><TD>".$row['CATEGORY']."</td></font>
<td align=left>
<FONT FACE=\"sans-serif, Arial, Helvetica, Geneva\"><a href=".$row['LINK'].">".$row['LINK']."</a></td></font>\n";
    echo "</TR>\n";
}

echo '<br>';


$query   = "SELECT COUNT(IP) AS numrows FROM hit where `category` like '$trimmed' ";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];
$trimmed = $_POST['trimmed'];
if ($pageNum > 1)
{
    $page = $pageNum - 1;
    $prev = " <a href=\"$self?page=$page\">[Prev]</a> ";

    $first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
    $prev  = ' [Prev] ';       
    $first = ' [First Page] ';
}

if ($pageNum < $maxPage)
{
    $page = $pageNum + 1;
    $next = " <a href=\"$self?page=$page\">[Next]</a> ";

    $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
    $next = ' [Next] ';      
    $last = ' [Last Page] '; 
}
echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;

?>
</body>
</html>
Any thoughts appreciated,

Edward
ebrooathealthydirectionsdotcom
Last edited by ebroo on Wed Jul 05, 2006 12:00 pm, edited 2 times in total.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

1. Use

Code: Select all

or

Code: Select all

tags where appropriate, please

2. Search this forum for "pagination" - plenty of previous threads/discussions and even a Pagination class in the snippets forum, if it hasn't been pruned.
ebroo
Forum Newbie
Posts: 3
Joined: Wed Jul 05, 2006 9:40 am

post and code - not just a pagination issue

Post by ebroo »

Is this the correct usage of Post and Code ?

I've read through the forums, and it seems that either:

a. the problem described was the same as mine - with no solution
b. the probem descirbed was not the same as mine - with solutions posted

It appears that using pagination where the sql query is built from a form input requires you to make the variables persistent somehow - but whats strange, is my _GET seems to be working, yet the variable does not pass -

So - I appreciate the advice on looking for other 'pagination' issues in the forum, but no advice on my current issues are in the forum - so off to the drawing board -
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

You did use the PHP tags. However, you should have used them where you used your Code tags. Instead, you replaced the <?php and ?> tag with the PHP BBCode tags. A simple misunderstanding.

As for your problem, you want the query to be passed from page to page, I assume, as the users goes to the next page or back to the previous page. In this case, you want to add a bit of code to your next and previous query generators.

Code: Select all

$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";
This would essentially become this:

Code: Select all

$prev = " <a href=\"$self?page=$page&q=$var\">[Prev]</a> ";
In this case, $var being what you assigned $_GET['q'] too. You'd want to make this adjustment to each of your links. This way, each time a person click on the previous or next link, the query text would be passed along as well.

You will probably want to encode the query string if you are using it like that, so you'd want to use urlencode before actually using $var in the link creation strings.

Edit:

As an additional note, you might consider testing the value of $_GET['q'] before you output the input search box, and if it exists, prefill the search box with the search term the user entered.
ebroo
Forum Newbie
Posts: 3
Joined: Wed Jul 05, 2006 9:40 am

Resolved -

Post by ebroo »

Doh - Thanks Jason -
Post Reply