php - 如何使用 mysql 和 php 选择多行？

Question

我有两张 table

1. 广告

1. 广告图片

此处表 ads id 和表 adImages adsId 相同。在这里，我必须从表 ads 和 adImages 中选择所有行，并以 json 格式显示。

我的代码看起来:

$query="SELECT a.*, b.images FROM `ads` AS a INNER JOIN `adImages` AS b ON a.id = b.adsId";

$result=mysql_query($query);
$value = mysql_num_rows($result);
if($value>=1)
{   
     while($row = mysql_fetch_array($result)) 
    {
        $details[] = array(

        'id' => $row['id'], 
        'location' => $row['location'],
        'title'=>$row['title'],
        'mobile' => $row['mobile'],
        'description' => $row['description'],
        'category' => $row['category'],
        'images' =>'http://greenwma.com/dev/uploads/'.$row['images'],
        );
    }
    echo  json_encode($details);
}

它的输出 json 看起来像:

[{"id":"10","location":"9.982852,76.300637","title":"Ggg","mobile":"5555555","description":"Google","category":"BusMedia","images":"http:\/\/greenwma.com\/dev\/uploads\/MapCapture2016062319:07:39.jpg{"id":"10","location":"9.982852,76.300637","title":"Ggg","mobile":"5555555","description":"Google","category"BusMedia","images":"http:\/\/greenwma.com\/dev\/uploads\/MapCapture2016-06-23 17:27:05.jpg"},{"id":"11","location":"9.962439,76.305337","title":"Test","mobile":"898566899","description":"Test","category":"Bus Shelters","images":"http:\/\/greenwma.com\/dev\/uploads\/MapCapture2016-06-23 19:15:16.jpg"},{"id":"11","location":"9.962439,76.305337","title":"Test","mobile":"898566899","description":"Test","category":"Bus Shelters","images":"http:\/\/greenwma.com\/dev\/uploads\/MapCapture2016-06-23 19:08:50.jpg"},{"id":"11","location":"9.962439,76.305337","title":"Test","mobile":"898566899","description":"Test","category":"Bus Shelters","images":"http:\/\/greenwma.com\/dev\/uploads\/MapCapture2016-06-23 19:07:39.jpg"}]

这里的问题是 id,location,title,mobile,description,category 对同一 adsId 上的每张图片重复。 p>

我的需要是，我必须以

的格式获取 json

[{"id":"10","location":"9.982852,76.300637","title":"Ggg","mobile":"5555555","description":"Google ","category":"Bus Media","images":"http:\/\/greenwma.com\/dev\/uploads\/MapCapture2016-06-23 19:07:39.jpg","http:\/\/greenwma.com\/dev\/uploads\/MapCapture2016-06-23 17:27:05.jpg"},{"id":"11","location":"9.962439,76.305337","title":"Test","mobile":"898566899","description":"Test","category":"Bus Shelters","images":"http:\/\/greenwma.com\/dev\/uploads\/MapCapture2016-06-23 19:15:16.jpg","http:\/\/greenwma.com\/dev\/uploads\/MapCapture2016-06-23 19:08:50.jpg","http:\/\/greenwma.com\/dev\/uploads\/MapCapture2016-06-23 19:07:39.jpg"}]

也就是说，我必须在单个 json 对象中获取所有用逗号分隔的图像。为此，我应该在上面的代码中进行哪些更改。

Answer 1

您可以使用 CONCAT() & GROUP_CONCAT()使用子查询在一个查询和更少的循环中完成。

SELECT a.*, GROUP_CONCAT(images) images FROM (
SELECT a.*, CONCAT('http://greenwma.com/dev/uploads/', b.images) images
FROM `ads` AS a 
INNER JOIN `adImages` AS b ON a.id = b.adsId 
) tbl
GROUP BY id