designing mysql db to be searchable

[djemmers]

Hi,

I want to make a database in mysql and a website that searches in het entries.

It will be a kind of event database for tournaments.
with data like:
title,description,level_age,city,date,...

To make a database that stores those data is not that difficult. The problem is making it so that it can handle multiple languages.

and more it really needs to be searchable for the fields
title and description together
level_age
city
date
country
province
category

I made 2 designs in DBdesigner4 if anyone could take a look at them and give me some pointers... please do.

I would like to use php and mysql and if needed javascript and ajax to create the website/forms/resultpages...

[phillw]

I have also set up a db that has to be searchable - I'm using a mix of FULLTEXT, parametised input & column searching (Oh, the joys of full text..)

The test area is currently in 'debug' mode - so, if you'd like to have a look and see how it returns the search string in different circumstances, it is displaying the search query that it has carried out to create the results.

In my case I have to be able to search for 'parts' in my case accross part-numbers, descriptions, suitable for, sizes etc.

To get some test data to try searches with, the following pages are populated with data.
Ignition and Door Lock Keys, Malleable Iron Fittings, Flexible Rubber Mountings, Oils and Lubricants, Pin Spanners, Stihl TS400, Partner K650/K700, and Dolmar DPC6400.

As you are looking to search accross multiple columns, I'm reckoning you're going to have to 'dip your toe' into FULLTEXT - It's a bit wierd at first, but once you get the hang of the rules it's quite painless

If you're going to be using foreign words, then it'll be FULLTEXT searching in BOOLEAN mode.

The MySQL resource on the joys of this can be found at, there is also a comment on how accents interact within the search.

MySQL :: MySQL 5.0 Reference Manual :: 11.8.2 Boolean Full-Text Searches

If you are searching in different languages then you really must build up a stop-list for that language, else your full-text index is going to be BIG !!! The resources are within the above link.

the link to my test area is © 2009 M.G. Judd Ltd Find Parts You're welcome to have a 'play' with differing searches - if any of the coding it returns looks like it may be of help to you, please feel free to ask !!

Regards,

Phill.

[djemmers]

thanx phillw,

That is a big part of the info I need.
tha data in my searchform (not working) is here: basketbal.tornooi.net/searchform.php
It is only the concept nothing more.
my text fields will probably be FULLTEXT searching in BOOLEAN mode.

I don't understand your comment about
"must build up a stop-list for that language"
yet, but I guess that will become clear while I read everything more in depth and start coding.

thanks a lot!

[phillw]

fulltext builds an index accross the columns which you wish to search. As we want it to be faster than doing a full mulit-column search, there are a few rules ...
1) Words of less than 4 characters are not searched for
2) Common words are not indexed. - This is the 'stop list', otherwise your index would be full of common words. The english stop-list is here MySQL :: The Full-Text Stuff That We Didn't Put In The Manual You will need a french version for your site.

Phill.

une liste de mots d'arrêt français est disponible d'ici
(Okay, so I ran that sentence through a translator - my French isn't that good)

French stopwords

[phillw]

You'll also want to set-up your fulltext index. It is considered best to put the information onto the database 1st, then create the index - else it has to rebuild the index every-time you add something !!
I have a couple of routines to do this - Don't worry, I'm still learning & all the pitfalls that can be fallen into - I have already done !!! - lol

[djemmers]

tnx for the extra info!

I am beginning to think I can do this!
my database will be filled through user input from my visitors.
so the fulltext index will have to be recompiled once a day or so I guess.

djemmers

[phillw]

From what i do understand about full-text, once the index has been set-up - it will auto add records itself (I'll double check) - It is only when you do a major change to your indexing system (adding / removing search columns, adding / removing stop words, changing the ftwordmin setting (that is the one set to 4 by default etc) that it is recommended you do a fulltext index rebuild (i.e drop & create), as opposed to the 'repair' function.

Phil.

[djemmers]

ah tnx.
my database will be subject to change.
text will be changed occasionally
records added
records deleted

but no extra collums or such

[phillw]

Okies. As I've been gleaning information from various forums, I've popped all that I learned on fulltext onto MySQL Full-Text Searches - VPOLink

The code I use is on there, fully commented - Hope it is of help

Phill.

[djemmers]

that I'll read when I am that far!

tnx