How to query MySQL for similar values?

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
User avatar
mareksl
Forum Newbie
Posts: 18
Joined: Tue Mar 08, 2011 12:24 pm
Location: Poland

How to query MySQL for similar values?

Post by mareksl »

Hi all!
I am creating a search function on a website I am working on.
Of course when a user searches something, he doesn't usually type in the exact text he wants to search, but wants to get similar results too.
However my query only searches for the exact string put in the search box.
How can I change this, so that it searches for similar values and orders them by relevance??
Here is my query:

Code: Select all

<?php
$search = $_GET["search_query"];
$con = mysql_connect("localhost","*****","*****");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db_kitchentube", $con);

$result = mysql_query("SELECT * FROM videos
WHERE title = \"$search\"");

while($row = mysql_fetch_array($result))
  { ?>
RESULTS GET DISPLAYED HERE
<?php
	}
mysql_close($con);
?>
EDIT: I guess this could be done with full text search, but I have some trouble implementing it.
User avatar
mareksl
Forum Newbie
Posts: 18
Joined: Tue Mar 08, 2011 12:24 pm
Location: Poland

Re: How to query MySQL for similar values?

Post by mareksl »

OK, I kinda got the first step. i got the fulltext search to work, i just needed to add "in boolean mode"
but still if i put in a spelling mistake (ex. "lorem ipsu" instead of "lorem ipsum") it doesn't return any result. can i change this???

EDIT: And also how to display "No results" if there are none? I know this would be easy, but I have some trouble doing it. :oops:
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: How to query MySQL for similar values?

Post by Jonah Bron »

mareksl wrote:EDIT: And also how to display "No results" if there are none? I know this would be easy, but I have some trouble doing it. :oops:
For that, use mysql_num_rows().

http://php.net/mysql-num-rows

That tells you how many results were found. If zero, just output your message.
mareksl wrote:OK, I kinda got the first step. i got the fulltext search to work, i just needed to add "in boolean mode"
but still if i put in a spelling mistake (ex. "lorem ipsu" instead of "lorem ipsum") it doesn't return any result. can i change this???
Could you post your SQL query?
User avatar
mareksl
Forum Newbie
Posts: 18
Joined: Tue Mar 08, 2011 12:24 pm
Location: Poland

Re: How to query MySQL for similar values?

Post by mareksl »

Code: Select all

<?php
$search = $_GET["search_query"];
$con = mysql_connect("localhost","*****","*****");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db_kitchentube", $con);

$result = mysql_query("SELECT * FROM videos
WHERE MATCH (title, description) AGAINST (\"$search\" in boolean mode)");

while($row = mysql_fetch_array($result))
  { ?>
Results are displayed here...
<?php
	}
mysql_close($con);
?>
Btw. thanks for the tips on my last topic, you really helped, I got all the stuff I needed figured out, just this search left now. :)
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: How to query MySQL for similar values?

Post by Jonah Bron »

Unfortunately, this part I can't help you on. I've never used full-text. I'll keep an eye on this thread though, always learning something new.
Btw. thanks for the tips on my last topic, you really helped, I got all the stuff I needed figured out, just this search left now. :)
Sure :)
User avatar
mareksl
Forum Newbie
Posts: 18
Joined: Tue Mar 08, 2011 12:24 pm
Location: Poland

Re: How to query MySQL for similar values?

Post by mareksl »

okay thanks!
i think spelling mistakes and such can be dealt with using some kind of dictionary, probably like google uses one. but that would be kinda hard to implement i guess and it would be a load on the server, and as i am planning to get a VPS for now, i wouldn't want much load if i want my page to be somewhat fast (regarding that I will be streaming videos) :P
maybe in the future.
but for now the search works great if you don't make any spelling mistakes or search for "similar" values xD
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: How to query MySQL for similar values?

Post by Jonah Bron »

I don't know how to implement this, but the following seems like a good way to order search results.
  1. Exact matches
  2. All words
  3. Most word matches - least word matches
That last one is important, because they may misspell one word, but optimally the rest of the words match correctly and turn up the desired result. If you want to make it even more complex, find a table/list of articles or conjunctions (might be one here) and strip those out of the search. That's will eliminate a lot of irrelevant results.
Last edited by Jonah Bron on Sun Mar 20, 2011 5:25 pm, edited 1 time in total.
User avatar
mareksl
Forum Newbie
Posts: 18
Joined: Tue Mar 08, 2011 12:24 pm
Location: Poland

Re: How to query MySQL for similar values?

Post by mareksl »

When you use fulltext search, it automatically orders it by relevance as fas as I know
And if you misspell one word it still searches for the rest, so that is taken care of. :P
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: How to query MySQL for similar values?

Post by Jonah Bron »

Okay, great. I added two more sentences to my reply there.
User avatar
mareksl
Forum Newbie
Posts: 18
Joined: Tue Mar 08, 2011 12:24 pm
Location: Poland

Re: How to query MySQL for similar values?

Post by mareksl »

Fulltext seaching also filters out words shorter than 4 characters. This filters out most of the trash, but can of course be inconvenient sometimes when you search for something short.
Thanks for the idea! I will look into it when I can. :D Gotta finish the project first and then I can improve it. :P
Post Reply