[RESOLVED] MySQL search function

[ddrfreek]

I'm using a search function I found online, and I modified it to fit in with my website. Now, I can pretty much understand everything it does, except for this one part, and this part seems to be the problem. When I enter a search, and it searches articles by their title, it will only return one word titles. Anything with multiple words, even if it's the exact same as the title in the database won't come up. This is part of the script I'm using

$arrWords = explode(" ", $search_keywords);

if(sizeof($arrWords) == 0 || $search_keywords == "")
	{
	echo "Error!</b></td></tr>
	<tr>
	<td class=\"text\">";
	echo "You didn't enter any keywords";
	}
else
	{
	include ("rdatabase.php");

for($i = 0; $i < sizeof($arrWords); $i++){
$query = "SELECT id FROM games WHERE title = '{$arrWords[$i]}'";
$result = mysql_query($query);

The script works off of two queries, and I'm almost positive this is the one causing the problem. Any idea on how to change this around so it will select all ids where the title contains the keyword? Thanks for your help.

[severndigital]

that is an interesting result you are getting. I have had much success with the

MATCH() AGAINST() statements in MYSQL. not only will it search the entire db but it will automatically sort the results by relevance.

with some minor tweaking of your code, you could use them

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

hope that helps.
chris

[ddrfreek]

Alright, thanks for that I'm sure it will be useful. I'm actually editing my code right now so it will work with the match and against. However, there's just one thing I gotta ask.

for($i = 0; $i < sizeof($arrWords); $i++){
$query = "SELECT id FROM games WHERE title = '{$arrWords[$i]}'";

I can't quite figure out what that part is trying to do, and I need to know if it's necessary to include in the MATCH, AGAINST queries.

[ddrfreek]

Well I just cut out that last part, and my script seems to be working, partially. I have it set up to do one of four different queries, based on user input, and the only one that is working is the one that just selects everything from the database (and doesn't use MATCH and AGAINST). The other three are continuously returning no results.

The other three queries all look something like

SELECT id, title, console, rating, publisher FROM games WHERE MATCH (console) AGAINST ('+$search_console' IN BOOLEAN MODE)

None of my fields have the fulltext index, could that be the problem?

[jdiver]

The reason why only 1 word titles are being returned is because you're asking for the title to match your search term exactly by using the = symbol. So if you search for "Dog" it will only return entries with the exact title "Dog", and won't return "Dog Cat", "Puppy Dog", "Raining Cats and Dogs", etc. Try this instead:

$query = "SELECT id FROM games WHERE title LIKE '%" . $arrWords[$i] ."%'";

[ddrfreek]

Alright, thanks. I did that, and it seems to be working, but the search has suddenly become really slow. Is there anything I can do to speed it up?

Edit: Nevermind that, I think it's just my internet acting up. Thanks for all the help.

[jdiver]

Could you post the structure of your database? (If you're using phpMyAdmin, then you can use the export function to export just the structure and then post that here.) How many records are there in the table?

[ddrfreek]

CREATE TABLE games (
id int(4) NOT NULL auto_increment,
title varchar(45) NOT NULL default '',
author varchar(10) NOT NULL default '',
console varchar(15) NOT NULL default '',
rating tinyint(2) NOT NULL default '0',
publisher varchar(25) NOT NULL default '',
path varchar(20) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM AUTO_INCREMENT=66 ;
There are 65 records so far, but I have a feeling that the lag is on my end.

[jdiver]

The structure looks fine, and with only 65 records the query itself should be nearly instant. (As an example, I ran the same type of query on a table with about 60,000 records and it took 0.0006 seconds.) Try running the query directly on the database and see what the result is. I suspect it's not the query, but something else in your code.

[ddrfreek]

It must have been something with my internet connection, because now it works very quickly for me, and other people. So, I guess that's that. Thanks for your help.

[Roger_Ramjet]

With no index on any of the columns you are doing a table scan for any search - ie read every record and compare.

If you want a decent search then read up on FULL TEXT SEARCH in the manual.