Multiple variable search in row

[cdherold]

Does anyone know how to search MySQL database tables with rows that have multiple variables ... in database X say row "letters" contains "a,c,d,e" for "id=1" and "d,c,g,h" for "id=2" and I want to select for id's that have d and h.
<P>how do i do that?
<p>Something like this doesn't work ...
Select from X where letters=d,h;
<p>Even something like this doesn't work for selecting out a single element ...
Select from X where letters=d; <p>

Any ideas.

[toma42]

where letters in ('d', 'h');

Sounds like you've got a really bad database design too...you should avoid using comma delimited lists in most cases and especially when those lists are not easily readable...that is, a list of permissions ala phplib like
NORMAL,ADMIN,MODERATOR
is easily readable while yours like
a,b,c
have no meaning to anyone looking at the for the first time.

[toma42]

say row "letters" contains "a,c,d,e" for "id=1"

er, so is their a field called letters with value 'a,c,d,e'? I can't be sure even after rereading your question.

If so, you'll need to use a like
letters like '%a%' or letters like '%c%'

either way, not good.

[cdherold]

ok maybe you can help me here ... my lists are actually looking something like this ...a sample list from one of the columns (skills)... NMR, Mass Spectrometry, Molecular Biology.

The way i'm putting these into the database is by using a form with a multiple selects dialog box and then using php to make it into a readable list delimited by comma's.

if ($skills)
{
$skills = implode($skills, ", ");
}

So what I'm trying to do it set up a method to search the skills column (and a bunch of other columns at the same time) so that users can specify which skills (and whatever else) are necessary and then they can see the correlate resumes for people who have checked off those skiIls (and have the other correlate qualifications).

So if someone checked off NMR and Molecular Biology as required skills. I want to go through the skills of all the entrants and find those that have both. Then the resume of those individuals would be displayed.

I make no bones about the fact that i really have no background with this at all and have been putting this whole thing together by the seat of my pants. I've done a lot with writing to and reading from databases to power a website, but this is an entirely new realm for me and I'm kinda lost.

If you see a different direction i should be going in regarding setting up this database for ease of (what i consider complicated) searching, please let me know. I'm trying to learn this from scratch and the MySQL documentation and o'reilly aren't helping me much with this part.

chris

[toma42]

You're facing a database design issue here. You need to resturcture your db by dropping the skills column and creating two new tables so you can have a many-to-many relationship with users(my guess at your table name) and skills.

There are a lot of posts here concerning the structure of a many-to-many relationship so I don't want to get into it...just do a search. I suspect you just needed to know what you needed was called.

[cdherold]

Gotcha. I'll check it out.

thanks for the direction.

chris