Working on custom search function in Drupal 6 block.
Everything seems to be working correctly, except the pages do not change. The search criteria populates into the url as does the page number. The page number there changes when I click it in the listing, but the results do not change. Here is the code:
<?php
//connect to the database
$db=mysql_connect('host', 'user', 'pass') or die(mysql_error());
//-select the database to use
$mydb=mysql_select_db("db100281_wun");
// The basic SELECT statement
$select = 'SELECT DISTINCT id, b_name, city, state';
$from = ' FROM post_business';
$where = ' WHERE state = "RI"';
$searchtext = $_GET['searchtext'];
if ($searchtext != '') { // Some search text was specified
$where .= " AND b_name LIKE '%$searchtext%' OR city LIKE '%$searchtext%'";
}
// set number of results to display per page (in this case, 10 per page)
$pagelimit = "10";
// run query
$strSQL = mysql_query($select . $from . $where) or die(mysql_error());
// count number of matches
$totalrows = mysql_num_rows($strSQL);
if ($totalrows > 0) {
// determine how many pages there will be by using ceil() and dividing total rows by pagelimit
$pagenums = ceil ($totalrows/$pagelimit);
// if no value for page, page = 1
if ($page==''){
$page='1';
}
// create a start value
$start = ($page-1) * $pagelimit;
// blank matches found
echo "<b>" . $totalrows . " matches found</b><br>\n";
// Showing Results 1 to 10 (or if you're page limit were 15) 1 to 15, etc.
$starting_no = $start + 1;
if ($totalrows - $start < $pagelimit) {
$end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
$end_count = $start + $pagelimit;
}
echo "Results $starting_no to $end_count shown.<br>\n";
// create dynamic next, previous, and page links
/* lets say you're set to show 10 results per page and your script comes out with 12 results.
this will allow your script to say next 2 if you're on the first page and previous 10 if you're on the second page. */
if ($totalrows - $end_count > $pagelimit) {
$var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
$var2 = $totalrows - $end_count;
}
$space = " ";
echo '<table width="600">';
echo '<tr>';
echo '<th width="90%">Publication Name</th>';
echo '<th width="10%"><div align="left">Date</div></th>';
echo '</tr>';
$strquery = $select . $from . $where . " LIMIT ".$start." ,".$pagelimit;
//echo $strquery; // check the correct query is being written
$strSQL = mysql_query("$strquery") or die('Database error: '.mysql_error());
$pubname = ereg_replace('\\[(B|EB|I|EI|L|L=|L=[-_./a-z0-9!&%#?+,\'=:;@~]+|EL|E)?(]|$)', '', $pubname);
while ($publication = mysql_fetch_array($strSQL)) {
echo "<tr valign='top'>\n";
$id = $publication['id'];
$pubname = htmlspecialchars($publication['b_name']);
$date = $publication['city'];
echo "<td><a href=\"pub.php?id=$id\">$pubname</a></td>\n";
echo "<td>$date</td></tr>\n";
}
echo "</table>";
// previous link (if you're on any page besides the first, create previous link)
if ($page>1) {
echo "« <a href='" . $PHP_SELF . "?page=".($page-1)."&searchtext=$searchtext' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
}
// dynamic page number links
for ($i=1; $i<=$pagenums; $i++) {
if ($i!=$page) {
echo " <a href='" . $PHP_SELF . "?page=$i&searchtext=$searchtext' class=main>$i</a>";
}
else {
echo " <b>[".$i."]</b>";
}
}
// next link (if the page you are on is less than the total amount of page numbers, there are more pages left)
if ($page<$pagenums) {
echo "" . $space . $space . $space . $space . " <a href='" . $PHP_SELF . "?page=".($page+1)."&searchtext=$searchtext' class=main>Next " . $var2 . "</a> »";
}
// LIMIT 0,10 will start at 0 and display 10 results
// LIMIT 10,5 will start at 10 and display 5 results
/* now you can do whatever you'd like with this query. it will only output ten results per page.
change the $pagelimit variable to whatever to output more than 10 result per page. */
}
else
echo "No results found. <a href=\"search-browse.php\">Search again</a>";
?>
