while loop and html table

Question

have this code to generate multiple table from a db. The problem is that a don't know how to close the table, because I have some rules inside my php with if and else

<?php
mysql_connect("localhost","root","");
 mysql_select_db('test');


$barcode_cliente='1111111111111';

$sql="SELECT ora, prezzo, data, prodotto, SUM(quantita) as QtyTotal, SUM(totale_parziale) as Sumtotale_parziale
FROM barcode_consumazioni
GROUP BY 
ora, data, prodotto";
$result=mysql_query($sql);
$dates_precedente = NULL;
while($row=mysql_fetch_array($result)){
$dates="Ordine del ".$row["data"]." delle ore ".$row["ora"];
if ($dates === $dates_precedente) {
echo '<tr>
        <td>'.$row["prodotto"].'</td>
        <td> x'.$row["QtyTotal"].'</td>
        <td>'.number_format($row["prezzo"],2,",",".").' &euro;</td>
        <td>'.number_format($row["Sumtotale_parziale"],2,",",".").' &euro;</td>
    </tr>';
}
else
{
echo '<table style="text-align:center; margin-top:20px;" align="center" cellpadding="5">
<caption><u><b>'.$dates.'<b/></u><br /></caption>
<thead style="background-color:#EBE9E9">
    <tr >
        <th scope="col">Prodotto</th>
        <th scope="col">Quantit&agrave;</th>
        <th scope="col">Prezzo</th>
        <th scope="col">Totale parziale</th>
    </tr>
</thead>
<tbody style="background-color:F5FFFF">
    <tr>
        <td>'.$row["prodotto"].'</td>
        <td> x'.$row["QtyTotal"].'</td>
        <td>'.number_format($row["prezzo"],2,",",".").' &euro;</td>
        <td>'.number_format($row["Sumtotale_parziale"],2,",",".").' &euro;</td>
    </tr>
</tbody></table>';
 }

$dates_precedente = $dates;

}

?>

Now the problem is that if dates is unique I can close the table, but if $dates is not unique it generates others rows. I'm trying to find a rule that say, when $dates===$dates_precedente and it is the last, so you can put also </tbody></table>

It is unclear what you are wanting, can you spit out the results you are seeking for? — Demodave, Oct 06 '15 at 15:58

Tomas Ceruti · Accepted Answer · 2015-10-06T16:07:25.970

Try this:

    $dates_precedente = NULL;
    echo "<table>";
    while($row=mysql_fetch_array($result)){
        $dates="Ordine del ".$row["data"]." delle ore ".$row["ora"];
        if ($dates === $dates_precedente) {
             echo '<tr>....</tr>';
        }
        else
        {
             echo '</table><table><tr>...</tr>';
        }
    }
    echo "</table>";

This only closes the current table when $dates != $dates_precedente, and after that it starts a new table (without closing it as you did in your example). Then, after the loop has finsihed, you have to close the current table.

score 0 · Answer 2 · edited May 23 '17 at 11:58

I suggest you to use mysqli_* functions or PDO because mysql_* functions are deprecated (Why shouldn't I use mysql_* functions in PHP?). Also, take a look at this about MySQL and GROUP BY about selecting not nonaggregated columns not named in GROUP BY (https://dev.mysql.com/doc/refman/5.1/en/group-by-handling.html).

Combining that with the solution already provided by @Tomas Ceruti you can use the following:

$connection = mysqli_connect('localhost', 'root', 'your_password', 'your_database');
mysqli_set_charset($connection, 'utf8');
if (!$connection) {
    die("Database connection failed: " . mysqli_error());
}

$barcode_cliente = '1111111111111';

$sql = "
    SELECT ora, prezzo, data, prodotto, SUM(quantita) as QtyTotal, SUM(totale_parziale) as Sumtotale_parziale
    FROM barcode_consumazioni
    GROUP BY 
    ora, data, prodotto";
$result = mysqli_query($connection, $sql);
if(!$result) {
    die("SQL Error: " . mysqli_error($connection);
}

$dates_precedente = NULL;
while ($row = mysqli_fetch_array($result)) {
    $dates = "Ordine del ".$row["data"]." delle ore ".$row["ora"];
    if ($dates === $dates_precedente) {
        echo '
        <tr>
            <td>'.$row["prodotto"].'</td>
            <td> x'.$row["QtyTotal"].'</td>
            <td>'.number_format($row["prezzo"],2,",",".").' &euro;</td>
            <td>'.number_format($row["Sumtotale_parziale"],2,",",".").' &euro;</td>
        </tr>';
    } else {
        echo '
            </tbody>
        </table>
        <table style="text-align:center; margin-top:20px;" align="center" cellpadding="5">
            <caption><u><b>'.$dates.'<b/></u><br /></caption>
            <thead style="background-color:#EBE9E9">
                <tr >
                    <th scope="col">Prodotto</th>
                    <th scope="col">Quantit&agrave;</th>
                    <th scope="col">Prezzo</th>
                    <th scope="col">Totale parziale</th>
                </tr>
            </thead>
            <tbody style="background-color:F5FFFF">
                <tr>
                    <td>'.$row["prodotto"].'</td>
                    <td> x'.$row["QtyTotal"].'</td>
                    <td>'.number_format($row["prezzo"],2,",",".").' &euro;</td>
                    <td>'.number_format($row["Sumtotale_parziale"],2,",",".").' &euro;</td>
                </tr>';
    }
    $dates_precedente = $dates;
}

while loop and html table

2 Answers2