[Resolved] Need help with a simple database search

Davidc316

Hi ho,

I'm trying to conduct a simple text search on a mysql database.

The idea is, someone can enter a key word in a form, hit go and then get the results from the search. Sounds easy eh?

Well, I can tell you, from my perspective it's not so easy.

According to the MySQL manual I should be able to conduct my little search by using the command:

$query="select * from pet where match (petname, petdescription)against ('$interest')";
$result=mysql_query($query)
 or die ("Could not execute the mysql query.");

But that simply does not work. When I try running it, all I get is the message "Could not execute the mysql query."

So, can anyone please tell me how to do a simple text search????

Thanks!

(NOTES- my table is called "pet" and the columns I wanted to search are called "petname" and "petdescription". The variable returned by the user from the form is called $interest)

(OTHER NOTES- I've tried both the PHP and the MySQL manual for this one. I am shocked to report that I couldn't find a satisfactory answer.)

thedream

If you want to a good search like your query you have to alter your table and use the "Boolean" mode

update your mysql table with the following mysql command:

ALTER TABLE pet ADD FULLTEXT ('petname', 'petdescription');

and validate, you should see a confirmation message

and for your query you can use:

$query="SELECT pet.* FROM pet where match (petname, petdescription) AGAINST ('$interest' IN BOOLEAN MODE)";

Enjoy !

Marc-André

Davidc316

Thanks.

Could you please tell me excactly what that table alter does and also why would I want to make my search boolean in the first place???

Boolean relates to true or false values and I fail to see what that has to do with this particular query.

Thanks again.

thedream

The "alter" function doesn't alter the table, it just add an index for a full text search on your fields and/or your database.

See the excellent tutorial to that way:

http://www.zend.com/zend/tut/tutorial-ferrara1.php

Enjoy !

Marc-André

Davidc316

Thank you my friend.

It's a very big article but I shall read through it and I'm hopeful I can soon return and mark this thread as resolved.

Davidc316

Ok, I've read it and I have to say that was a great article!!!

Not only did I like the guy's tone and style of writing, but the actual website itself looks very interesting indeed.

I'm still not sure why he executed the search twice when he does this...

$query = " 
        SELECT *, 
            MATCH(title, body) AGAINST ('PHP') AS score 
        FROM articles 
        WHERE MATCH(title, body) AGAINST('PHP') 
    ";

... that's the only bit that I think he didn't explain very well.

The other thing that I'm a bit unsure about is how to do a "like" search for words which contain a certain string. So far, if someone searches my database for "cats" then they get lots of results, but if they do a search for "cat" they get nothing. That's a shame.

Anway, I guess these small issues are beyond the scope of my original question so this thread certainly gets marked as resolved.

Thanks again!

I owe you one!