trouble with php/mysql search script

[tengri]

Got a real trouble with php/mysql search script, pagination 1, 2, 3 etc. the script does not work correctly, for example starts results countdown from 2, I don’t know how. Could you please look at the code and tell what I am doing wrong:

<?php
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=3;
// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
// Build SQL Query
$query = "select * from tengri where msg like \"%$trimmed%\"
order by msg"; // EDIT HERE and specify your table and field names for the SQL query
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
$total_pages = ceil($numrows / $limit);
// If we have no results, offer a google search as an alternative
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=1;
}
// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>You searched for: &quot;" . $var . "&quot;</p>";
$count = 1 + $s ;
echo '<p>Results found: '.$numrows.' </p>';
echo '<table cellspacing="2" cellpadding="2" border="0" width =155>';
{
while ($row = mysql_fetch_array($result))
{
echo "<tr>";
$title = $row["title"];
$id = $row["id"];
echo "<td colspan=25 bgcolor=green>$count</td>";
echo "<td colspan=4 bgcolor=\"#ffffa0\"><a href ='tur.php?show=$id'>$title</a></td>";
echo "</tr>";
$msg = $row["msg"];
echo "<tr>";
echo '<td colspan=155 bgcolor=red>'.$msg.'</td>';
echo "</tr>";
$count++ ;
} #end while
echo "</table>";
$currPage = (($s/$limit) + 1);
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt;
Prev </a>&nbsp&nbsp;";
}
for($i=1; $i<= $total_pages; $i++){
if($page == $i)
{
echo "<strong><font color=#FF0000>$i</font></strong>";
}
else
echo "<a href=\"$PHP_SELF?s=$i&q=$var\">[$i]</a> ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division

{
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next &gt;&gt;</a>";
if ($numrows!= 0)
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
}
}
}
?>

Help, please, I was completely wasted this day. Thanks a lot in advance!

[tengri]

Did it myself at last!
Here is the code (if someone is interested):

<?php
mysql_connect("localhost", "root", "");
mysql_select_db("tengri");
echo '<form action='.$_SERVER['PHP_SELF'].' method="get">
<input type="text" name="search">
<input type="submit" name="Submit" value="search">
</form>';
if($search)
{
if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}
// Define the number of results per page
$max_results = 3;
// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);
$sql = mysql_query("SELECT * FROM tengri WHERE msg LIKE '%$search%' LIMIT $from, $max_results");
while($row = mysql_fetch_array($sql)){
// Build your formatted results here.
// replace title with desired output
echo $row['title']."<br />";
}
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM tengri WHERE msg LIKE '%$search%'"),0);
// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);
echo "<center>$total_results Total Results<br />";
// Build Previous Link
if($page > 1){
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev&search=$search\">Previous</a>&nbsp;";
}
for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "[$i]&nbsp;";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&search=$search\">$i</a>&nbsp;";
}
}
// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next&search=$search\">Next</a>";
}
echo "</center>";
}
?>

[ergophobe]

Thanks for the followup. I didn't look through the code really, but one thing did jump out as a possible improvement.

You have

if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}

Since this is user input really (in that someone can type in the URL), I would prefer to be safe against negative numbers and non-integers, so I would do something more like this

if (!empty($_GET['page']) && is_numeric($_GET['page'])) && $_GET['page'] > 1)
{
$page = (int) $_GET['page'];
}
else
{
$page = 1;
}