时间戳

首页 > 解决方案 > 我有两个表第一个表有一些数据另一个表第二个有所有数据现在加入查询空值显示在 json 响应中

php - 我有两个表第一个表有一些数据另一个表第二个有所有数据现在加入查询空值显示在 json 响应中

问题描述

我有两个表第一个表有一些数据另一个表第二个有所有数据现在离开连接查询放在 json 响应空值中显示如何替换空值我正在使用邮递员，我在 json 字段中更新鲜。

如果存在数据，则数据会像这些文件一样显示响应

"athlete_attendance_id":"48","coach_id":"302","athlete_id":"380","athlete_attendance":"1"

{"responseCode":200,"responseMessage":"Athlete details Successfully display","data":[{"user_id":"380","athlete_attendance_id":"48","coach_id":"302","athlete_id":"380","athlete_attendance":"1"}]}

如果表 1 中不存在数据，则在邮递员的响应中显示空值替换为“0”值

"athlete_attendance_id":"null","coach_id":"null","athlete_id":"null","athlete_attendance":"null"

{
    "responseCode": 200,
    "responseMessage": "Athlete details Successfully display",
    "data": [
        {
            "user_id": "377",
            "athlete_attendance_id": null,
            "coach_id": null,
            "athlete_id": null,
            "athlete_attendance": null
        }
]
}

我想要这样的回应价值

{
        "responseCode": 200,
        "responseMessage": "Athlete details Successfully display",
        "data": [
            {
                "user_id": "377",
                "athlete_attendance_id": 0,
                "coach_id": 0,
                "athlete_id": 0,
                "athlete_attendance": 0
            }
    ]
    }

这是模型

            public function showAthleteData($team_id2,$coach_id2){
 $this->db->select('user.*,team.team_id,teams_athlete.team_id,dev_athlete_attendance.*');
          $table = array('user');
          $this->db->from($table);
                 $this->db->join('teams_athlete', 'user.user_id=teams_athlete.user_id');
                $this->db->join('dev_athlete_attendance' ,'dev_athlete_attendance.athlete_id = dev_teams_athlete.user_id','left' );
              $this->db->join('team','team.team_id = teams_athlete.team_id');
                $this->db->where('team.user_id',$coach_id2);

                $result = $this->db->get();
               if($result->num_rows() > 0 ){
                      return $result->result_array();
                                 }else{
                                     return 0;
                                }
 }

控制器

$team_id2= $this->input->post('team_id');
         $coach_id2= $this->input->post('coach_id'); //coach_id

         $userCount['result'] = $userCount1 = $this->Querydata->showAthleteData($team_id2,$coach_id2);
                    if($userCount['result']>0){


                         $data_arr1 = array(
                        "responseCode" =>  $this->res = 200,
                         "responseMessage" =>  $this->login = 'Athlete details Successfully display',
                        "data" =>$userCount['result']);
                       echo json_encode($data_arr1);

标签： phpjsonapicodeigniter

解决方案

使用 IFNULL 检查您的列是否为空，然后将默认值设置为 0

前任：

   select IFNULL(coach_id, '0') AS coach_id,     
    IFNULL(athlete_id, '0') AS athlete_id,     
    IFNULL(athlete_attendance, '0') AS athlete_attendance ....

编辑：

SELECT dev_user.*, IFNULL(team.team_id, 0) as team_id1,
 IFNULL(teams_athlete.team_id, 0) as team_id2,
IFNULL(dev_athlete_attendance.coach_id,0) as coach_id,
IFNULL(dev_athlete_attendance.athlete_id,0) as athlete_id,
IFNULL(dev_athlete_attendance.athlete_attendance,0) as athlete_attendance 
FROM dev_user JOIN dev_teams_athlete ON dev_user.user_id=dev_teams_athlete.user_id 
LEFT JOIN dev_athlete_attendance ON dev_athlete_attendance.athlete_id = dev_teams_athlete.user_id 
JOIN dev_team ON dev_team.team_id = dev_teams_athlete.team_id 
WHERE dev_team.user_id = '301'

推荐阅读