Page 1 of 1

Paging SQL results from form input

Posted: Wed Jul 05, 2006 9:47 am
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

Posted: Wed Jul 05, 2006 10:06 am
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.

post and code - not just a pagination issue

Posted: Wed Jul 05, 2006 12:03 pm
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 -

Posted: Wed Jul 05, 2006 12:10 pm
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.

Resolved -

Posted: Wed Jul 05, 2006 12:38 pm
by ebroo
Doh - Thanks Jason -