TripleDeal TripleDeal - 6 months ago 9
HTML Question

Each value is displayed in a new row HTML table

I'm currently working on a small website where my colleagues can see their work times.


  • My "rooster" table looks like this:

  • rooster_id int(2) Auto_increment primary key.

  • personeel_id int(2) //personeel means staff in the Dutch language
    (I'm using inner join to get the person's name).

  • dag varchar(10) //dag means day.

  • start varchar(5) //start is the time when the employee has to start
    working.

  • eind varchar(5) //eind means end.

  • datum date

  • weeknummer int(2) //weeknummer means weeknumber.



Display:

---------------------------------------------------------------------
|rooster_id|personeel_id|dag |start|eind |datum |weeknummer|
|1 |1 |Tuesday |12:00|21:00|2016-05-10|19 |
|1 |1 |Wednesday|15:00|21:00|2016-05-11|19 |
|1 |2 |Monday |08:00|18:30|2016-05-10|19 |
---------------------------------------------------------------------


My php code above HTML tag:

include_once 'config.php';

$people = "SELECT DISTINCT * FROM personeel INNER JOIN rooster ON rooster.personeel_id = personeel.id";
$result = mysql_query($people);


My php code inside the table tag:

<table cellpadding="1" width="100%" cellspacing="1" class="box-inhoud">
<?php
while($row = mysql_fetch_array($result)){
echo "<tr>";
echo "<td>".$row['naam']."</td>";
if($row['dag'] === "maandag"){
echo "<td class='td-midden'>".$row['start']." - ".$row['eind']."</td>";
} else {
echo "<td class='td-midden'>Vrij</td>";
}
if($row['dag'] === "dinsdag"){
echo "<td class='td-midden'>".$row['start']." - ".$row['eind']."</td>";
} else {
echo "<td class='td-midden'>Vrij</td>";
}
if($row['dag'] === "woensdag"){
echo "<td class='td-midden'>".$row['start']." - ".$row['eind']."</td>";
} else {
echo "<td class='td-midden'>Vrij</td>";
}
if($row['dag'] === "donderdag"){
echo "<td class='td-midden'>".$row['start']." - ".$row['eind']."</td>";
} else {
echo "<td class='td-midden'>Vrij</td>";
}
if($row['dag'] === "vrijdag"){
echo "<td class='td-midden'>".$row['start']." - ".$row['eind']."</td>";
} else {
echo "<td class='td-midden'>Vrij</td>";
}
if($row['dag'] === "zaterdag"){
echo "<td class='td-midden'>".$row['start']." - ".$row['eind']."</td>";
} else {
echo "<td class='td-midden'>Vrij</td>";
}
if($row['dag'] === "zondag"){
if($row['start'] === "00:00:00"){
echo "<td class='td-midden'>Feestdag</td>";
} else {
echo "<td class='td-midden'>".$row['start']." - ".$row['eind']."</td>";
}
} else {
echo "<td class='td-midden'>Vrij</td>";
}
echo "</tr>";
}
?>
</table>


This displays the data like this:

------------------------------------------------------
|Employee's|Monday |Tuesday |Wednesday |
|Tom |Vrij |12:00 - 21:00|Vrij |
|Tom |Vrij |Vrij |15:00 - 21:00|
|Jack |08:00 - 18:30|Vrij |Vrij |
------------------------------------------------------


It also displays Thursday, Friday, Saturday, Sunday but I didn't want to write all of the days here on stackoverflow since those will have "vrij" as value.

But the thing is, I want to have it like this:

------------------------------------------------------
|Employee's|Monday |Tuesday |Wednesday |
|Tom |Vrij |12:00 - 21:00|15:00 - 21:00|
|Jack |08:00 - 18:30|Vrij |Vrij |
------------------------------------------------------


So every employee does only have 1 row with all their times listed in it.

I'm new on this website so I hope I explained everything good, so you guys can easily help me.

And yes I know, I'm using MySQL because I want to keep it simple and I have never worked with MySQLi nor PDO.

Answer

Here we go entire example:

<?php
    $con = mysqli_connect('localhost', 'root', '', 'test') or die(mysqli_error($con));
    $query = "SELECT * FROM personeel INNER JOIN rooster ON rooster.personeel_id = personeel.id";
    $result = mysqli_query($con, $query) or die(mysqli_error($con));

    if (mysqli_num_rows($result) > 0) {
        $arr = array();
        $nam = array();
        $day = array('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');
        while ($row = mysqli_fetch_assoc($result)) {
            $nam[$row['id']] = $row['naam'];
            $arr[$row['id']][$row['dag']] = $row['start'] . ' - ' . $row['eind'];
        }
        echo '<table border="1">';
            echo '<tr>';
                echo '<td>Employees</td>';
                foreach($day as $d){
                    echo '<td>'.$d.'</td>';
                }
            echo '</tr>';
            foreach ($nam as $k=>$n){
                echo '<tr>';
                    echo '<td>'.$n.'</td>';
                    foreach ($day as $d){
                        if(isset($arr[$k][$d])){
                            echo '<td>'.$arr[$k][$d].'</td>';
                        }else{
                            echo '<td>Virj</td>';
                        }
                    }
                echo '</tr>';
            }
        echo '</table>';
    }
?>

Output is:

enter image description here

Some details:

$nam array stores all fetched names from personeel together with his id relation.

Array
(
    [1] => Tom
    [2] => Jack
)

$arr is main array which contains relation between personeel id and his data:

Array
(
    [1] => Array
        (
            [Tuesday] => 12:00 - 21:00
            [Wednesday] => 15:00 - 21:00
        )

    [2] => Array
        (
            [Monday] => 08:00 - 18:30
        )

)

$day is static array with week days.

Walking trough both $nam and $arr arrays gives you desired table without any duplicates. In addition provided solution uses mysqli_* functions, so you can see how to use them.

Hope this will help you to solve your problem. :)

Comments