Jump to content

Query help.


ToonMariner

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.