如何使用MySQL和PHP匹配两组数据

我有多个MySQL表,需要使用PHP在表中返回许多行.我遇到的问题是当其中一个表有多个匹配ID时,如何正确显示信息.

以此为例.这是一个表,用于保存用户(userID)已预订的计划.

bk_schedule

id  userID  date    block   tos status
113 46  2013-12-31  3        yes    1
114 44  2013-12-26  1        yes    3
115 45  2013-12-31  1        yes    3
116 44  2013-12-31  2        yes    3
117 44  2013-12-31  1        yes    3

在保存这些数据时,它还将数据保存到另一个表中,用户选择的"服务"分为foreach他们选择的新行服务.

bk_service

id  userID  bk_id   services
212 46       113    7
213 44       114    62
214 45       115    61
215 44       116    14
216 44       117    1
217 44       117    8
218 44       117    22
219 44       117    15

bk_id与bk_schedule id相关,以形成它们的关系.

现在,当我必须使用Laravel 4将此信息提取到表中时,如果我使用不同的表变量,我将所有结果合并到每一行中.如果我尝试使用相同的表集JOIN's我使用得到的行很好但是它们循环遍历每个服务而不是组合(我猜它因为它循环每一行发现将它计为一个新行).

有点像这样.

userID  bk_id   services
44       116    14
44       114    62
44       117    8
44       117    22
44       117    15

这是反映这一点的代码.

    public function showHistory($id) {

   $appointment = DB::table('bk_schedule')
    ->select('bk_schedule.id', 'bk_schedule.date', 'bk_timeslot.block', 'bk_status.status', 'pr_service.service')
    ->where('bk_schedule.userID', $id)      
    ->join('bk_status', 'bk_schedule.status', '=', 'bk_status.id')
    ->join('bk_timeslot', 'bk_schedule.block', '=', 'bk_timeslot.id')
    ->join('bk_service', 'bk_schedule.id', '=','bk_service.bk_id')
    ->join('pr_service', 'pr_service.id', '=', 'bk_service.services')
    ->orderBy('date', 'ASC')            
    ->get();

  // var_dump($appointment); die;

    $today = date('Y-m-d');

    foreach($appointment as $appointments) {

        $date = strtotime($appointments->date);     

        $appointments->date = date('l: F d, Y',$date);              
    }


    $service = DB::table('bk_service')
        ->select('pr_service.service', 'pr_service.price')
        ->join('pr_service', 'pr_service.id', '=', 'bk_service.services')
        ->where('bk_service.userID', $id)
        ->where('bk_service.bk_id', $appointments->id)
        ->get();

   return View::make('appointments.history', array('pageTitle' => 'Apppointment History',
                                'today' => $today, 'service' => $service,
                                'appointment' => $appointment)); 
}

刀片模板:

        
            @foreach($appointment as $appointments)
                
            @endforeach
            

            

                
Status/Result
                
Date
                
Block
                
Services
                
Action
            
            

                    
{{{ $appointments->status }}}
                    
{{{ $appointments->date }}}
                    
{{{ $appointments->block }}}
                    

                        @foreach($service as $services)
                        {{{ $services->service }}}
                        @endforeach
                    
                
        

这基本上就是我想要的样子.(这是一个约会历史页面,如果这有帮助)

userID  bk_id   services
44       117    1, 8, 22, 15
44       116    14
44       114    62

我试图尽可能详细,试图让它发挥作用是一种痛苦.我尝试过,GROUP_CONCAT但我得到了同样的问题(它正在梳理该userID的所有记录)

我的尝试

        $schedule = DB::table('bk_schedule')
            ->select( DB::raw('users_information.street_2, users_information.phone_2, users_information.apartment, bk_schedule.note, bk_schedule.date, bk_schedule.office, bk_status.status, bk_schedule.id, bk_schedule.userID, bk_timeslot.block, users_information.last_name, users_information.street_1, users_information.phone_1, users_information.user_zip_code, group_concat(pr_service.short_name SEPARATOR " | ") as group_service, group_concat(pr_service.service SEPARATOR ", ") as service_detail'))
            ->join('users_information', 'bk_schedule.userID', '=', 'users_information.id')
            ->join('bk_timeslot', 'bk_schedule.block', '=', 'bk_timeslot.id')
            ->join('bk_service', 'bk_schedule.userID', '=', 'bk_service.userID')
            ->join('pr_service', 'bk_service.services', '=', 'pr_service.id')
            ->join('bk_status', 'bk_schedule.status', '=', 'bk_status.id')
            ->orderBy('bk_schedule.date', 'asc')
            ->groupBy('bk_schedule.id')
            ->paginate(15);

如果有人对我的最终解决方案感到好奇.

        $schedule = DB::table('bk_schedule')
            ->select( DB::raw('bk_schedule.office, pr_service.short_name, bk_timeslot.block, bk_schedule.date, bk_status.status, users_information.last_name, users_information.street_1, users_information.phone_1, users_information.user_zip_code, users_information.street_2, users_information.phone_2, users_information.apartment, bk_schedule.userID, bk_service.id, group_concat(pr_service.service)as service_detail, group_concat(pr_service.short_name)as group_service '))
            ->join('bk_service', 'bk_schedule.id', '=', 'bk_service.bk_id')
            ->join('users_information', 'bk_schedule.userID', '=', 'users_information.id')
            ->join('bk_status', 'bk_schedule.status', '=', 'bk_status.id')
            ->join('bk_timeslot', 'bk_schedule.block', '=', 'bk_timeslot.id')
            ->join('pr_service', 'bk_service.services', '=', 'pr_service.id')
            ->groupBy('bk_service.userID', 'bk_service.bk_id')
            ->paginate(15);

Andreas Wede.. 8

您需要按userId和预订ID进行分组.

select sc.userId, sc.id, group_concat(services)
  from bk_schedule sc
  join bk_service se on (sc.id = se.bk_id)
 group by sc.userId, sc.id;

在sqlfiddle上看到它