Get Similar rows count in mysql

Question

I am working on a project in which a tutor can save its class timing. He can see his timing according to the days. I used the code

$qry = mysqli_query($con, 'select * from users left join time_slot on users.id=time_slot.u_id where users.id=' . $id);
echo '<table class="table_class" border="2">
                <tr>
                <th class="details">id</th>
                <th class="details">Date</th>
                <th class="details">start time</th>
                <th class="details">End Time</th>
                </tr>';
while ($row = mysqli_fetch_array($qry)) {
    echo '<tr>';
    echo '<td class="details">' . $row['id'] . '</td>';
    echo '<td class="details">' . $row['name'] . '</td>';
    echo '<td class="details">' . $row['day'] . '</td>';
    echo '<td class="details">' . $row['time_from'] . '</td>';
    echo '<td class="details">' . $row['time_to'] . '</td>';
    echo '</tr>';
}
echo '</table>';

But It show the multiple time if a tutor have multiple class in same day. I want to show if he has 2 or more class on similar day(Monday) then all time slot show in a single row. Same this for all days of the week. How can I do it?

Answer 1

You can use GROUP_CONCAT function for this. Assuming your ddl is something like that

create table users(id bigint, name varchar(50));
create table time_slot(id bigint, u_id bigint, day datetime, time_from time, time_to time);

the sql would be as follows:

select u.id,u.name, ts.day, 
group_concat(ts.time_from, ' - ', ts.time_to ORDER BY ts.time_from, ts.time_to)
from users u left outer join time_slot ts on u.id = ts.u_id
group by u.id, u.name, ts.day
order by u.name, ts.day

See fiddle.