CODEWITHSUNDEEP
phpmysqlsqlsearch
Thomas Lai
Thomas Lai

Reputation: 895

Is it possible to treat hyphens and spaces the same in SQL?

So I'm working on a search function for a social networking site, and it searches user posts. Some users like to put hyphens instead of spaces, but I would like for the search function look for both hyphens and spaces in a result.

For example, if they have a post named "SQL-IS AWESOME" and I search for "SQL IS AWESOME", can I still find that post? I tried using 2 sql queries, one for the original search query, and one modified to change all spaces to hyphens.

But if I search "SQL IS-AWESOME" it still won't find it. Is there an easier way?

My current code:

$sql = "SELECT * FROM posts 
WHERE (post_title='".$query."' 
OR post_title LIKE '%".$query."' 
OR post_title LIKE '%".$query."%' 
OR post_title LIKE '".$query."%') 
".$locquery."
".$cat."
ORDER BY date DESC
LIMIT 18";

Upvotes: 0

Views: 506

Answers (1)

MrMarlow
MrMarlow

Reputation: 856

As someone has suggested, you could just adapt and use fulltext searching.

If you choose to take this route, you will need to enable fulltext searching on the fields required.

I'll assume you will check post_title and post_body (?), which needs you to run this;

ALTER TABLE `posts` ADD FULLTEXT KEY `post_search` (`post_title`,`post_body`);

When that is done, your search query can easily be edited to become;

$sql = "SELECT * FROM `posts` WHERE MATCH(post_title,post_body) AGAINST '$search'";

If you'd like better matching, it is also possible to give it a score and order by that, which would require code similar to this:

$sql = "SELECT *, MATCH(post_title, post_body) AGAINST ('{$search}') AS score ".
       "FROM `posts` WHERE MATCH(post_title, post_body) AGAINST ('{$search}') ORDER BY `score` DESC";

--- NOTES

For the search, you need to work out how you will be searching.

In the last instance I used similar, I simply had a form for the search term (Named "Search") resulting in $_POST['search'] being sent to the server.

I then used;

$search = (array_key_exists('search', $_POST) && is_string($_POST['search'])) ? mysql_real_escape_string($_POST['search'], $c) : FALSE ;
if ($search) {
    // Do the fulltext query requires (See above)
}

Since fulltext search will disregard the hyphen, you are left with just spaces, which works great for fulltext, if you opt to use scored results.

Upvotes: 2

Related Questions