[RESOLVED] search query not returning exact search term

cmp241

hello everyone,

I am trying to "simply" search for a search term in a database. The problem is it isn't simply returning the desire output.

For example, if the database have "billy" under name field, and the user type "b" in the search input box, billy and every other entry starting with b returns.

Here's the code

<?php
.
.
.
$clean = trim($searchterm); //remove whitespace

if($clean == "")
{
echo "you forgot to enter a search term dummy";
}

//connect to database code
.
.
.
//this is the problem, the like keyword, returning every occurrence
$query = "select * from $ttable where $ffield like \"%$clean%\";
.
.
.
?>

I've read the article at wikipedia regarding SQL injection, but that's not my main concern right now, I just want to return the exact search term. http://en.wikibooks.org/wiki/Programming😛HP:SQL_Injection

I also googled and found this article which deals with the search issue and the sql injection problem.
http://iamcal.com/publish/articles/php/search/

I am trying to KISS(keep it simple...), but this article goes into too much details.
Just need to return the entry matching the exact search term.

Thanks,

CMP241

dougal85

$query = "select * from $ttable where $ffield like \"%$clean%\";

May be irrelevanty but you have an error on that line, you are missing the " at the end. but i guess this could be from your formatting of the post only.

Anyway.......

I am trying to "simply" search for a search term in a database. The problem is it isn't simply returning the desire output.

For example, if the database have "billy" under name field, and the user type "b" in the search input box, billy and every other entry starting with b returns.

I'm having trouble with this, it doesnt quite make sense.

what results are being returned?

When they enter 'b' you want all the records or only the first record?

If you can clear that up, i'll be able to help more.

cmp241

dougal85 you are correct, it is missing a double quotation mark, but that was a typo, not my problem, thanks.

$query = "select * from $ttable where $ffield like \"%$clean%\"";

what results are being returned?

When they enter 'b' you want all the records or only the first record?

If you can clear that up, i'll be able to help more.

example database employees, with field called name.

name

billy
sean
mike
sue
brian
curly
moe
brad

if the user input billy, my search result return, billy, brian, and brad.

The desire output should be all occurrences of the search term. example, user input "brian", so the search result should be "brian" and if brian is in the database more than one time then also return all occurrences.

Thanks

dougal85

cmp241 wrote:
if the user input billy, my search result return, billy, brian, and brad.

I don't really understand how that is happening unless you are only sending in the first character from the string.

LIKE '%$var%'

matches any string that has the var in it.

Have you tried echoing out the queries before they run to check they look OK?

cmp241

It's much more worst then I allegedly suspected. This like keyword is very flexible and refer to all occurrences of the search term.

Field 1
Billy
Thomas
alfred
Johnathon
Jessica

If the user search term is "a", it will then return "Thomas, alfred, Johnation, Jessica", since they all have "a" inside, it should return nothing.

Here's the key code:

$query = "select * from $ttable where $ffield like \"%$clean%\"";

if database entry is equal to search term then return the info else return nothing. but its going through each entry character by character.

Yes I did echo the user input and the outcome.

dougal85

Field 1
Billy
Thomas
alfred
Johnathon
Jessica

results you want

Search 'a' returns nothing
Search 'jessica' returns only Jessica?

You want to match the full word only?

$query = "select * from $ttable where $ffield like \"$clean\"";

The % signs are the wild cards.

stewartship

if you want it to match the exact term placed in the search and the exact term only then use the =:

"select * from $ttable where $ffield = '$clean' ";

cmp241

Thanks you two, it was the wildcards, stupid mistake on my end.

Thanks again