Jump to content

Archived

This topic is now archived and is closed to further replies.

ToonMariner

Query help.

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.