Help needed with simple search script

Hello,

I have a basic search script that i use for my new site.

All data is stored in 1 database.

I want to be able to search the database using 2 fields (catagory and title).

My search table consists of a drop down field named category and a text field named search and of course a submit button.

The following script has worked for me but i'd like to add the ability to search via catagory as well.

Could you please give me some pointers on how to do this and what to add to my existing script.

My script is as follows.

<?
{
include("../includes/dbconnect.php");
}	
mysql_select_db("my_db", $con);

$search=$_POST["search"];

$result = mysql_query("SELECT * FROM downloads WHERE title LIKE '%$search%' ORDER by title");

while($row = mysql_fetch_array($result))
{	
  
   $title=$row["title"];
   
echo "<results table goes here>";
}
?>

Code (markup):

Any support would be appreciated.

Regards

 

Ok, so do you want it so the search is by title and category or within a category? Or an either/or type of deal?

 

First off, what you're doing is extremely dangerous as far as potential SQL injection is concerned. You need at the very least to use mysql_real_escape_string on the search input. You would be better off, cleaning the input at this part $search=$_POST["search"]; using a regular expression to strip off potentially dangerous code. As is, your script is completely open to SQL injection.

On to the search question, without knowing your DB structure, something like this seems appropriate:

$cat = $_POST['cat'];
$result = mysql_query("SELECT * FROM downloads WHERE category = '".mysql_real_escape_string($cat)."' AND title LIKE '%".mysql_real_escape_string($search)."%' ORDER by title");

PHP:

 

OK I would like users to be able to select a category then enter their search keywords.
The script should see that they're looking for x in cat y and only select data which is in cat y with the similar description of x. If that makes sense.

Thanks for the heads up about the vulnerability. I'll act on your suggestion.

Regards

 

Work this over as needed but here:


Create a file called func.php

<?

function cleanse($string)
{
        return str_replace('"', '\"', strip_tags($string));
}

function esc ($s) {
        return escapeshellcmd($s);
}
?>

Code (markup):

Then main.php

<?
include "func.php";
if ($searchme){
include("../includes/dbconnect.php");
mysql_select_db("my_db", $con);

$search=cleanse($_POST["search"]);

$result = mysql_query("SELECT * FROM downloads WHERE category = '%".esc($cat)."%' AND title LIKE '%".esc($search)."%' ORDER by title");

	while($row = mysql_fetch_array($result))
	{	
  
   	$title=$row["title"];
   
	echo "<results table goes here>";
	}
}
?>
<form method="post">
<select name="cat">
<option value="cat1" value="<? echo $cat; ?>">Category1
</select>
Enter text to serach 
<input name="search" value="<? echo $search ?>">
<input type="submit" name="searchme" value="Search">
</form>

Code (markup):

 

OK I've implemented the above into my search page. When I search it returns no results.

So I altered:

$result = mysql_query("SELECT * FROM downloads WHERE cat = '%".esc($cat)."%' AND title LIKE '%".esc($search)."%' ORDER by title");

Code (markup):

to read:

$result = mysql_query("SELECT * FROM downloads WHERE title LIKE '%".esc($search)."%' ORDER by title");

Code (markup):

This now works fine but i'm back to square one.

The Category seems to be the error.

Any ideas as to why this is happening?

Regards

 

Did you assign cat? Did you echo all the passed parameters to see what the query sting looked like?

 

$result = mysql_query("SELECT * FROM downloads WHERE cat = '%".esc($cat)."%' AND title LIKE '%".esc($search)."%' ORDER by title");

Code (markup):

i guess you should use

cat like '%".esc($cat)."%' 

Code (markup):

'cat =' type query will look for the '%' character in the string.

As you select categories from drop down box, why are you using % on categories anyway ?

 

Hi,

I changed my script as above and I can search using the Category drop down field now, however when I select a different category and use the same search keyword i get the same results, this shouldn't bring the same results. Is this because of 'LIKE' opposed to '='?

Regards

 

Do not know why the results are like that.
Have you printed out the prepared SQL statement to see the actual query being executed ?

 

I'm not 100% sure what you mean there.

Just a novice.....

Could you please explain?

 

I am no expert either. Just learning along with you.

This is what I usually do -

$sqlstring = "SELECT * FROM downloads WHERE cat = '%".esc($cat)."%' AND title LIKE '%".esc($search)."%' ORDER by title" ;
print $sqlstring ;
$result = mysql_query($sqlstring;);

Code (markup):

Now the sql being sent to database will print out in the browser.
Once the problem is solved, you can comment out / remove the print statement.

cheers,
Govind