Fulltext mysql search?

PoorDoggie · 09-19-2005, 10:41 AM

here is my mysql code:

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.
????: NamePros.com http://www.namepros.com/programming/125006-fulltext-mysql-search.html

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 - http://www.theshutter.co.uk/poordogg.../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:

Code:

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 · 09-19-2005, 10:50 AM

Code:

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

that should work.

PoorDoggie · 09-19-2005, 11:05 AM

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 · 09-19-2005, 11:14 AM

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 · 09-19-2005, 11:21 AM

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 ?

PoorDoggie · 09-19-2005, 12:03 PM

Originally Posted by 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...

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

Originally Posted by Albino

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

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

Originally Posted by Albino

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

How would I single out the search terms?
????: NamePros.com http://www.namepros.com/showthread.php?t=125006

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 · 09-19-2005, 12:09 PM

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

Albino · 09-19-2005, 12:10 PM

oops, sorry, i completely miss understood

worse thing is i cant help you

PoorDoggie · 09-19-2005, 12:31 PM

Originally Posted by dgaussin

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.
????: NamePros.com http://www.namepros.com/showthread.php?t=125006

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:

Code:

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 · 09-19-2005, 01:57 PM

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

Code:

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 · 09-19-2005, 02:31 PM

<?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 "<a href=\"".$sql['url']."\">".$sql['title']."</a> <HR>".$sql['discription']."}";
?>

PoorDoggie · 09-19-2005, 03:35 PM

Originally Posted by 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 "<a href=\"".$sql['url']."\">".$sql['title']."</a> <HR>".$sql['discription']."}";
?>

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

Thanks!

Originally Posted by slantednet

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

Code:

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:

Code:

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 · 09-19-2005, 03:54 PM

Group the rest of the where...

Code:

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

09-19-2005, 10:41 AM	THREAD STARTER #1 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,408	Fulltext mysql search? here is my mysql code: 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. ????: NamePros.com http://www.namepros.com/programming/125006-fulltext-mysql-search.html 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 - http://www.theshutter.co.uk/poordogg.../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: Code: 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 Last edited by PoorDoggie; 09-19-2005 at 10:46 AM.

09-19-2005, 10:50 AM	#2 (permalink)
aween Pro Coder & Designer Join Date: Apr 2005 Location: Netherlands Posts: 967	Code: SELECT * FROM pd_shops WHERE name LIKE '%$q%' that should work. __________________ aween web development

09-19-2005, 11:21 AM	#5 (permalink)
Albino Munky Designs Join Date: May 2005 Posts: 996	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>? __________________ Toddish.co.uk - Portfolio/Blog Powcomics.com - Webcomic Hosting/Directory Erant.co.uk - vent your rage!

09-19-2005, 12:10 PM	#8 (permalink)
Albino Munky Designs Join Date: May 2005 Posts: 996	oops, sorry, i completely miss understood worse thing is i cant help you __________________ Toddish.co.uk - Portfolio/Blog Powcomics.com - Webcomic Hosting/Directory Erant.co.uk - vent your rage!

09-19-2005, 01:57 PM	#10 (permalink)
slantednet NamePros Regular Join Date: Aug 2005 Posts: 214	You need to group your last 'or' statement, like so: Code: 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'" __________________ OnlineGames.net

09-19-2005, 11:05 AM	THREAD STARTER #3 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,408	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!

09-19-2005, 11:14 AM	#4 (permalink)
dgaussin Senior Member Join Date: May 2004 Location: France Posts: 1,226 Follow @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...

09-19-2005, 12:09 PM	#7 (permalink)
dgaussin Senior Member Join Date: May 2004 Location: France Posts: 1,226 Follow @dgaussin	Tom, the fulltext engine on mysql allow to order by relevance. I currently work on that, I'll test tomorrow.

09-19-2005, 02:31 PM	#11 (permalink)
The_Inferno NamePros Regular Join Date: Sep 2005 Posts: 471 Follow @scriptzshop	<?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>}"; ?>

09-19-2005, 03:54 PM	#13 (permalink)
slantednet NamePros Regular Join Date: Aug 2005 Posts: 214	Group the rest of the where... Code: SELECT * FROM pd_shops WHERE (name LIKE '%$q%' OR description LIKE '%$q%' OR keywords LIKE '%$q%') AND (country='usa' OR country='both') __________________ OnlineGames.net

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Googlism - What does google think of you?	deadserious	The Break Room	55	12-15-2005 10:09 AM
GP0.com Taking Offers	.X.	Domains For Sale - Make Offer	4	08-16-2005 09:56 PM
Tutorial: Getting Started With MySQL (The Basics)	deadserious	Webmaster Tutorials	3	04-18-2004 02:17 PM