Fulltext Mysql search?

[PoorDoggie]

here is my mysql code:

SELECT * FROM pd_shops WHERE MATCH (name, description, keywords) AGAINST ('$q')

How come this dosen't work? I apparantly have a fulltext index on those 3 fields.

I have 1 entry in my db, that is "Casino Tropez". Supposedly, if you were to type in "Casino" it should pick up "Casino Tropez" in the name field, right?

It dosen't

Fulltext search page - www.theshutter.co.uk/poordoggie/search/index.php
"Like" search page - www.theshutter.co.uk/poordoggie/search/indexlike.php

Type "casino tropez" into them both. The like one will work, and the other one wont! MAKE SURE YOU SELECT "BOTH" INSTEAD OF "UK" OR "USA". I DON'T HAVE THE COUNTRIES LOADED YET...

If I use the query:

SELECT * FROM pd_shops WHERE name LIKE '$q'

and type "casino tropez" into the search bar it will find "Casino Tropez", but any squiffy searches won't come out, and it won't search each individual word either.

Can anyone help me, please!

Thanks
Tom

 

[aween]

SELECT * FROM pd_shops WHERE name LIKE '%$q%'

that should work.

 

[PoorDoggie]

does! thanks

Is there any way of:
- ordering that by relevancy?
- making the search terms bold?
- searching more than 1 field?

Thanks
Tom

Oh yea, and how would I time how long it took to execute that query? Thanks a lot for your time!

 

[dgaussin]

Did you try that ?

SELECT * FROM pd_shops WHERE MATCH (name) AGAINST ('$q') or MATCH (description ) AGAINST ('$q') or MATCH (keywords) AGAINST ('$q')

I can't remember if match use an AND or OR logic by default, and no time to check...

 

[Albino]

to order by something, add "ORDER BY variable" on the end.

make the search terms bold? surely thats how you display it, in which case, use html tags <b></b>?

 

[PoorDoggie]

Did you try that ?

SELECT * FROM pd_shops WHERE MATCH (name) AGAINST ('$q') or MATCH (description ) AGAINST ('$q') or MATCH (keywords) AGAINST ('$q')

I can't remember if match use an AND or OR logic by default, and no time to check...

That could be a good idea actually, and that would get the order thing done too.

to order by something, add "ORDER BY variable" on the end.

I want to order by relevance of the search. Not by a variable...

make the search terms bold? surely thats how you display it, in which case, use html tags <b></b>?

How would I single out the search terms?

So, If I have:
"This casino is very good" and I searched for "Casino" I want to be able to single out "casino" in the description and make it bold:

This casino is very good.

 

[dgaussin]

Tom, the fulltext engine on mysql allow to order by relevance. I currently work on that, I'll test tomorrow.

 

[Albino]

oops, sorry, i completely miss understood :D

worse thing is i cant help you

 

[PoorDoggie]

Tom, the fulltext engine on mysql allow to order by relevance. I currently work on that, I'll test tomorrow.

Fulltext dosen't seem to work on any of my scripts/servers. I have 2 mysql servers, all of them seem to allow fulltext indexes (of which I have set up) but they don't seem to work.

Sorry to keep troubling everyone, but I seem to have come across another problem.

Lets say I want to search up all records with "$q" in them, but I also want to filter it by a country. So for instance... if the user selects UK, I want the database to search all records with the search term, and where the country is "UK", or "Both"...

This code won't work:

SELECT * FROM pd_shops WHERE name LIKE '%$q%' OR description LIKE '%$q%' OR keywords LIKE '%$q%' AND country='uk' OR country='both'

becasuse all results with "country='both'" come up regardless of weather they have the search term in them or not.

Thanks be to anyone who can help me, and has already helped me...

Tom

 

[slantednet]

You need to group your last 'or' statement, like so:

SELECT * FROM pd_shops WHERE name LIKE '%$q%' OR description LIKE '%$q%' OR keywords LIKE '%$q%' AND (country='uk' OR country='both')

I believe the way you have it is saying "WHERE name LIKE '%$q%' OR description LIKE '%$q%' OR keywords LIKE '%$q%' AND country='uk' " -*OR-* "country='both'"

 

[The_Inferno]

<?php
$query = "SELECT * FROM search where keywords like $serch ORDER BY hitid";
$array = mysql_query($query);
$items = mysql_num_rows($query);
if($items < 1){
die("nuttin");}
else { echo " Your search returned $items results.";}

while($sql = mysql_fetch_array($array)){
echo "<P><a href=\"".$sql['url']."\">".$sql['title']."</a><BR><HR>".$sql['discription']."</P>}";
?>

 

[PoorDoggie]

<?php
$query = "SELECT * FROM search where keywords like $serch ORDER BY hitid";
$array = mysql_query($query);
$items = mysql_num_rows($query);
if($items < 1){
die("nuttin");}
else { echo " Your search returned $items results.";}

while($sql = mysql_fetch_array($array)){
echo "<P><a href=\"".$sql['url']."\">".$sql['title']."</a><BR><HR>".$sql['discription']."</P>}";
?>

Wow... great idea - a hit list. Items are ordered by popularity! Thanks! :lol:

You need to group your last 'or' statement, like so:

SELECT * FROM pd_shops WHERE name LIKE '%$q%' OR description LIKE '%$q%' OR keywords LIKE '%$q%' AND (country='uk' OR country='both')

This still dosen't work. When "usa" is selected (and the query is:

SELECT * FROM pd_shops WHERE name LIKE '%$q%' OR description LIKE '%$q%' OR keywords LIKE '%$q%' AND (country='usa' OR country='both')

) then the results show UK results too.

Please help

thanks

Know what: scrap that - I won't have a countries thing at the moment... just base it all in UK. When I need to get another country I will get that ccTLD and just use that! woo! all sorted

Thanks for everyone's help... thanks a lot!

 

[slantednet]

Group the rest of the where...

SELECT * FROM pd_shops WHERE (name LIKE '%$q%' OR description LIKE '%$q%' OR keywords LIKE '%$q%') AND (country='usa' OR country='both')