How to match two sets of data using MySQL and PHP

Question

I have multiple MySQL tables with numerous rows that need to be returned in a table using PHP. The issue I am having is how to display the information correctly when one of the tables has numerous matching ID's.

Take this as an example. This is a table that would hold a schedule that the user(userID) has booked.

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

While it's saving this data it is also saving data into another table with what the user has selected as their "service" separated into new rows foreach service they selected.

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

The bk_id is related to the bk_schedule id to form their relationship.

Now when I have to pull this information into a table using Laravel 4 I am getting all the results combined into each row if I use to different table variables. If I attempt to use that same table set using JOIN's I get the rows fine but they are looping through each service rather than combining (I guess since it's looping every row found counting it as a new row).

Kind of like this.

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

Here is the code that reflects that.

    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)); 
}

Blade template:

        <table class="main-table">
            <thead class="main-table-head">
                <th>Status/Result</th>
                <th>Date</th>
                <th>Block</th>
                <th>Services</th>
                <th>Action</th>
            </thead>
            <tbody class="main-table-head">
            @foreach($appointment as $appointments)
                <tr>
                    <td>{{{ $appointments->status }}}</td>
                    <td>{{{ $appointments->date }}}</td>
                    <td>{{{ $appointments->block }}}</td>
                    <td>
                        @foreach($service as $services)
                        {{{ $services->service }}}
                        @endforeach
                    </td>
                </tr>
            @endforeach
            </tbody>
        </table>

This is essentially what I want it to look like. (it's an appointment history page if that helps)

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

i tried to be detailed as possible it's been a pain trying to get this to work. I have attempted GROUP_CONCATbut I get the same problem (it's combing all the records for that userID)

My attempt

        $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);

If anyone was curious as to my final solution.

        $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);

Answer 1

You need to group by both userId and booking 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;

See it on sqlfiddle