Jump to content


Photo

Query help.


  • Please log in to reply
2 replies to this topic

#1 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 20 October 2006 - 09:07 AM

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.

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


follow me on twitter @PHPsycho

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 20 October 2006 - 01:09 PM

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

doc_id | docname| docinfo
------------------------------
1        |  xxxxx    |  xxxxxx
2        |  xxxxx    |  xxxxxx

doc_id| image_id
----------------------------
1       | 2
1       | 3
1       | 4
2       | 20
2       | 11

To answer your question.
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'] . "

http://phpsec.org/pr...uide/3.html#3.2
http://www.php.net/m...l_escape_string

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 October 2006 - 04:12 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users