The code I have here works great, except for the fact that when I search it doesn't always display the proper amount of results, but if I press 'search' again or just refresh the page, then all the results show.
For example, if I search for something and I know there's 28 results (10 results per page) then it should show 'Page 1-2-3' at the bottom, but instead it just shows the first 10 results and for the pagination it just shows 'Page 1 of 1'. If I refresh the page, then it shows everything properly!
So here's my code, I'm thinking it has something to do with SQL_CALC_FOUND_ROWS. It's my first time using it, so maybe I'm doing something wrong. I've looked around here, and I'm thinking maybe I should be doing a count instead? I played around with count(*) a bit but I'm getting a little lost here.
<?php
// Connect to the database.
$cnx = mysql_connect('localhost', 'root', 'pass') or die ("Could not connect");
mysql_select_db('main', $cnx) or die (mysql_error());
// Create the search function:
function searchForm()
{
// variable setup for the form.
$searchwords = (isset($_GET['words']) ? htmlspecialchars(stripslashes($_REQUEST['words'])) : '');
echo '<form method="get" action="'.$_SERVER['PHP_SELF'].'">';
echo '<input type="hidden" name="cmd" value="search" />';
echo 'Search for: <input type="text" name="words" value="'.$searchwords.'" /> ';
echo '<input type="submit" value="Search" />';
echo '</form>';
}
// Create the navigation switch
$cmd = (isset($_GET['cmd']) ? $_GET['cmd'] : '');
switch($cmd)
{
default:
echo '<h1>Search Database</h1>';
searchForm();
break;
case "search":
searchForm();
$searchstring = mysql_escape_string($_GET['words']);
echo '<h3>Search Results for ';
echo '"';
echo $searchstring;
echo '"';
echo ':</h3><br />';
// If current page number, use it, if not, set one.
if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}
// Define the number of results per page
$max_results = 10;
// Figure out the limit for the query based on the current page number.
$from = (($page * $max_results) - $max_results);
{
$sql = "SELECT SQL_CALC_FOUND_ROWS name, description, content, url,
MATCH(name, description, content, url)
AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM search
WHERE MATCH(name, description, content, url)
AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score DESC LIMIT $from, $max_results";
}
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT FOUND_ROWS()"), 0);
//Echo query
$result = mysql_query($sql) or die (mysql_error());
if ($total_results !=0) {
while($row = mysql_fetch_object($result))
{
echo '<a href="'. $row->url .'">'.stripslashes(htmlspecialchars($row->name)).'</a><br />';
echo stripslashes(htmlspecialchars($row->description)).'<br /><br />';
}
// Figure out the total number of pages.
$total_pages = ceil($total_results/$max_results);
// Page of page
echo "<div align=right>";
echo "<table border=0 align=right cellpadding=4 cellspacing=1>";
echo "<tr>";
echo "<td class=navigation-cell2>";
echo "Page $page of $total_pages";
echo "</td>";
echo "<td class=navigation-cell1>";
// Build Previous Link
$first = 1;
if ($page != 1) {
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev&cmd=$cmd&words=$searchstring\"><<</a> ";
echo "[ <a href=\"".$_SERVER['PHP_SELF']."?page=$first&cmd=$cmd&words=$searchstring\">First</a> ] ";
}
for ($i = 1; $i <= $total_pages; $i++){
if ($page == $i){
echo "<strong>$i</strong> ";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&cmd=$cmd&words=$searchstring\">$i</a> ";
}
}
// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next&cmd=$cmd&words=$searchstring\">>></a> ";
echo "[ <a href=\"".$_SERVER['PHP_SELF']."?page=$total_pages&cmd=$cmd&words=$searchstring\">Last</a> ] ";
}
echo "</td>";
echo "</tr>";
echo "</table>";
echo "</div>";
break;
} else {
echo 'Sorry, there were no results for ';
echo '"';
echo $searchstring;
echo '"';
echo '.<br />';
}
}
?>
