How to display table row like this

Question

i have this table in mysql

this is mycode //generate report

<center>
<table border=1 cellpadding=10>
    <tr>
        <td rowspan="2">TANGGAL</td>
        <td colspan="2" align="center">PAGI</td>
        <td colspan="2" align="center">SORE</td>
        <td rowspan="2">JML. JAM</td>
        <td rowspan="2">JML. Rp</td>
    </tr>
    <tr>
        <td>Masuk</td>
        <td>Keluar</td>
        <td>Masuk</td>
        <td>Keluar</td>
    </tr>
    <?php   
    $qry_tgl=mysql_query("SELECT id_peg, date(waktu) Tgl from absen where id_peg=1 group by Tgl");
    while($row=mysql_fetch_array($qry_tgl)){    
        echo "<tr>";
            echo "<td>".$row['Tgl']."</td>";
        echo "</tr>";
    }           
    echo "</table></center>";

I want to display table in php like this

Please help me, I'm very confuse what to do

thankyou

*"i want to display table in php like this html php table"* - Question is; what results are you getting now? Edit: in order to have something to be compared with. — Funk Forty Niner, Jun 01 '17 at 14:20
Can you see the red box? http://php.net/manual/en/function.mysql-query.php — Andreas, Jun 01 '17 at 14:22
**Stop** using deprecated `mysql_*` API. Use `mysqli_*` or `PDO` — Jens, Jun 01 '17 at 14:24
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 01 '17 at 14:25
I tried to fix it. Is it not just a space that is needed. I never remember what to do and with mobile page I can't see what has been done until I save edits — Andreas, Jun 01 '17 at 14:26

Viacheslav · Answer 1 · 2017-06-01T16:23:18.607

Your HTML code seems to be fine, but SQL query not. I assume you were asking about how to make a correct query. When using GROUP BY you should (and in newer MySQL versions by default must) select only fields with either aggregate functions like SUM, AVG, etc., or select fields that are specified in GROUP BY. You cannot group by result of a select.

Your query should be like this:

SELECT DATE(waktu) Tgl FROM absen WHERE id_peg=1 GROUP BY DATE(waktu)

EDIT: Ok, according to google translate and other sites Masuk/Keluar are Since/Till. And using new provided information this should do the trick:

<center>
<table border=1 cellpadding=10>
<tr>
    <td rowspan="2">TANGGAL</td>
    <td colspan="2" align="center">PAGI</td>
    <td colspan="2" align="center">SORE</td>
    <td rowspan="2">JML. JAM</td>
    <td rowspan="2">JML. Rp</td>
</tr>
<tr>
    <td>Masuk</td>
    <td>Keluar</td>
    <td>Masuk</td>
    <td>Keluar</td>
</tr>
<?php
function display_row($timeRanges) {
    $pagi = isset($timeRanges[1]) ? strtotime($timeRanges[1]) - strtotime($timeRanges[0]) : 0;
    $sore = isset($timeRanges[3]) ? strtotime($timeRanges[3]) - strtotime($timeRanges[2]) : 0;
    $seconds = $pagi + $sore;

    echo "<tr>";
    echo "<td>" . substr($timeRanges[0], 0, 10) . "</td>"; // TANGGAL
    echo "<td>" . substr($timeRanges[0], 11, 8) . "</td>"; // PAGI Masuk
    echo "<td>" . (isset($timeRanges[1]) ? substr($timeRanges[1], 11, 8) : "") . "</td>"; // PAGI Keluar
    echo "<td>" . (isset($timeRanges[2]) ? substr($timeRanges[2], 11, 8) : "") . "</td>"; // SORE Masuk
    echo "<td>" . (isset($timeRanges[3]) ? substr($timeRanges[3], 11, 8) : "") . "</td>"; // SORE Keluar
    echo "<td>" . round($seconds / 3600, 2) . "</td>"; // JML. JAM shows rounded number of hours
    echo "<td>" . round($seconds * 5000 / 3600) . "</td>"; // JML. Rp number hours * 5000
    echo "</tr>";
}

$qry_tgl=mysql_query("SELECT DATE(waktu) Tgl, waktu FROM absen WHERE id_peg=1 ORDER BY waktu");
$lastDate = null;
$timeRanges = array();
while($row=mysql_fetch_array($qry_tgl)){
    if( $row['Tgl'] !== $lastDate ) {
        if( $lastDate !== null )
            display_row($timeRanges); // renders the row when the date changes, but only if we fetched at least one date
        $lastDate = $row['Tgl'];
        $timeRanges = array();
    }
    $timeRanges[] = $row["waktu"];
}
if( $lastDate !== null )
    display_row($timeRanges); // renders the last row when the loop ends, but also only if we fetched at least one date
echo "</table></center>";

This algorithm reads dates into $timeRanges array until another date is encountered. So when display_row() function is called the $timeRanges will contain only ordered records for the same date.

I'm afraid doing it using only MySQL would be slow and a huge waste of resources.

sorry but it's not exactly that i want. i wanna fill blank space column table (see html table) with query results from mysql waktu column. for example Mysql waktu column has day value like 2017-06-01 repeats 4 times (see mysql table) that's i want to fill to blank html table space. html table (pagi: masuk, keluar) 2 times + (sore: masuk, keluar) 2 times. sorry for my english. thankyou — Komang Panca, Jun 01 '17 at 14:55
@KomangPanca, what are JML.JAM and JML.Rp columns? Will there be any information shown from the same table or from some other table? If so then what information? I need to know, because it affects how I will write the final algorithm. — Viacheslav, Jun 01 '17 at 15:23
Thankyou for your quick reply. JML. JAM is sum calculation of time column Pagi: (Keluar (-) Masuk) (+) Sore: (Keluar (-) Masuk). and JML Rp is calculation of JML. JAM * 24 * 5000 (5000 is salary in hour) — Komang Panca, Jun 01 '17 at 15:51

How to display table row like this

1 Answers1