首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何获取两个表值并在json响应中作出

问如何获取两个表值并在json响应中作出
EN

Stack Overflow用户
提问于 2017-08-09 06:34:04
回答 1查看 38关注 0票数 0

这里我想从MySQL中获取计数,我是两个基于trip id的表,我想要进行计数,如何做到这一点?

这里,trip对于表和trip id 1都是常见的,trip id 1有三个计数,trip id 2有两个计数。

trip_details (表名)

代码语言:javascript
复制
 tripId        cabNo 

  1          BMW4455

  2          Audi005

trip_member (表名)

代码语言:javascript
复制
id    tripId    empId

1     1       100
2     1       101
3     1       102
4     2       105
5     2       106

预期结果

代码语言:javascript
复制
{
"status": "success",
"data": [
    {
        "tripId": "1",
        "cabNo": "BMW4455",
        "empcount": "3"
        "employees": [
            {
                "empId": "100",
            },
            {
                "empId": "101",
            },
            {
                "empId": "102",
            }
        ]
    },
    {
        "tripId": "2",
        "cabNo": "Ad2K2001",
        "count": "2"
        "employees": [
            {
                "empId": "100",
            },
            {
                "empId": "101",
            },
            {
                "empId": "102",
            }
        ]
    }
]
}

我试过了,但我没有得到确切的答案,,我的代码在这里,

代码语言:javascript
复制
     $sql = "SELECT * FROM trip_details a INNER JOIN trip_member b ON a.tripId=b.tripId GROUP BY a.tripId";
  $mysql = mysql_query($sql);
  $count =mysql_num_rows($mysql);
  if($count > 0){
  while ($row = mysql_fetch_assoc($mysql)) {
    $data[] = $row;
  }
  $arrayName = array('status' => 'success', 'count' => $count, 'data' =>$data );
  echo json_encode($arrayName);
 }else{
$arrayName = array('status' => 'error', 'data' =>'Data not found' );
  echo json_encode($arrayName);
 }

我得到了输出

代码语言:javascript
复制
{
"status": "success",
"count": 2,
"data": [
    {
        "tripId": "1",
        "cabNo": "BMW4455",
        "driverId": "DRE001",
        "routeId": "1",
        "tripDate": "",
        "startTime": "",
        "endTime": "",
        "shiftId": "1",
        "tripStatus": "0",
        "id": "1",
        "empId": "100",
        "callStartTime": "",
        "callEndTime": "",
        "cabReachingTime": "",
        "pickupTime": "",
        "dropTime": "",
        "dropStatus": "",
        "otp": "",
        "pickupotpStatus": "",
        "empPresentStatus": ""
    },
    {
        "tripId": "2",
        "cabNo": "Audi005",
        "driverId": "DRV101",
        "routeId": "2",
        "tripDate": "",
        "startTime": "",
        "endTime": "",
        "shiftId": "1",
        "tripStatus": "0",
        "id": "4",
        "empId": "105",
        "callStartTime": "",
        "callEndTime": "",
        "cabReachingTime": "",
        "pickupTime": "",
        "dropTime": "",
        "dropStatus": "",
        "otp": "",
        "pickupotpStatus": "",
        "empPresentStatus": ""
    }
]
}
EN

回答 1

Stack Overflow用户

发布于 2017-08-09 06:36:26

由于您只需要计数,所以应该将查询更改为:

代码语言:javascript
复制
SELECT count(*) as count, tm.tripId from trip_details td
JOIN trip_member tm on tm. tripId = td.tripId
GROUP by tm.tripId
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/45583202

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档