The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Need help excluding certain terms from database search.

Hi folks -thanks for any input.

I'm running a MySQL database client version: 3.23.58

The database contains a lot of comic strips and is designed to be searchable by keyword (among other things). Each strip's record contains keywords related to its content, and it also includes a list of every character who appears in that strip.

However, I want to restrict people from searching for specific keywords, for example "George", because searching for the word George would bring up nearly all of the strips in the database, as George is in almost all of them - something we're hoping to avoid. With thousands of records, it wouldn't make sense for me to remove all instances of George from the keywords field, either.

Here's the way my query is presently structured. I capture the keywords from an input form, trim the whitespace and then run the query.

Code:

$query = "select * from table where description like \"%$trimmed%\" order by date";

This works well to let people search for the terms we want to allow, like "flower", "flowers", "flowering", but I want to refuse to let people enter "George", "Georg", "orge" etc. and having every George-related strip show up.

I started experimenting with if statements to deny the search based on the value of %$trimmed%, but as you can see this gets problematic quickly especially for characters with longer names. There's got to be a better method.

Thank you - that's certainly a more economical way of doing the coding; I'm hoping to avoid having to come up with a list of all possible combinations of the letters in the characters' names. Is there a way of setting up the array so that $forbidden = "George" or any sequential combination of the letters in the word without having to come up with all the permutations by hand?

For a name like Victoria, there could be hundreds of combinations of search terms used to get around the restriction: ictoria, cto, ictor, vic, vi, ria, you get the idea. Thanks.

Thanks folks. I ended up restricting the search to a max of 50 results for now...there didn't seem to be any way, with the current table structure, to get the effect that was necessary. At least this way I can make them refine their keyword search if they didn't get the results they wanted in the first shot, without exposing all the contents.