Anonymouse Anonymouse - 22 days ago 10
MySQL Question

how to group id and inline the dates using php

This is my sample code, i want to group id and inline the dates as shown on the image below

$sql_e = mysqli_query($link,"SELECT * FROM tbl_attendees");
while($sql_e_res = mysqli_fetch_array($sql_e)){
echo'<tr>
<td>'.$sql_e_res['s_usn'].'</td>
<td>'.$sql_e_res['s_name'].'</td>
';
$dt = ''.$sql_e_res['at_date'].'';
$dt = strtotime(str_replace(',', '', $dt));
$d = date('j',$dt);

$currentdays = intval(date("t"));
$i = 0;
while ($i++ < $currentdays){
if($i == $d){
$ff='<td style="text-align:center">'.$d.'</td>';
}else{
$ff='<td style="text-align:center">';
}
echo $ff;
}
echo'</tr>';
}


Original

What i want

Answer

As xQbert said , you need to sort your query by s_usn

EDIT: the query that my code expects to work on is "SELECT * FROM tbl_attendees ORDER BY s_usn asc;"


I guess this code has a chance to be more optimized but I tried to keep my coding style very close to yours and reusing your var names. I also tried to make the code readability a priority. try this code, and I almost commented everything

the code is a about a big loop throw the result rows, and for every row you are going to check if this is a new student or not. if it is a new student , then you are going to build the attendees days of the previous student in a string named $daysTDs looks something like this "<td>1<td><td><td><td>3<td>...."

we are going to build this string from an array called $attendees that holds all the days this student has attended in it, it may be look something like this

  $attendees = [12,10]

when we encounter a new student we are going to echo the $daysTDs of the previous student and close the row by '' after the loop is finished we are going also to echo the last student $daysTDs string and close it's row by </tr>

$lastId = "";  //var to check the new students
$daysTDs = ""; // a string holding the TDs of the student e.g '<td>1<td><td><td><td>3<td>....'
$attendees = []; //array to hold the days 
$currentdays = intval(date("t"));

//start query result loop
 while($sql_e_res = mysqli_fetch_array($sql_e)){
    if($sql_e_res['s_usn']!=$lastId){//if new student
        $i = 0;
        //new student ? then build the $daysTDs string from $attendees attay for the previous student
        while ($i++ < $currentdays){ //fot the first student it will be empty and will not get echoed
            if(in_array($i, $attendees)){$daysTDs .= "<td>$i</td>";}
            else{$daysTDs .= "<td></td>";}
        }
        if($lastId!=""){
            echo $daysTDs;//if not first student append the $daysTDs
            echo'</tr>'; //if not first student, then close the row </tr>       
        }
        $attendees = []; // flush the array for the next student
        $daysTDs=""; // empty the TDs string for the next student
        echo'<tr>
        <td>'.$sql_e_res['s_usn'].'</td>
        <td>'.$sql_e_res['s_name'].'</td>';
    }
    $lastId=$sql_e_res['s_usn'];

    $dt = ''.$sql_e_res['at_date'].'';
    $dt = strtotime(str_replace(',', '', $dt));
    $d = date('j',$dt);
    $attendees[]=$d; //push that day to this student attendees array                                               
}
//after the loop is ended finish the last student row
$i=0;
while ($i++ < $currentdays){
    if(in_array($i, $attendees)){$daysTDs .= "<td>$i</td>";}
    else{$daysTDs .= "<td></td>";}
}
echo $daysTDs;
echo'</tr>';

check the code and tell me did it get the desired result or not