php - mysql one-to-many result classification
ringa_lee
ringa_lee 2017-06-29 10:08:19
0
1
670

I found a lot of data from the database, one by one, a two-dimensional array, the format is as follows:

Array ( [0] => Array ( [id] => 29 [user_id] => 1 [car_no] => 234567 ) [1] => Array ( [id] => 21 [user_id] => 1 [car_no] => 23565 ) [2] => Array ( [id] => 23 [user_id] => 1 [car_no] => 12345 ) [3] => Array ( [id] => 28 [user_id] => 1 [car_no] => 124455 ) [4] => Array ( [id] => 6 [user_id] => 1 [car_no] => 12345 ) [5] => Array ( [id] => 7 [user_id] => 2 [car_no] => 22 ) [6] => Array ( [id] => 3 [user_id] => 2 [car_no] => 粤T863 ) [7] => Array ( [id] => 25 [user_id] => 3 [car_no] => 12345 ) );

What should I do to classify users with the same user_id? The format is as follows:

Array ( [0] => Array ( [user_id] => 8 [login] => 18620982882 [car_no] => Array ( [0] => 粤A123 ) ) [1] => Array ( [user_id] => 7 [login] => [car_no] => Array ( [0] => 粤T456 [1] => 123456 ) ) [2] => Array ( [user_id] => 2 [login] => 13318029123 [car_no] => Array ( [0] => 粤T863 [1] => 22 ) ) [3] => Array ( [user_id] => 1 [login] => 15088138542 [car_no] => Array ( [0] => 12345 [1] => 23565 [2] => 12345 [3] => 124455 [4] => 234567 ) ) )

My approach is as follows, which achieves the goal, but it feels too inefficient:

//先把id去重组成一个数组,计算需要处理的$data的子数组个数,然后遍历,如果user_id与去重后的那个数组的键值相同,就把这个值存到新数组里面去,组成一个新的二维数组。 private function group_sort($unique, $num, $data) { foreach ($unique as $key => $value) { for ($i = 0; $i < $num; $i++) { if ($data[$i]['user_id'] == $value) { $data_format[$key]['user_id'] = $data[$i]['user_id']; $data_format[$key]['login'] = $data[$i]['login']; $data_format[$key]['car_no'][] = $data[$i]['car_no']; } } } return $data_format; }

Is there any way to check it directly from the database? In addition, the login here needs to be queried from the user table. In the vehicle table, only user_id is associated with the user table. It needs to be paginated after making it. I plan to use array_chunk to split the array. Please give me some advice from the experts.

The complete code is as follows:

/** * 获取完整或者查询用户信息接口 * @desc 后台查询用户信息,可以根据登录名,手机号,邮箱号,车牌号等查询,不传查询参数表示获取全部用户信息 * @param search string N N 需要搜索的条件,可以是登录名,手机号,邮箱号,车牌号,例如:123,不传参数表示获取全部 * @return int code 操作码,200表示成功,其他表示没有查到数据 * @return string data.login 登录名,是二维数组下标 * @return string data.car_no 车牌号,是二维数组下标 */ public function searchUserInfo() { $search = $this->input->post('search'); //如果没有传查询参数,就查询全部,并分页 if (empty($search)) { $pageSize = (int) $this->input->post('page_size') > 0 ? (int) $this->input->post('page_size') : 100; $pageNo = (int) $this->input->post('pageNo') > 0 ? (int) $this->input->post('pageNo') : 1; $field = 'id, user_id, car_no'; $where = array( 'id>' => 0, ); $result = $this->CarManageModel->my_listPager($this->CarManageModel->table, $where, $field, $pageNo, $pageSize, '', 'user_id ASC', '', 'user_id'); // $query=$this->db->last_query(); // $this->outPutJson(200, array($query)); echo "
"; print_r($result); echo "
"; exit; $this->outPutJson(200, $result); } // 如果传参了,就根据电话号码或者车牌号码查询 //根据电话号码查询 $this->db->select('id'); $this->db->like('mobile', $search); $result_1 = $this->db->get('user')->result_array(); $result_1 = array_column($result_1, 'id'); //根据车牌号码查询 $this->db->select('user_id'); $this->db->like('car_no', $search); $result_2 = $this->db->get('car_info')->result_array(); $result_2 = array_column($result_2, 'user_id'); $result = array_merge($result_1, $result_2); if (!empty($result)) { $this->db->select('user_id,login,car_no'); $this->db->from('car_info'); $this->db->join('user', 'car_info.user_id = user.id', 'left'); $this->db->where_in('car_info.user_id', $result); $data = $this->db->get()->result_array(); $num = count($data); $data_user_id = array_unique(array_column($data, 'user_id')); $data_format = $this->group_sort($data_user_id, $num, $data); echo "
"; print_r($data_format); echo "
"; exit; if ($data_format) { $this->outPutJson(200, $data_format); } $this->outputJson(500); } } private function group_sort($unique, $num, $data) { foreach ($unique as $key => $value) { for ($i = 0; $i < $num; $i++) { if ($data[$i]['user_id'] == $value) { $data_format[$key]['user_id'] = $data[$i]['user_id']; $data_format[$key]['login'] = $data[$i]['login']; $data_format[$key]['car_no'][] = $data[$i]['car_no']; } } } return $data_format; }

The database table structure is as follows:
Vehicle information table

CREATE TABLE `car_info` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `user_id` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT '后台用户id', `car_type_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '车型id,从第三方库获取', `car_no_type` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '车牌号码类型,粤A粤B等', `car_no` varchar(10) NOT NULL DEFAULT '' COMMENT '车牌号码', `frame_no` varchar(8) NOT NULL DEFAULT '' COMMENT '车架号码后6位', `engine_no` varchar(8) NOT NULL DEFAULT '' COMMENT '发动机号后6位', `remark` varchar(200) NOT NULL DEFAULT '' COMMENT '车辆备注', `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否启用', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '软删除', PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`), KEY `idx_status` (`status`), KEY `idx_is_deleted` (`is_deleted`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 COMMENT='用户车辆信息,一个用户可添加多个';

user table:

CREATE TABLE `user` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `login` varchar(50) NOT NULL DEFAULT '' COMMENT '用于登录,用户名/手机号/邮件', `password` varchar(50) NOT NULL DEFAULT '' COMMENT '登录密码', `nickname` varchar(50) NOT NULL DEFAULT '' COMMENT '昵称或者显示的用户名', `email` varchar(50) NOT NULL DEFAULT '' COMMENT '邮件地址', `mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机号码', `reg_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '注册类型,1为手机,2为微信,3为email', `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否启用', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '软删除', PRIMARY KEY (`id`), KEY `idx_login` (`login`), KEY `idx_mobile` (`mobile`), KEY `idx_status` (`status`), KEY `idx_is_deleted` (`is_deleted`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='用户表';
ringa_lee
ringa_lee

ringa_lee

reply all (1)
学霸

SELECT user_id,group_concat(car_no) FROM12311111group by user_id

It seems more troublesome to operate based on the basics

    Latest Downloads
    More>
    Web Effects
    Website Source Code
    Website Materials
    Front End Template
    About us Disclaimer Sitemap
    php.cn:Public welfare online PHP training,Help PHP learners grow quickly!