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?

I have bounty it by mistake. Any one know how can I remove the bounty — Yogendra
– Yogendra, Commented May 27, 2019 at 8:21

rasso · Accepted Answer · 2015-12-31 09:57:20Z

5

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.

edited Dec 31, 2015 at 9:57

answered Dec 31, 2015 at 8:41

rasso

2,2213 gold badges23 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yogendra Over a year ago

Hi @ozgur-bar, I don't know about the bounty. By mistake I have added bounty to this question. If you have any knowledge to remove it. Please help me

Sebastian Brosch Over a year ago

@Yogendra - This is not possible: meta.stackexchange.com/questions/21529/…

rasso Over a year ago

Hi @Yogendra I've never asked a question on stackoverflow, so I have no idea how it's done..

Divesh Oswal · Accepted Answer · 2015-12-31 08:57:24Z

I have did with some temp values. if you want in same way to impliment then it is usefull for you.

copy the code and check here http://phpfiddle.org/

$obj1['id']='1';
$obj1['name']='a1';
$obj1['day']='asdadh';
$obj1['time_from']='1';
$obj1['time_to']='1';

$obj2['id']='2';
$obj2['name']='a2';
$obj2['day']='asdad';
$obj2['time_from']='1';
$obj2['time_to']='1';

$obj3['id']='3';
$obj3['name']='a2';
$obj3['day']='asdad';
$obj3['time_from']='1';
$obj3['time_to']='1';


$arr = Array();
$arr[]=$obj1;
$arr[]=$obj2;
$arr[]=$obj3;


echo '<table class="table_class" border="2">';
echo '<tr>';
echo '<th class="details">id</th>';
echo '<th class="details">name</th>';
echo '<th class="details">day</th>';
echo '<th class="details">start time</th>';
echo '<th class="details">End Time</th>';
echo '</tr>';

foreach($arr as $row)
{
    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>'; 

echo "<br><br><br><br><br><br><br>";

$dates=Array();
$count=0;
foreach($arr as $id=>$row){
    $val = $row['day'];
    $key = array_search($val,$dates);
    if(is_numeric($key)){
        $arr[$key]['day']=$dates[$key].','.$val;
        unset($arr[$id]);
    }else{
        $dates[$count]=$val;
    }
    $count++;
}

// new table 

echo '<table class="table_class" border="2">';
echo '<tr>';
echo '<th class="details">id</th>';
echo '<th class="details">name</th>';
echo '<th class="details">day</th>';
echo '<th class="details">start time</th>';
echo '<th class="details">End Time</th>';
echo '</tr>';
foreach($arr as $row)
{
    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>';

Collectives™ on Stack Overflow

Get Similar rows count in mysql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related