PROS! Simple search on SQL tables! Please help!

brianb

Hello,

I am basically trying to create a search engine that seraches the table selected. I want to be able to control the output.

I am well aware of the LIKE function, but for some reason my code doesn't seem to work.

Could someone have a look here?

<?php 

$sql = "SELECT * FROM Film WHERE \"$field\" LIKE \"$searchString\"";

$res = mysql_query ( $sql ); 

while ( $arr = mysql_fetch_array ( $res ) ) 
{ 
echo "$arr[title]<br>";
} 
?>

Any help would be greatly appreciated!

mrmufin

First, $field shouldn't be wrapped in quotes. Next, LIKE requires at least one %, either at the beginning or the end, or at both the beginning and the end of the statement.

Try something like:

"SELECT * FROM Film WHERE $field LIKE \"$SearchString%\";
to match 'SearchString' or 'SearchStringABC', etc

"SELECT * FROM Film WHERE $field LIKE \"%$SearchString\";
to match 'ThisSearchString' or 'SearchThing'

"SELECT * FROM Film WHERE $field LIKE \"%$SearchString%\";
to match 'SearchString' or 'ThisSearchString' or 'SearchStringABC'

Any help?

And keep in mind that string searches tend to be slow...

bladx

Have you tried:

$sql = "SELECT * FROM Film WHERE $field LIKE '%$searchString%'";

rapmonkey

Well I don't really understand any of this code, but I am also wondering how to create a basic search engine where the user types a word in a text box, then it searches a table, (not exact matches only). How would I do that? (I only need the PHP code, not the HTML code for the form)

bladx

That is what the mysql LIKE command does ...

rapmonkey

I understand that, but I am still confused. Could you give me an example and show what the code means? I would appreciate it.

brianb

Hey guys!!!

Thais was it:

$sql = "SELECT * FROM Film WHERE $field LIKE '%$searchString%'";

Works like a charm! Thanks so much for the help!

Question: So if this type of thing is slow and tasking on the server what's another alternative?

mrmufin

String searching is slow because it takes longer to compare strings than, say, integers. Depending on what you need to search, it may be your only alternative. However, if you have a finite number of things to be searched (say baseball teams), it's much faster to reference each 'team name' with an integer value, and do a select based on the integer id, rather than a search based on the team name.

But like I said, this is not always an alternative. If you're looking for instances of the phrase 'fire truck' in a TEXT column, you probably don't have too many options. However, if you have a finite number of things to seearch from, consider using selects where you can make a definitive equality assessment in the WHERE part of your query.

For small databases, you probably won't notice any huge drawbacks, or advantages, regardless of the method. But as tables grow, things will take longer.

You can get around some of the performance penalties of string searching by creating indexes on your columns that will be searched. All in all, designing data structures can be a big, complex process; and there are zillions of books out there which do a much better job of describing this than I do...

Have fun...

brianb

Thanks! Very informative...

I'll have to look into ALL fo that stuff.

Thanks again.