MySQL search & pagination problems

[sford999]

Hi all

I`ve built a script to search a db, and to show 20 results per page.

The problem I`m having is that when I click to view "Page 2/3/4/5 etc" I get zero results, like the variables are being erased when I click to view the next 20 results.

Page 1 of the search results:
<snip>
Page 2 of the search results:
<snip>

There's too much information to be parsed via the url ($_GET method) in my opinion, and I was wondering what, if anything I`d done wrong for the next pages to show up as blank.

This is the code as it is now:

<?php
error_reporting(E_ALL);
include('functions.php');
$p = isset($_GET["p"])? $_GET["p"] : $_POST["p"];
switch($p)
{
case "results":
// Get The page (Page ID)
$page = $_GET['page'];
if(!$page)
{
// If page is blank set it to 1
$page = 1;
}

// mysql_real-escape_string on all POST vars

// a bunch of conditionals to add to WHERE clause

$WhereClause = implode(' AND ', $whereParts);
$_SESSION['query'] = $WhereClause;
db_connect();

///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
if(isset($_SESSION['query'])) {
$sql = "SELECT * FROM fisheries WHERE ".$_SESSION['query'];
} else {
$sql = "SELECT * FROM fisheries WHERE ".$WhereClause;
}
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////

$result = mysql_query($sql) or
die(sql_error(mysql_error(), $_SERVER['REQUEST_URI'], $_SERVER['REMOTE_ADDR'], getenv($_SERVER['REMOTE_ADDR']), $sql));
if(!$result) {
show_header($title = "Search Results", $active = 'search_fisheries');
echo 'Sorry, we didn`t find anything matching your search query, please go back and try again.';
show_footer();
exit();
}
$num_rows = mysql_num_rows($result);

// Define the number of results per page
$max_results = 20;

// Figure out the limit for the query based on the current page number.
$from = (($page * $max_results) - $max_results);

// Perform MySQL query on only the current page number's results
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
if(isset($_SESSION['query'])) {
$sql = "SELECT * FROM fisheries WHERE ".$_SESSION['query']." AND auth = '1' ORDER BY id ASC LIMIT $from, $max_results";
} else {
$sql = "SELECT * FROM fisheries WHERE ".$WhereClause." AND auth = '1' ORDER BY id ASC LIMIT $from, $max_results";
}
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////

$result = mysql_query($sql);
show_header($title = "Search Results", $active = 'search_fisheries');
echo '<p>We have '.$num_rows.' results matching your query.</p>';
while($row = mysql_fetch_array($result))
{
extract($row);
echo '<p><a href="fisheries.php?id='.$id.'">'.$name.' in '.$town.'</a><br />';
}
echo $_SESSION['query'];
echo '<br /><br />';

// Figure out the total number of results in DB:
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
if(isset($_SESSION['query'])) {
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM fisheries WHERE ".$_SESSION['query']." AND auth = '1'"),0);
} else {
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM fisheries WHERE ".$WhereClause." AND auth = '1'"),0);
}
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);
// Build Page Number Hyperlinks
// Build Previous Link
if($page > 1) {
$prev = ($page -1);
echo '<a href="'.$_SERVER['PHP_SELF'].'?p=results&amp;page='.$prev.'"><span class="pagelink">Previous 20 Results</span></a> ';
}
// Build Next Link
if($page < $total_pages) {
$next = ($page +1);
echo '<a href="'.$_SERVER['PHP_SELF'].'?p=results&amp;page='.$next.'"><span class="pagelink">Next 20 Results</span></a>';
}
echo '<p><a href="'.$_SERVER['PHP_SELF'].'?p=fisheries"><< Search Again</a></p>';
show_footer();
break;

// Search Form goes here

show_footer();
break;
default:
// Temp redirect until I get the above working
header("Location:new_search.php?p=fisheries");
}
?>

As you can see, I`ve created a $_SESSION named "query", but that's being cleared when I click onto page 2/3/4/etc...

I`ll openly admit i`m terrible with sessions and have never been able to grasp them properly, so can anyone help me here as I`d love to get this working.

Thanks

[edited by: jatar_k at 1:06 pm (utc) on May 31, 2007]
[edit reason] shortened code [/edit]

[dreamcatcher]

Hi sford999, welcome to Webmaster World. :)

I see your session variables, but are you using 'session_start' to initialise the session?

session_start(); // goes at the very top of your page

dc

[sford999]

Yes, but I get an error stating:

Notice: A session had already been started - ignoring session_start() in C:\Server\xampp\htdocs\fisheries\captcha.php on line 15

So I removed it.

The script does the same with and without it, so I`m still stuck.

[jatar_k]

in that captcha.php file does it sart a named session? Maybe your vars aren't getting into the session because of some confusion there.

Have you tried echoing all the session vars on your first page, where the value should be set?

echo '<p>session: <pre>';
print_r($_SESSION);
echo '</pre>';