Hi all,

I have been doing database design and web design for a solid 5 years.

Recently, I have had the need to get much deeper into MySQL syntax and various functions.

What I am looking for here is a confirmation that the following idea will work with MySQL.

here's a little background:

I have a website/directory service, where there are 18 categories with each sub category containing varying amounts of subcategories.


Now, in the design I have a table that searchs are performed on, and I have the following columns as part of my "descriptions" table where all my data is stored.

column1 Name: cat_id which contains the numeral from 1 to 18 (descriptor of the general category.

column 2 Name: sub_cat_id which contains a variable numeral which describes the sub category.

column 3 Name: loc_id: which contains the geographical numerical reference.


So say our Main category is Entertainment (which equals 5 ) and it's sub category which equals =11 (which in this case would make it snowboarding).


Now, here's where it gets tough. I designed a ADVANCED search page, where it allows for a WORD SEARCH and then you can also select MULTIPLE sub category selections, and also select a GEOGRAPHICAL LOCATION,( which is covered by an additional column in the exact same table which is called loc_id). This would fullfill a more detailed search then your typical word search.

You following me?


Ok, so, I want to know if the following will work.

So say a user types in a string for searching.... in this case it will be "ski hill"

and then they select a sub category UNDER the main category of Entertainment. (In this case, there are 13 different subcategories under the main category "Entertainment") They then select the LOCATION which I described above....


HOW EXACTLY CAN I DESCRIBE THIS VIA MySQL SYNTAX??? and then pass this advanced search onto the database?



Here is my syntactical idea...:


SELECT descriptions.cat_id,
descriptions.sub_cat_id,
descriptions.loc_id
FROM descriptions
WHERE cat_id ="5" AND sub_cat_id = '13' AND sub_cat_id = '12' AND sub_cat_id = '10' AND LOC_id = 3
// and so on
LIKE '%#search_word#%'



So in my interface (i am using flash MX) I was thinking I would store a string PER EACH SUB CATEGORY like so

UNDER ENTERTAINMENT:
selection box 1 ="AND sub_cat_id = 1"

selection box 2 ="AND sub_cat_id = 2"

and so on all the way up to 13 (which is the max amount of sub categories per the category of "Entertainment").


Then I would build a large string and pass it to the the query.


So basically, in this case, my user named JOE selects 5 out of the available 13 subcategories by checking off the list he's viewing. Then he would select the LOCATION (which is like the following: loc_id = 4)

Am I way off here, Or is this allowable under Current MySQL versions? Can this be done?


Any other ideas are welcome. If it matters much in this case, I am using Cold Fusion 5.0 as the web language.


Thanx in advance...

Hello,

What you want to do seems all the more possible to me.

First of all, let's put your "LIKE" apart. Your query should be like this(with ORs for subcategories instead of ANDs) :

SELECT descriptions.cat_id,
descriptions.sub_cat_id,
descriptions.loc_id
FROM descriptions
WHERE cat_id ="5" AND (sub_cat_id = '13' OR sub_cat_id = '12' OR sub_cat_id = '10') AND LOC_id = 3;

Which you should put this way :

SELECT descriptions.cat_id,
descriptions.sub_cat_id,
descriptions.loc_id
FROM descriptions
WHERE cat_id ="5" AND sub_cat_id IN ('13', '12', '10') AND LOC_id = 3;

Now for your "text search", you can use "AND field LIKE '%...'" or something like this. However, the LIKE operator may be slow, especially if thr text you are looking in is large. Think about it, but you may want to use MySQL "Full-Text Search" abilities : MySQL FTS . To do so, you have to create FTS indexes. Please refer to MySQL manual (I just gave you the link).

I think it really depends on the type of text you're searching in.

Concerning the first part of the query (without LIKE or so), I think an index on (cat_id, sub_cat_id, loc_id) would be useful.

Regards,

RBARAER

Remove the quotes from this statement:
sub_cat_id IN ('13', '12', '10')
as you are converting numbers into strings without need which will slow your query down.

Hello guelphdad,

Well, there were quotes in the original query, so I assumed sub_cat_id was of string datatype. If sub_cat_id is indeed of number datatype, then, as you stated, it would be better to remove them.

Regards,

RBARAER

Hey thanx for the replies guys, My email notification didn't work, so I am reposting to this late - a month later.... but oh well.

Thanx for that interesting confirmation guys!

It gave me alot of food for thought.

Thanx again!

Cheers!

Madcoding