multiple column values sperated by a semicolon

[webposer]

Hello,

 

I've just inherited a site that is backed by a mysql db (v5.1.37).

 

The person below me stored a multiple-select values in one column.

 

id

values

1

1;3;4

1

6;8

 

The 'values' column needs to map to an adjoining table to grab that particular values meta information:

 

id

meta

1

Dog

2

Cat

 

etc.

 

I have no idea how to pull one of the values from the semicolon'd list, then query the subsequent table for the meta.  Can anyone point me in the right direction here?

 

I very much appreciate it! I'd like to find the previous developer and explain mapping tables whilst poking him in the eye.

 

- Josh

[cags]

It is a badly designed database. Ideally it would be normalised so one column only holds one piece of information. But since that what you have, you will probably need to make multiple queries.

 

$sql = "SELECT values FROM table WHERE id=1 LIMIT 1";
$result = mysql_query($sql) or trigger_error("SQL: $sql, ERROR: " . mysql_error(), E_USER_ERROR);

$row = mysql_fetch_assoc($result);

$values = explode(';', $row['values']);
$bob = " WHERE id=". $values[0];

for($i = 1; $i < count($values); ++$i) {
   $bob .= " OR id=" . $values[$i];
}

$sql = "SELECT * FROM table2" . $bob;
$result = mysql_query($sql) or trigger_error("SQL: $sql, ERROR: " . mysql_error(), E_USER_ERROR);

That's probably not the best method, it's just the first that came to mind. It also has plenty of flaws that could cause it to crash, but it should at least give you one idea.

Nb. I assume that the 2 id's in your first table should have been different.

[webposer]

Thanks, I'll give that a shot.  This is truely poor design.  And yes, I meant to have different id's in the first table...sorry.