Simple search (in theory)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
evilchris2003
Forum Contributor
Posts: 106
Joined: Sun Nov 12, 2006 6:43 am
Location: Derby, UK

Simple search (in theory)

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
User avatar
mpeacock
Forum Newbie
Posts: 10
Joined: Thu Apr 12, 2007 9:07 am
Location: Mobile AL

Post 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
User avatar
evilchris2003
Forum Contributor
Posts: 106
Joined: Sun Nov 12, 2006 6:43 am
Location: Derby, UK

Post 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:
Post Reply