我正在玩php以获得一个REST api,我以后可以使用它作为Ember应用程序。 到目前为止一切正常,我刚才有一个关于MySQL的问题。 因为我更像是一个前端人,所以我并不熟悉它。 我的数据库中有一个表,其中一些元素是其他元素的父元素。 所以每个元素都有一个父元素,最顶层的元素有父元素0.现在我希望我的数据库返回元素的所有值以及元素为父元素的元素ID。
我想结合起来
SELECT * FROM table WHERE id=exId和
SELECT id FROM table WHERE parent_id=exId我希望将这些孩子作为一个数组返回,所以我可以像{"prop":"val", "children": [1,2,3,4]}那样从中获取JSON。 有没有办法结合这个或者更容易做两个查询并组合数组?
I am playing around with php to get a REST api I can later use for an Ember app. So far everything works fine, I just got a question concerning MySQL. Since I'm more a frontend guy I'm not that familiar with it. I have a table in my database where some elements are parent elements of others. So every element has a parent, the topmost elements have the parent 0. Now i want my database to return all the values of an element and the element ids of which the element is the parent.
I want to combine
SELECT * FROM table WHERE id=exIdand
SELECT id FROM table WHERE parent_id=exIdAnd i want to return the children as an array so taht i can get JSON out of it like {"prop":"val", "children": [1,2,3,4]}. Is there a way to combine this or is it easier to do two queries and combine the arrays?
最满意答案
加入这两个查询。
SELECT t1.prop, GROUP_CONCAT(t2.id) AS children FROM table AS t1 JOIN table AS t2 ON t1.id = t2.parent_id WHERE t1.id = :exIdchildren将是一个以逗号分隔的ID列表,您可以使用PHP中的explode将其转换为数组。
Join the two queries.
SELECT t1.prop, GROUP_CONCAT(t2.id) AS children FROM table AS t1 JOIN table AS t2 ON t1.id = t2.parent_id WHERE t1.id = :exIdchildren will be be a comma-separated list of IDs, which you can turn into an array using explode in PHP.
更多推荐
发布评论