Joining MySQL tables and comparing columns to list data

Question

I have been struggling with the proper way to extract the data I need. I am using MySQL with PHP and will be putting the data into a list format. My only problem is with the actual query itself. Here is how the DB is setup:

I have a 'chars' table for characteristics with the columns 'id', 'descrip', and 'class'.

I have a 'animal' table with the columns 'animal_id', 'charlist', ...

In 'chars' id is an incremented int, 'descrip' and 'class' are text/strings.

'id' and 'descrip' are different for every row but 'class' will be the same sometimes having a value like 'habitat', 'size', 'diet', et cetra.

What I am going for is a list that will look like this:

Habitat
• underground
• arctic

Diet
• fish
• insects
• omnivore

Here is where I start to have trouble. On a 'Details' page I am showing all of the data specific to a certain animal (whichever is clicked) and to reference the 'characteristics' each animal has it's own 'charlist' value in the database. This value is a string of numbers that reference the 'chars' table, like '2,55,67,90,122'.

So I've pulled all my (specific) animal data to a php variable on the page that I want to supply with the information. Now I need to use the 'charlist' data from that animal to lookup and list the characteristics relative to the animal.

My queries are looking like this, and I know I'm waaay off:

SELECT * FROM 'chars' LEFT INNER JOIN 'animals' ON chars.id IN (animals.charlist) WHERE ...

I've tried a lot of different ways and this is where I get lost. My brain is telling me to join the tables, find the numbers that are in the charlist WHERE animal.animal_id = mysql_real_escape_string($animal_id) - and of course MySQL tells me I can't do it this way.

I know that for the titles of the lists I'll probably have to do this with a GROUP BY condition.

Any help on the query and query syntax would be great. I'm fairly new to MySQL and I'm very happy and eager to learn how to do this right.

Thanks for reading.

Answer 1

a IN (x) returns true if (and only if) a equals x.

If x is a string (even one delimited by commas), then the statement will only be true if a is equal to that same string. That is, 123 IN ('123,456,789') is false. Note that this is not the same as passing multiple arguments such as 123 IN (123, 456, 789).

In MySQL, one could instead use FIND_IN_SET(), which expects a string delimited by commas. However, storing delimited lists in database columns is a really bad idea. You should instead define a third, relationship, table in which you store foreign keys into both of your existing tables: (animal_id, characteristic_id):

CREATE TABLE animal_characteristics (
  PRIMARY KEY (animal_id, characteristic_id),
  FOREIGN KEY (animal_id) REFERENCES animal (animal_id),
  FOREIGN KEY (characteristic_id) REFERENCES chars (id)
) SELECT animal.animal_id, chars.id AS characteristic_id
  FROM   animal JOIN chars ON FIND_IN_SET(chars.id, animal.charlist)
;

Then you can do:

SELECT *
FROM   animals
  JOIN animal_characteristics USING (animal_id)
  JOIN chars ON chars.id = animal_characteristics.characteristic_id
WHERE  ...