search feature for a database
Moderator: General Moderators
search feature for a database
hi everybody.
I have a database that needs the search functionality.
I've heard about full-text search bulit-in mysql.
Are there any other alternatives?
I thought maybe google would offer an API?
I have a database that needs the search functionality.
I've heard about full-text search bulit-in mysql.
Are there any other alternatives?
I thought maybe google would offer an API?
Here's a few places you can read up on it:
http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html
http://www.devarticles.com/c/a/MySQL/Ge ... abilities/
I haven't yet come across a search that I couldn't accomplish with mysql's full-text. That's not to say it doesn't have its drawbacks, but it works.
http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html
http://www.devarticles.com/c/a/MySQL/Ge ... abilities/
I haven't yet come across a search that I couldn't accomplish with mysql's full-text. That's not to say it doesn't have its drawbacks, but it works.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
What are you searching through and for? What is the quantity of data, and how quickly do the searches need to be completed? What technologies do you have access to (are you limited by shared hosting for example)? Apply all those questions to the future are they likely to change? If so, how? If you can provide information on all of that it will probably help you, and others, to find something to suit your needs.
1. I am searching through a database.ole wrote:What are you searching through and for? What is the quantity of data, and how quickly do the searches need to be completed? What technologies do you have access to (are you limited by shared hosting for example)? Apply all those questions to the future are they likely to change? If so, how? If you can provide information on all of that it will probably help you, and others, to find something to suit your needs.
The search form looks like this:
Country: (Dropdown List)
Region: (Dropdown List)
Subject (Dropdown List)
Type of material: (Dropdown List)
Language: (Dropdown List)
Producer (Dropdown List)
Search all fields(Type a word/phrase in this box): (text field)
Note: the last text field searches through every field of a database row. Usually the most important info should be taken from the description field in the database.
Note2: there are probably 15 columns that pertain to this database.
2. There are about 16,000 entries in the db
3. The web server and the db server are dedicated and managed by us, therefore there are no limitations as far as technology goes.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
Well from the sounds of it you can just use the LIKE clause. LIKE is case insensitive and because all your fields are dropdowns they can be indexed and the search will run quickly.
Even better (you might want to check out my accuracy on this) is to have the contents of each drop-down in a separate table foreign key referenced wherever you need them by an integer primary key.
Code: Select all
SELECT `meh` FROM `table` WHERE `country` LIKE '$country' AND ...Even better (you might want to check out my accuracy on this) is to have the contents of each drop-down in a separate table foreign key referenced wherever you need them by an integer primary key.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
this is what I was talking about: http://dev.mysql.com/doc/refman/5.0/en/ ... olean.html
Doing a fulltext search will return rows in order of relevance - as in, how well they match what you've searched. So, the advantage to doing a fulltext search would be that it would still return results if someone typed in "Canada AB" when the proper field values would be "Canada" and "Alberta". If you want those partial matches to show up, go with fulltext. Just a note though - you'll need to set up an index, which will slow down insert queries (though you probably won't even notice).
Fulltext searching will be faster for you than using LIKE (I believe) as fulltext searching uses an index rather than actually checking each row. If you want to duplicate LIKE functionality, just massage the search terms people use, put '+' in front of each word, & use BOOLEAN mode.
If you do use BOOLEAN mode, make sure you do a sort, as it won't do it for you like non-BOOLEAN fulltext searching will.
Fulltext searching will be faster for you than using LIKE (I believe) as fulltext searching uses an index rather than actually checking each row. If you want to duplicate LIKE functionality, just massage the search terms people use, put '+' in front of each word, & use BOOLEAN mode.
If you do use BOOLEAN mode, make sure you do a sort, as it won't do it for you like non-BOOLEAN fulltext searching will.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.