Mysql/php - 查询问题
我一直在尝试找出一种不同的方法来完成本网站上另一个问题的这项任务,但我想也许我让它变得太困难了。
这是我所拥有的: 表与 ImageID、ImageName、GalleryID 另一个包含评论、作者、日期、图像 ID 的表
我想做的是执行一个查询,在其中找到所有具有 galleryID=42 的图像。此外,我想获取与每张图片相关的所有评论(通过 ImageID)并将它们连接到一个值中。例如:
ImageID:1234,ImageName:IMG425,GalleryID:42,评论:酷!|||约翰·史密斯|||2010-09-06~~拍得好!|||理查德·克拉克|||2010-10-01 ~~我记得这次运行。|||Susan Edwards|||2010-10-04
我需要连接注释表中每个图像的所有结果并将它们作为单个值放入,然后我可以解析它们通过我的页面正文中的 PHP 进行。
I have been trying to figure out a different way to complete this task on another question on this website, but think maybe I am making it too difficult.
Here is what I have:
Table with, and ImageID, ImageName, GalleryID
Another Table with Comments, Author, Date, ImageID
What I want to do is do a query where I find all of the Images that have a galleryID=42. In addition, I would like to grab all of the comments that are associated with each picture (via the ImageID) and concatenate them in a single value. For example:
ImageID: 1234, ImageName: IMG425, GalleryID: 42, Comments: Cool!|||John Smith|||2010-09-06~~Nice shot!|||Richard Clark|||2010-10-01~~I remember this run.|||Susan Edwards|||2010-10-04
I need to concatenate all of the results from the Comments table that are for each image and put them in as a single value, then I can parse them via PHP in the body of my page.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
GROUP_CONCAT() 是可行的方法,其他答案也很接近。吻。
请注意,默认情况下 GROUP_CONCAT() 的最大长度非常短。你可能需要跑
GROUP_CONCAT() is the way to go, and the other answers are close. KISS.
Note that GROUP_CONCAT() has a very short max length by default. You may have to run
mysql中有一个名为GROUP_CONCAT 我还没有真正尝试过,但我认为它可以帮助
祝你好运
编辑:
查询可能是类似的东西
,但我不知道 group_concat 是否适用于连接
there's a function in mysql called GROUP_CONCAT i havent really tried it but i think it could help
Good Luck
EDITED:
The query might be something like
or something like that, but i dont know if group_concat works with joins
为什么不直接在单独的查询中提取评论数据呢?
虽然额外的数据库访问并不理想,但它可能会产生与连接和提取数据一样多的开销,并且分离查询将使您的代码不那么混乱。
Why not just pull the comments data in a separate query?
While the extra trip to the database isn't ideal, it's probably going to be just as much overhead as concatenating and extracting that data, and separating the queries will make your code less jumbled.