Getting advanced search form to work - very close

[howster2011]

Hi Guys,

I am trying to get the below code to work - search form with textbox, dropdown and submit button. I found scripts on the web but the addition of the pagination just causes an error. I spent hours tweaking it but alas I cant see the solution. Perhaps someone could help please. I would love to see an example with 1 textbox, 2 dropdowns and a submit button or a link to such a tutorial. My googling has hit a dead end.

 

In the below code I can run a search and the reults are listed on results.php. Results get screwed up when I go to pagination page 2,3,4 etc...I think i need to integrate  $max somewhere in the 

$data = mysql_query("SELECT * FROM users WHERE upper($field) LIKE'%$find%'"); 

Thanks

 

<h2>Search</h2> 
<form name="search" method="post" action="results.php">
Seach for: <input type="text" name="find" /> in 
<Select NAME="field">
<Option VALUE="fname">First Name</option>
<Option VALUE="lname">Last Name</option>
<Option VALUE="result">Profile</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>


//results.php

<? 
// Grab POST data sent from form
$field = @$_POST['field'] ;
$find = @$_POST['find'] ;
$searching = @$_POST['searching'] ;
//This is only displayed if they have submitted the form 
if ($searching =="yes") 
{ 
echo "<h2>Results</h2><p>"; 

//If they did not enter a search term we give them an error 
if ($find == "") 
{ 
echo "<p>You forgot to enter a search term"; 
exit; 
} 

// Otherwise we connect to our Database 
mysql_connect("mysql.yourhost.com", "user_name", "password") or die(mysql_error()); 
mysql_select_db("database_name") or die(mysql_error()); 

// We preform a bit of filtering 
$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Now we search for our search term, in the field the user specified 
$data = mysql_query("SELECT * FROM users WHERE upper($field) LIKE'%$find%'"); 



//This counts the number or results - and if there wasn't any it gives them a little message explaining that 
$anymatches=mysql_num_rows($data); 
if ($anymatches == 0) 
{ 
echo "Sorry, but we can not find an entry to match your query<br><br>"; 
} 

//And we remind them what they searched for 
echo "<b>Searched For:</b> " .$find; 
} 
?> 

<?php 



if(!isset($_REQUEST['pagenum']))
{
$pagenum=1;
}
else
{
$pagenum=$_REQUEST['pagenum'];
}



//Here we count the number of results 

//Edit $data to be your query 



$rows = mysql_num_rows($data); 



//This is the number of results displayed per page 

$page_rows = 4; 



//This tells us the page number of our last page 

$last = ceil($rows/$page_rows); 



//this makes sure the page number isn't below one, or more than our maximum pages 

if ($pagenum < 1) 

{ 

$pagenum = 1; 

} 

elseif ($pagenum > $last) 

{ 

$pagenum = $last; 

} 



//This sets the range to display in our query 

$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;

//This is your query again, the same one... the only difference is we add $max into it

//$data = mysql_query("SELECT * FROM topsites $max") or die(mysql_error());  this works with pagination hmmm


//This is where you display your query results

  //And we display the results 
while($result = mysql_fetch_array( $data )) 
{ 
echo $result['fname']; 
echo " "; 
echo $result['lname']; 
echo "<br>"; 
echo $result['info']; 
echo "<br>"; 
echo "<br>"; 
} 

echo "<p>";


// This shows the user what page they are on, and the total number of pages

echo " --Page $pagenum of $last-- <p>";


// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.

if ($pagenum == 1) 

{

} 

else 

{

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";

echo " ";

$previous = $pagenum-1;

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";

} 


//just a spacer

echo " ---- ";


//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links

if ($pagenum == $last) 

{

} 

else {

$next = $pagenum+1;

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";

echo " ";

echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";

} 

?> 

[gizmola]

Nobody can help you when you provide the code from the part that works, but leave out the code that doesn't.

 

For what it's worth, this query will never be able to use an index.  It will table scan through the entire users table every time you do a search.  That might not be a problem for you if your user database never gets too large, but I thought I'd mention it.  First off you don't need to use upper($field) because mysql indexes are already case insensitive.  Using upper() will cause a tablescan, however, so will LIKE '%something%'; 

[howster2011]

Hi,

The issue is how to apply $max to

$data = mysql_query("SELECT * FROM users WHERE upper($field) LIKE'%$find%'"); 

 

I tried

$data = mysql_query("SELECT * FROM users WHERE upper($field) LIKE'%$find%'$max"); 

 

but the while loop wouldn't display the data when I paged to 2,3,4 - Seemed fine on page 1 of the pagination

 

Thanks