Multiple selects

Results 1 to 2 of 2

Thread: Multiple selects

  1. #1
    Join Date
    Dec 1969

    Default Multiple selects

    I have a form with a list box for say "jobs". User can apply for multiple jobs. The values are stored in database with "&#124" as delimiter. so If a user selected, joker(1), developer(4), values will be stored as 1&#124 4. Now in next page, another select box is displayed with same values. User select multiple values and I want to match these values with one stored in database. for eg: in second list box, I have joker(1), monster(4) etc. I want to match 1 with 1 stored in database. <BR>How do I go about doing it without writing lot of where clauses?? Plus delimiter will cause the problem. Should I split the value stored in database and then compare??<BR><BR>Any suggestion will be appreciated

  2. #2
    Join Date
    Dec 1969

    Default Wrong DB design...

    ...storing the list of anything with delimiters is a no-no in DB design. You should be using another table and doing a one-to-many link to that table. This is one of the basic points of DB "normalization".<BR><BR>You further complicate things by using 4 for "developer" in one list and 4 for "monster" in another, right? So you don&#039t really want to compare numbers, at all, you want to compare the strings the numbers represent, right? Again, better DB design would make this easier.<BR><BR>Table: USER<BR>&nbsp; &nbsp; UserID -- autonumber, primary key<BR>&nbsp; &nbsp; userName -- text<BR>&nbsp; &nbsp; ... etc. ...<BR><BR>Table: JOBS<BR>&nbsp; &nbsp; JobID -- autonumber, primary key<BR>&nbsp; &nbsp; JobName -- text<BR>&nbsp; &nbsp; ... etc. ...<BR><BR>Table: UserJobs<BR>&nbsp; &nbsp; UserID -- integer, foreign key to USERS table<BR>&nbsp; &nbsp; JobID -- integer, foreign key to JOBS table<BR><BR>Table: ROLES &nbsp; &nbsp; [I am guessing this is where "monster" comes from?]<BR>&nbsp; &nbsp; RoleID -- autonumber, primary key<BR>&nbsp; &nbsp; RoleName -- text<BR><BR>If you do all that, then finding where a RoleName is the same as a JobName, as selected by a given user, is actually pretty easy. Heck, Access could practically build the query automatically for you.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts