searching mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

searching mysql

Post 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...
Yossarian
Forum Contributor
Posts: 101
Joined: Fri Jun 30, 2006 4:43 am

Re: searching mysql

Post 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.
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: searching mysql

Post 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?
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: searching mysql

Post by tomsace »

Thanks alot, just figured it out then seen you had the same answer :)
Great stuff !!
Post Reply