Create html table with rowspan from mysql table with one query?

Question

I have a question. Let's say I have a relational table "ClientInvoices" with this structure:

id | id_client | id_invoice
1          1         1
2          1         2
3          2         3
4          3         4
5          1         5

You can see that CLIENT 1, have 3 invoices. Each client can have multiple invoices, and I to extract this information and put it in an html table, like this:


CLIENT | INVOICES
            1
    1       2
            5
    2       3
    3       4

How can I do that? I know is very simple, but I think I'm stuck. How can I calculate rowspawn, and show only one client with all invoices for him?

Madara's Ghost · Accepted Answer · 2011-11-18 09:42:36Z

Nice question, the calculation of rowspan is done by counting the invoices. Here's the code I came up with:

<?php

    /**
     * @author Truth
     * @copyright 2011
     */

    $dsn = "mysql:host=localhost;dbname=test";
    $dbc = new PDO($dsn, 'root', 'pass');

    $query = 'SELECT * FROM invoice';
    $stmt = $dbc->prepare($query);
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $result[$row['client_id']][] = $row['invoice_id'];
    }

?>
<!DOCTYPE html>
<html>

    <head>
        <!-- Metas -->
        <meta http-equiv="content-type" content="utf-8" />
        <meta name="author" content="Truth" />

        <title>Invoice Rowspan Example</title>

    </head>

    <body>

        <table id="invoices" border="1">
            <thead>
                <th>Client</th>
                <th>Invoices</th>
            </thead>
            <tbody>
                <?php

                    foreach($result as $id => $invoices) {
                        echo '<tr>';
                        echo '<td rowspan='. count($invoices) . '>' . $id . '</td>';
                        $count = 0;
                        foreach ($invoices as $invoice) {
                            if ($count != 0) {
                                echo '<tr>';
                            }
                            echo "<td>$invoice</td>";
                            echo "</tr>";
                            $count++;
                        }
                    }

                ?>
            </tbody>
        </table>

    </body>
</html>

This generates a table as you require. If there's something you don't understand, comment and I'll explain

remi bourgarel · Accepted Answer · 2011-11-18 09:15:32Z

1

Don't do it on the SQL side of the force !!

SQL = data ( data != html)

1 - Select your data with sql

2 - Load your data with php

3 - Put your data in the form you want with php / html

SQL wasn't made for creating html code it was made for selecting data, use the tools for what they was made for.

answered Nov 18, 2011 at 9:15

remi bourgarel

9,4394 gold badges46 silver badges76 bronze badges

Collectives™ on Stack Overflow

Create html table with rowspan from mysql table with one query?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related