Topic awaiting preservation: MySQL search engine.

I'm trying to write a php search engine for a small table. This is the main part of my code:

function performSearch($string){

       

        $result=mysql_query("SELECT * FROM lannotes WHERE MATCH(title,body) AGAINST('".$string."')");

		

		

		while ( $row=mysql_fetch_array($result) ){ 

		    echo $row[title];

		 

		 }  

    }

This code works fine as long as there is only one row that contains the search string. As soon as there is more than on row containing the string nothing gets displayed.

I'm stumped.... could anyone point me towards a solution, please?



/* Sure, go ahead and code in your fancy IDE. Just remember: it's all fun and games until someone puts an $i out */

I believe that there is a rule where your search string can not appear in more than 50% of your query space. This means that you should query at least 3 rows, with each row containing different data, that way you will not have a 50% problem. I am told that this is good for larger databases, where you could spend a lot of overhead searching for each instance of 'a' in the text. You also didn't state how you constructed your table. You must specify fulltext(title,body) in your table creation or you must alter your table so that those two rows are both in the fulltext realm of control. If you specify them on their own it will not search correctly. You should also make sure that your seach string does not contain any of the stop values, such as -,~ or + I think there may be some others, that can cause funky behavior.

The other thing I notice that might not have any relevance is that your echo statement is echo $row[title]; In this instance I am imagining title must be a variable from somewhere, if it is not I think it might default to an integer with a value of 0. I am not sure I would call it via, echo $row['title']; I am not sure if this is valid but it might be something to look at anyways.

-Dan-

I used the ALTER statment after I made the table, because I had never even heard of fulltext searching when I got started.

Here is my table:


I've gotten into the lazy habit of not using the quotes in my arrays, because they seem to work fine without and sometimes the quotes get clumsy in certain constructs.

Since I ......Wait it works now!!!!(I was multi-tasking)

I added one more row and it worked perfectly. Thanks WarMage! I spent most of the day bouncing from website to book to website, and your suggestion is what made this happen for me. Drop by my cell for a beer anytime.....

/* Sure, go ahead and code in your fancy IDE. Just remember: it's all fun and games until someone puts an $i out */

[This message has been edited by norm (edited 02-24-2004).]

norm: I looked itno this a while back and if you look at the online manual:
http://www.mysql.com/doc/en/Fulltext_Search.html

You'll see that a lot of people have reported problems with this when you have a lot of data (your problem is also answered in the top user comment) - I use complex LIKE statements instead. If you aren't going to be working with a lot of data then this might be OK but I thought I'd warn you so that you know what is happening if you run into trouble later

___________________
Emps

The Emperor dot org

Emps:

That page was one of my first stops. I read the part about needing at least 3 rows and thought 'Well I have already have 3 rows...' and moved on. Kind of embarrassing to admit.

Thanks for the warning about using this with large data sets, I may need to reconsider my approach.

As usual, once the momentary elation of having my code do what it should wears off and I stop patting myself on the back for being so clever (as you can tell I'm easy to impress), I realize how truly clumsy my code is.

Oh well, even if I have to use the big crayons, I'm really glad I get to color......

/* Sure, go ahead and code in your fancy IDE. Just remember: it's all fun and games until someone puts an $i out */

hm. turn on error reporting norm. Error_reporting(E_ALL) - no see how fine your arrays without the quotes actually are. (PHP defaults undefined constanst to their string value. But that's not well defined behaviour, and more or less just an accident of the current implementation. So I for one would not rely on it).

so long,

Tyberius Prime

Edit: Emps, I've just reread that page, since the grail also uses the mysql fulltext search. Guess what: These guys had 1.5 * 10^6 + rows in their table and my only guess is that their table contains very different data from your normal document. Others had even bigger databases and terribly fast search results. The grail has about 500 megs of data, maybe about 250 of them actual text to search through. It searchs through that in half a second. Go figure.

[This message has been edited by Tyberius Prime (edited 02-24-2004).]

Half a second is a really long time in computer terms. The problem with that is that is 2 simultaneous queries take a second, and then 4 takes two seconds, and 8 takes four second, and so on, eventually that adds up to some real time, or real issues.

Imagine for instance your grail gets slashdotted, then you are in for some problems.

-Dan-