Creating a Search feature with PHP and SQL

drumdogg

drumdogg

drumdogg

Associate
Joined
6 Mar 2009
Posts
495
HI guys

I have created a database in PhpMyAdmin and would like to create a search feature in my website for users to search for certain things throughout all the tables in the database.

Would like to be able to search by name, date etc.

I have done some research online and found code which i tried to use but couldn't get it working.

Code: 
<? 
 //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("******", "*****", "*******") or die(mysql_error()); 
 mysql_select_db("******") 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 ******** WHERE upper($field) LIKE'%$find%'"); 
 
 //And we display the results 
 while($result = mysql_fetch_array( $data )) 
 { 
 echo $result['Product']; 
 echo " "; 
 echo $result['Date']; 
 echo "<br>"; 
 echo $result['PassFail']; 
 echo "<br>"; 
 echo "<br>"; 
 } 
 
 //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; 
 } 
 ?>

When setting up my search form i have set the action to <?=$PHP_SELF?>.

When i run the search i just get a blank page.

Any suggestions folks or even a better method to use.

Thanks
 
Try putting a space inbetween LIKE and '%

or change it to something like:

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

I'm not sure if this is even where the problem is - I've just had a quick look during lunch :p

:edit: Hold on, I can't see "$field" being defined anywhere?
 
The field value is defined in here in think!
<form name="search" method="post" action="<?=$PHP_SELF?>">
Seach for: <input type="text" name="find" /> in
<Select NAME="field">
<Option VALUE="Product">Product</option>
<Option VALUE="Date">Date</option>
<Option VALUE="PassFail">PassFail</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
Click to expand...
 
I think letting us see how you've defined your variables will give us more insight, particularly $find, $field and $searching.

For example:

Code: 
$searching = $_POST['searching'];
$find = $_POST['find'];
$field = $_POST['field'];
 
Last edited:
Dj_Jestar said:
Looks like he could be using the god awful register_globals directive.
Click to expand...

Yep - My guess is the person who developed the code had that turned on, but drumdogg's configuration (quite rightly) has it turned off, so by doing what Ed has mentioned, could fix it!

Again, I'm still at work, so this is a guess instead of actually sitting down and testing it out! :p
 
I take it by what you guys are saying is that this is the wrong way to go about this?

Ok Ed, i defined my variables the way you have said but no joy:( Tho now im getting page cannot be displayed!!
 
drumdogg said:
I take it by what you guys are saying is that this is the wrong way to go about this?

Ok Ed, i defined my variables the way you have said but no joy:( Tho now im getting page cannot be displayed!!
Click to expand...

Did you put the code at the top of the page? Also, instead of <?=$PHP_SELF?>, try putting the exact url in there instead, or even just the filename of the php script.
 
Yea i put them at the top of the page. I also changed <?=$PHP_SELF?> to the filename of the php script. Now when i run it, it just prints out the php code onto the screen:(

But at least we are getting somewhere:)
 
Sounds like Apache isn't running. I hate to ask, but have you installed a web server? XAMPP for example? Is the file in the HTDOCS folder? Are you calling the file through LOCALHOST?

Edit:

The PHP variable declarations are placed within <?php ... ?> aren't they?
 
I found the problem and feel like an idiot now!! I copied the code of the internet and the PHP tags were not properly opened! It was like this <?....?> instead of <?php...?>

Just as you talked about the tags i realized, don't know how i didn't spot that before.

Thanks again for the help:)
 
Ok now that that is sorted, another question lol

The search query that i done only searches from one table in the database. So how can i search from all tables in the database?
 
Why would you want to? There are (or atleast should be) certain tables you don't want to return results from, lookup tables for example. Do you have good database structure (primary and foreign keys etc)? If so, you can use joins to search through all of the necessary tables, although you would need to think about what you return and how you return it.

Search algorithms are far more complex than people think, and if they aren't then they are likely crap, hard to use search features. Take a bit of time to understand what the above SQL is doing and it should become obvious what you need to do.

Edit: Also, consider using stored procedures rather than inline SQL commands, much quicker and makes your code a lot more readable.
 
Last edited:
sw1ft said:
Why would you want to? There are (or atleast should be) certain tables you don't want to return results from, lookup tables for example. Do you have good database structure (primary and foreign keys etc)? If so, you can use joins to search through all of the necessary tables, although you would need to think about what you return and how you return it.

Search algorithms are far more complex than people think, and if they aren't then they are likely crap, hard to use search features. Take a bit of time to understand what the above SQL is doing and it should become obvious what you need to do.

Edit: Also, consider using stored procedures rather than inline SQL commands, much quicker and makes your code a lot more readable.
Click to expand...

Unfortunately Drumdogg this is all part of the learning curve and it sounds like you're at the bottom. We've haven't even discussed filtering input to prevent attacks but keep going, keep learning, you'll get there soon.
 
Guys thanks for the info.

Filtering is something i never came across before and will now do some research on. Done some PHP at uni but never learned about filtering.
 
Found a few good articles on filtering and now have a better understanding:)

One thing im not sure on. Is it not just data that is being inputed into a database that needs to be cleaned and filtered or is it also data that is being search from databases??
 
You have to assume that all data is dirty so it needs filtering before it goes into the database and needs escaping before you print it to the screen. This way you vastly reduce the risks involved with allowing the world to corrupt your site.

Chris Shiflett has some useful articles but the web is full of guides on this:

http://shiflett.org/articles

Just to add a basic example of filtering and escaping:

Code: 
// filter input
function filterInput($str){
  return strip_tags(mysql_real_escape_string($str));
}
  
// escape output
function filterOutput($str){
  return htmlspecialchars($str);
}

To use the above functions you'd:

Code: 
$searching = filterInput($_POST['searching']);
$find = filterInput($_POST['find']);
$field = filterInput($_POST['field']);

then...

echo filterOutput($searching);
echo filterOutput($find);
echo filterOutput($field);
Google some of the functions like mysql_real_escape_string and htmlspecialchars to find out more.
 
Last edited:
Yea sort of thought it would have to be filtered both ways.

Within the code at the top of the thread there is a little filtering:
Code: 
$find = strtoupper($find); 
 $find = strip_tags($find); 
 $find = trim ($find);

This seems to work aswel, but will take onboard what you also said.

Will work on this for the next day or so and see how i get on.

Thanks again for the info, it has been very helpful:)
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom