August 22, 2002 at 7:32 am
Hello. I'd like to ask your opinions about using a multiple select in a search.
I have form in which I have a few checkboxes named "Language". I have about 8 checkboxes (English,German,etc.).
I'd like to ask first of all the database structure. This search will be used for people who speak those languages. I mean, you have Joe who can speak English and German and Nicole who can speak German and French. How would you make the records in the table? I'm thinking of having a "LanguagesSpoken" column and insert the values using commas.
I'll be very glad if you can share your opinions about both building the table with this multiple choices in mind and the search itself.
Edited by - kensai on 08/22/2002 07:37:06 AM
August 22, 2002 at 7:44 am
Basically I'm guess you would like to have a record based on the checkboxes a person selects. I'd like to suggest you implement a relational design where you have a person table ,a language_spoken table, and a language table. The tables might look something like this:
Create table person (
person id int not null,
person_name char(50) not null,
....
)
create table language_spoken (
person_id int not null,
languageid int not null)
create table language (
languageid int not null,
language_description char(25) not null)
This way any single person could easily have one or more languages they speak, with no limitations, or repeating fields in the main person table. Also this would allow you to add languages should more be needed.
This is only my opinion, I'm sure there are other options to pick from.
Good luck, hope you determine a design that works for you.
-------------------------
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 22, 2002 at 8:32 am
GAL0303 is absolutely right, do it the relational way instead. A search for people who speak some languages is much easier that way:
SELECT * FROM PERSON p
INNER JOIN LANGUAGE_SPOKEN ls
ON p.person_id = ls.person_id
WHERE ls.languageid IN (1,3)
where 1 and 3 are idnumbers of some languages that the user checked in the boxes to search for.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Edited by - chrhedga on 08/22/2002 08:36:25 AM
August 23, 2002 at 11:47 am
GAL0303 excellent idea. Thanks, I'll do just like you said. Also chrhedga thanks for the IN tip.
I'd like to ask your opinion about doing this on an already built database. I've built a bulletin board script just like Snitz Forum. If you look to Snitz's database you can see columns like FORUM_ID,TOPIC_ID,T_SUBJECT,T_MESSAGE etc. in the TOPICS table and columns like TOPIC_ID,REPLY_ID,R_MESSAGE in the REPLY table. I'm using this exact design for my forum. Now, how'd you do a search on such a database where a user selects the forums with checkboxes? For example, I want to search a word on all messages in forums 1,3,10 etc. How'd you do such a thing?
August 24, 2002 at 4:20 am
Thanks for the code Stuart. But the problem I'm having is the "in (1,3,10)" part. The forum numbers (which forums to search - 1,3,10) will be selected by users using checkboxes on the search page. This part is giving me a hard time to implement..
August 24, 2002 at 6:10 pm
Ok so you submit to one variable and if you try to do IN (@myvar) it looks like IN ('1,3,10') now you can do this one of two ways.
Either use dynamic sql
ex.
EXEC ('SELECT [whatever columns you display]
FROM TOPICS
WHERE T_MESSAGE LIKE ''%''' + @searchtext + '''%'' AND FORUM_ID in (' + @myvar +')'
Or you can parse the input variable using CHARINDEX in a while loop to seperate each item in a temp table field then do a subquery IN (SELECT myval FROM #tmpTbl)
Something like this from another example
CREATE PROC ip_SumAct
@activity VARCHAR(20)
AS
SET NOCOUNT ON
CREATE #tmpTbl (
activity VARCHAR(1)
)
WHILE CHARINDEX(',', @activity) --Loop thru the submitted values to make sure we can use them.
BEGIN
INSERT INTO #tmpTbl (activity) VALUES (LEFT(@activity, 1)
SET @activity = RIGHT(@activity, LEN(activity) - CHARINDEX(',', @activity))
END
IF LEN(@activity) > 0 --Make sure got all data.
BEGIN
INSERT INTO #tmpTbl (activity) VALUES (@activity)
END
Then you query with the IN subquery.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 26, 2002 at 8:45 am
My opinion would be to skip the languageids altogether. It's not like you'll have two Englishes or two Germans in the languages table. The space saved by inventing a languageid is negligible, and no speed would be saved.
I'd do it this way:
Table People:
userid...
whatever else...
Table Languages:
language (with unique index)
Table people_languages
userid
language
Then the query is more human-readable:
SELECT p.*
FROM people p INNER JOIN people_languages pl
ON p.userid = pl.userid
WHERE pl.language IN ('English', 'German', 'Spanish')
However, if the languages are known in advance, a UNION would be even faster. The speed saved by UNIONing the individual languages would probably not be worth the code hassle, though.
I'd also use varchars rather than chars. It's true that char columns permit SQL to make a predetermined width judgement when determining query costs, but in the long run, sending the spaces back over the wire and chopping them off in the client app (or doing that via SQL itself) would most likely negate any query-cost savings made by making it a char. Only in the most extreme cases would I use char columns any more, where speed is of the absolute top priority and/or ALL columns in the table are able to be fixed width and are truly fixed-width, not padded variable-width.
- Troy King
August 29, 2002 at 3:21 am
As a related question is there an easy way
to combine selection criteria ie to find those people who speak ALL of German, French and English - rather than nested selects?
ie the long way
select personId from table where personid
in select(personid from table where language = German) And personid in select(personid from table where language = French)...
I seem to remember a operator in SQLAnywhere...
August 29, 2002 at 4:05 am
This should do the trick.
SELECT personid
FROM table
WHERE language IN ('German', 'French', 'English')
GROUP BY personid
HAVING COUNT(personid) = 3
Or if you only have those three languages then
SELECT personid
FROM table
GROUP BY personid
HAVING COUNT(personid) = 3
will work as well.
Edited by - antares686 on 08/29/2002 04:06:16 AM
August 29, 2002 at 4:08 am
Thanks
Chris
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply