MYSQL PHP API - 显示与安培;从另一个表中的行内读取多行 [英] MYSQL PHP API - Displaying & Fetching multiple rows within rows from another table

问题描述

我创建了我的移动应用程序的API。我开发这个用PHP MYSQL和修身框架（这基本上是无能为力的这个问题）。

I'm creating an API for my mobile application. I'm developing this with PHP MYSQL and the Slim framework (which is largely irrelevant for this problem).

我想从我的MySQL数据库中提取多个场所，并获取多个venue_images每个场地。数据库：

I'm trying to pull multiple "venues" from my mysql database, and retrieve multiple "venue_images" for each "venue". The database:

venues        venue_images
------        ------------
id PK         image_venue_id FK (to venues)
venue_name    image_path
active

，然后我需要输出这种格式的数据：

{
  "completed_in":0.01068,
  "returned":10,
  "results":[
    {
      "venue_id":"1",
      "venue_name":"NameHere",
      "images": [
         {
           "image_path":"http://www.pathhere.com"
         },
         {
           "image_path":"http://www.pathhere2.com"
         }
      ]
    }
  ]
}

所以基本上，图像的每个场地重复多次。

So basically, the images are iterated multiple times for each venue.

我目前的code是：

$sql = "
    SELECT
         venues.id, venues.venue_name, venues.active,
         venue_images.image_venue_id, venue_images.image_path
    FROM
         venues
    LEFT JOIN 
         venue_images ON venue_images.image_venue_id = venues.id
    WHERE
         venues.active = 1
    LIMIT 0, 10
    ";

    $data = ORM::for_table('venues')->raw_query($sql, array())->find_many();
         if($data) {
        foreach ($data as $post) {
            $results[] = array (
                 'venue_id' => $post->id,
                 'venue_name' => $post->venue_name,
                 'images' => $post->image_path
            );
        }

        //Build full json
        $time = round((microTimer() - START_TIME), 5);
        $result = array(
             'completed_in' => $time,
             'returned' => count($results),
             'results' => $results
        );
        //Print JSON
        echo indent(stripslashes(json_encode($result)));
    } else {
         echo "Nothing found";
    }

我目前的code ++工程，但它会产生这样的：

{
  "completed_in":0.01068,
  "returned":10,
  "results":[
    {
      "venue_id":"1",
      "venue_name":"The Bunker",
      "images":"https://s3.amazonaws.com/barholla/venues/1352383950-qPXNShGR6ikoafj_n.jpg"
    },
    {
      "venue_id":"1",
      "venue_name":"The Bunker",
      "images":"https://s3.amazonaws.com/barholla/venues/1352384236-RUfkGAWsCfAVdPm_n.jpg"
    }
]
}

有对地堡两个图像。代替会场阵列内存储的图像，它的创建的的仓重复行，与所述第二图像。就像我前面说的，我需要有多个图像每个场馆内的迭代。任何帮助将是非常美联社preciated！谢谢！

There's two images for "The Bunker". Instead of storing the images within the venue array, it's creating a duplicate row of "The Bunker", with the second image. Like I said earlier, I need to have multiple images iterating within each venue. Any help would be much appreciated! Thanks!

推荐答案

您想使用GROUP_CONCAT

You want to use GROUP_CONCAT

像这样的东西（不是100％准确的可能:)）

Something like this (not 100% accurate probably :) )

$sql = "
    SELECT
         v.id, v.venue_name, v.active,
         GROUP_CONCAT(i.image_path) as venue_image_string
    FROM
         venues v
    LEFT JOIN 
         venue_images i ON i.image_venue_id = v.id
    WHERE
         v.active = 1
    GROUP BY i.image_venue_id
    LIMIT 0, 10
";

您可能不得不摆弄一点，但应该把你在正确的轨道上（注：提供venue_image_string为CSV）

You may have to fiddle a little but should put you on the right track (note: provides venue_image_string as CSV)

这篇关于MYSQL PHP API - 显示与安培;从另一个表中的行内读取多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！