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

问题描述

我正在为我的移动应用程序创建 API.我正在使用 PHP MYSQL 和 Slim 框架(这与此问题在很大程度上无关)进行开发.

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 数据库中提取多个场地"，并为每个场地"检索多个场地图像".数据库:

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.

我当前的代码是:

$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";
    }

我当前的代码有效，但它会产生这个:

{
  "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"
    }
]
}

地堡"有两张图片.它不是将图像存储在场地数组中，而是使用第二个图像创建The Bunker"的重复行.就像我之前说的，我需要在每个场地内迭代多个图像.任何帮助将非常感激！谢谢！

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屋！