Page 1 of 1

searching mysql

Posted: Sun Apr 26, 2009 10:55 am
by tomsace
Hey,
I have setup my own search on my games website, it searches keywords that I store in my database under the name 'tags'.
Now... If I store a 'tag' as say pacman, and search 'pacman' it comes up, but if I search 'pacman game' it doesn't find it because I only stored it as 'pacman'. So what I want help with really is setting it so that the search results don't have to be so specific, any one of the search keywords are found under 'tag' in the DB it will show the results...

My code is something like this:

Code: Select all

<?php
$search = mysql_real_escape_string($_GET['search']);
// Performing SQL query
$query = "SELECT * FROM games WHERE tags = '$search' ORDER BY id DESC";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
http://www.zippygame.com try the search, if you search 'pacman' you will see a result because i only stored the keyword 'pacman' in the mysql database. Where as if you search 'super' you will get nothing as I stored the keywords 'super mario world' so you would have to search the full 'super mario world' to get the result. I just want to be able to search 'super' and still get the result...

Re: searching mysql

Posted: Sun Apr 26, 2009 11:22 am
by Yossarian
You have a choice,

1 . standard LIKE search:

detect incoming phrases, ie 2 words, or 3 words.

Break the phrase into words, search for each word.

You may want to discard 'noise' words like game, games, play, player etc.

It would be a good idea to log and monitor all search terms so you can develop appropriate 'noise' words for your own site.

If no match is found using single words with LIKE etc, you could then elect to go on and

2 . do a FULLTEXT search, but for that you'd have to go out and research "mysql fulltext search".

If you wanted to you could create a "layered search" in which the type of search made depends upon the results of the first search etc.

Re: searching mysql

Posted: Sun Apr 26, 2009 11:40 am
by tomsace
Hi,
Thanks for the response, I have done it using:

Code: Select all

$query = "SELECT * FROM games WHERE tags like '%$search%' ORDER BY id DESC";
Is there a way of now searching more than one collumn? Search title, description and tags for example?

Re: searching mysql

Posted: Sun Apr 26, 2009 12:05 pm
by tomsace
Thanks alot, just figured it out then seen you had the same answer :)
Great stuff !!