Query help.

[ToonMariner]

Good morning...

I have a little problem.

I have a field in a table that holds comma separated values of the primary key values of another table that relate to this record. I want to use a join query to pull all the records from the second database where the id is in the comma separarted string contained in the first table.

Now I have tried this and imagine (if its possible to do this in one query that is) that the right answer will be something similar...

the field containing the comma separated values i want is called `images` and is in the `tbldoc` table.

[code]
SELECT
`im`.*
FROM
`tblimages` AS `im`,
`tbldocs` AS `doc`
WHERE
`doc`.`docid` = " . $_GET['d_id'] . "
AND
`im`.`image_id` IN (`doc`.`images`)

[/code]

[shoz]

You should have 2 tables similar to the following instead of having a field with comma separated values.

[code]
doc_id | docname| docinfo
------------------------------
1        |  xxxxx    |  xxxxxx
2        |  xxxxx    |  xxxxxx

[/code]
[code]
doc_id| image_id
----------------------------
1      | 2
1      | 3
1      | 4
2      | 20
2      | 11
[/code]

To answer your question.
[code]
SELECT
im`.*
FROM
`tblimages` AS `im`
INNER JOIN
`tbldocs` AS `doc`
ON
FIND_IN_SET(`im`.`image_id`, REPLACE(`doc`.`images`, ' ', ''))
WHERE
`doc`.`docid` = " . $_GET['d_id'] . "
[/code]

http://phpsec.org/projects/guide/3.html#3.2
http://www.php.net/mysql_real_escape_string

[fenway]

Yeah, comma-separated anythings are bad in a database, since it kind of defeats the entire point of having atomic representations.  I always wondered how people go about editing such fields.. searching is easy enough (see above), but it's a bad starting point.