Fulltext mysql search?

PoorDoggie · 09-19-2005, 09: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.

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

xlusive · 09-19-2005, 09:50 AM

Code:

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

that should work.

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

Quote:

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.

Quote:

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...

Quote:

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?

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, 11:09 AM

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

Albino · 09-19-2005, 11:10 AM

oops, sorry, i completely miss understood

worse thing is i cant help you

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

Quote:

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.

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, 12: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, 01: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, 02:35 PM

Quote:

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!

Quote:

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, 02: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, 09:41 AM	#1 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,390 316.50 NP$ (Donate)	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. 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 __________________ █ Exchange your long URL for a much smaller one! █ Free Anonymizer/Proxy █ Taz Martin's blog - Learn all about internet marketing, promoting your website and much more! Last edited by PoorDoggie; 09-19-2005 at 09:46 AM.

09-19-2005, 09:50 AM	#2 (permalink)
xlusive Pro Coder & Designer Join Date: Apr 2005 Location: Netherlands Posts: 964 101.50 NP$ (Donate)	Code: SELECT * FROM pd_shops WHERE name LIKE '%$q%' that should work. __________________ Online Dragonball Game

09-19-2005, 10:05 AM	#3 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,390 316.50 NP$ (Donate)	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! __________________ █ Exchange your long URL for a much smaller one! █ Free Anonymizer/Proxy █ Taz Martin's blog - Learn all about internet marketing, promoting your website and much more!

09-19-2005, 10:21 AM	#5 (permalink)
Albino Munky Designs Join Date: May 2005 Posts: 997 417.00 NP$ (Donate)	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>? __________________ Munky Designs - Web Developers\|\| Erant - Vent Your Rage! \|\| Pow Comics - webcomic hosting + directory

09-19-2005, 11:10 AM	#8 (permalink)
Albino Munky Designs Join Date: May 2005 Posts: 997 417.00 NP$ (Donate)	oops, sorry, i completely miss understood worse thing is i cant help you __________________ Munky Designs - Web Developers\|\| Erant - Vent Your Rage! \|\| Pow Comics - webcomic hosting + directory

09-19-2005, 10:14 AM	#4 (permalink)
dgaussin Senior Member Join Date: May 2004 Location: France Posts: 1,294 85.60 NP$ (Donate)	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, 11:09 AM	#7 (permalink)
dgaussin Senior Member Join Date: May 2004 Location: France Posts: 1,294 85.60 NP$ (Donate)	Tom, the fulltext engine on mysql allow to order by relevance. I currently work on that, I'll test tomorrow.

09-19-2005, 12:57 PM	#10 (permalink)
slantednet NamePros Regular Join Date: Aug 2005 Posts: 214 170.00 NP$ (Donate)	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, 01:31 PM	#11 (permalink)
The_Inferno NamePros Regular Join Date: Sep 2005 Posts: 252 179.85 NP$ (Donate)	<?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, 02:54 PM	#13 (permalink)
slantednet NamePros Regular Join Date: Aug 2005 Posts: 214 170.00 NP$ (Donate)	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 09:09 AM
AdultSearch.us	Monsterman	Adult Domains For Sale	3	06-11-2004 07:54 AM
What should a US Adult Search be going for?	Monsterman	Adult Domain & Site Reviews	0	06-10-2004 12:29 PM
Tutorial: Getting Started With MySQL (The Basics)	deadserious	Webmaster Tutorials	3	04-18-2004 01:17 PM