Page 1 of 1
How to query MySQL for similar values?
Posted: Sat Mar 19, 2011 1:43 pm
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.
Re: How to query MySQL for similar values?
Posted: Sun Mar 20, 2011 3:47 pm
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.

Re: How to query MySQL for similar values?
Posted: Sun Mar 20, 2011 4:15 pm
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.

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?
Re: How to query MySQL for similar values?
Posted: Sun Mar 20, 2011 4:50 pm
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.

Re: How to query MySQL for similar values?
Posted: Sun Mar 20, 2011 5:08 pm
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

Re: How to query MySQL for similar values?
Posted: Sun Mar 20, 2011 5:14 pm
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)

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
Re: How to query MySQL for similar values?
Posted: Sun Mar 20, 2011 5:18 pm
by Jonah Bron
I don't know how to implement this, but the following seems like a good way to order search results.
- Exact matches
- All words
- 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.
Re: How to query MySQL for similar values?
Posted: Sun Mar 20, 2011 5:22 pm
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.

Re: How to query MySQL for similar values?
Posted: Sun Mar 20, 2011 5:26 pm
by Jonah Bron
Okay, great. I added two more sentences to my reply there.
Re: How to query MySQL for similar values?
Posted: Sun Mar 20, 2011 5:37 pm
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.

Gotta finish the project first and then I can improve it.
