search?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
AndrewBacca
Forum Commoner
Posts: 62
Joined: Thu Jan 30, 2003 10:03 am
Location: Isle of Wight, UK

search?

Post by AndrewBacca »

what i want to do is allow users of my site it search my database, using keyword(s) or phrases?

for example, if they type 'planets', then it will list any news/articles with the word 'planets' in it. and if they enter '"jupiters moons"' (with the "") it will find only the records with "jupiters moons" some where in it.

im not sure how to do this, so any help would be great

cheers
Andrew
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If you are using MySQL you can use the LIKE keyword:

Code: Select all

SELECT field1, field2, field3 FROM table WHERE field1 LIKE '% keyword %'
http://www.mysql.com/doc/en/String_comp ... ml#IDX1216

Mac
User avatar
AndrewBacca
Forum Commoner
Posts: 62
Joined: Thu Jan 30, 2003 10:03 am
Location: Isle of Wight, UK

Post by AndrewBacca »

cheers, ive got it working :D

Andrew
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

And to get "planet" but not "planetary"

RLIKE '[[:<:]]" . $value . "[[:>:]]'
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

McGruff wrote:And to get "planet" but not "planetary"

RLIKE '[[:<:]]" . $value . "[[:>:]]'
You can still use LIKE for that, as in the example I gave, just put spaces between the keyword and the wildcards (%).

Mac
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

twigletmac wrote:
McGruff wrote:And to get "planet" but not "planetary"

RLIKE '[[:<:]]" . $value . "[[:>:]]'
You can still use LIKE for that, as in the example I gave, just put spaces between the keyword and the wildcards (%).

Mac
Would that find planet in:

"... the red planet. New sentence ..etc.."

or

".. planet-forming .."

ie are non-alphabetic chars like punctuation or dashes treated as spaces?

Not trying to be a smartass - that's why I use RLIKE but maybe I don't need to.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Good point about punctuation, forgot about that... :oops:

I tend to do 'beginning of word' or 'part of word' searches so LIKE is fine but you're right McGruff, use RLIKE for 'this word only' searches.

I was just looking at the example given and not thinking further than that, sorry.

Mac
Post Reply