Page 1 of 1

Simple search (in theory)

Posted: Tue Apr 24, 2007 1:43 am
by evilchris2003
Hi there

I have a working search page using PHP/MYSQL the problem is with the query below

Code: Select all

$query = "SELECT film_id, film_title, film_actors, film_description, film_year, film_location, film_backup FROM films WHERE (film_title LIKE '%$trimmed_title%' OR film_actors LIKE'%$trimmed_name%' OR film_year LIKE'%$trimmed_year%') ORDER BY film_title";
Im using 3 test records one of which is the movie The Fifth Element, using the current query if i enter 'fifth' into my search it returns all 3 results (the other 2 are The Matrix and The Matrix Reloaded)

I have messed about with this removing the % etc which means you get no results unless the string matches exactly.

Just as a thought should I be using explode() on the string to check each word individually ( PHP Code question I know ) or is there a better way of wording the query

Posted: Tue Apr 24, 2007 3:16 am
by Christopher
If you are getting all the records back then it is probably also matching the name or year values. I would recommend building the SQL programatically -- only adding a condition if the value is actually passed.

Posted: Tue Apr 24, 2007 3:25 am
by CoderGoblin
MySQL does have full text search capabilities, Postgres has it via something called tsearch2. There are restrictions I believe in the MySQL method with the table having to be a certain type (you would have to investigate as I don't use MySQL). This is likely to be a lot faster than searching using LIKE in the select command.

Hope that helps.

Posted: Tue Apr 24, 2007 10:23 am
by mpeacock
When you say this:

Code: Select all

$query = "SELECT film_id, film_title, film_actors, film_description, film_year, film_location, film_backup FROM films WHERE (film_title LIKE '%$trimmed_title%' OR film_actors LIKE'%$trimmed_name%' OR film_year LIKE'%$trimmed_year%') ORDER BY film_title";
What are the values of: $trimmed_title, $trimmed_name , and $trimmed_year?


You say you used the term "fifth" in this, and it returns all three. If that't the trimmed_title value, then your query evaluates to:

Code: Select all

$query = "SELECT film_id, film_title, film_actors, film_description, film_year, film_location, film_backup FROM films WHERE (film_title LIKE '%$fifth%' OR film_actors LIKE'%%' OR film_year LIKE'%%') ORDER BY film_title";
Which will always return the whole table.

Instead, do something like so:

Code: Select all

$query = "SELECT film_id, film_title, film_actors, film_description, film_year, film_location, film_backup FROM films WHERE (film_title LIKE '%$query_term%' OR film_actors LIKE'%$query_term%' OR film_year LIKE'%$query_term%') ORDER BY film_title";

Also - be aware that queries with leading wildcards (contains queries or "ends with" queries) are not indexable. Most RDBMSs (MySQL, Oracle, and Postgres) will always do a full table scan on these types of queries - making them slow down as the table gets larger. Stick with exact match, or begins with queries to take advantage of your indexing scheme, keep the tables small, or use synthetic data that you can index.

Cheers

Posted: Tue Apr 24, 2007 11:23 am
by evilchris2003
many thanks i will try some of the suggestions

Sorry about late reply im at work and shouldnt even be doing this on company time :wink: