Php - PostgreSQL echo data into one cell (HTML Table)

Question

I'm trying to echo data from a database in the format shown below;

|column-1|column-2|column-3|column-4|column-5|
|--------|--------|--------|--------|--------|
|My Name |  Date  |Message | People |Phone #s|
                           | People |Phone #s|
                           | People |Phone #s|
                           | People |Phone #s|
                           | People |Phone #s|

That means, the data I'm selecting from the database is such that the people in column 4, 5 received the message (column-3) from the person on column-1. But according to my code that echoes data from the db, I have a new column for every other person on column 4 and 5 as shown below;

|column-1|column-2|column-3|column-4|column-5|
|--------|--------|--------|--------|--------|
|My Name |  Date  |Message | People |Phone #s|
|My Name |  Date  |Message | People |Phone #s|
|My Name |  Date  |Message | People |Phone #s|
|My Name |  Date  |Message | People |Phone #s|
|My Name |  Date  |Message | People |Phone #s|

Below is my php code that generates the table;

echo "<table id='table'>";
while($row=pg_fetch_assoc($result)){echo "<tr>";
echo "<td align='left' width='200'>" . $row['message_by'] . "</td>";
echo "<td align='left' width='200'>" . $row['message_date'] . "</td>";
echo "<td align='left' width='200'>" . $row['message_text'] . "</td>";
echo "<td align='left' width='200'>" . $row['phone_number'] . "</td>";
echo "<td align='left' width='200'>" . $row['recipient_name'] . "</td>";
echo "</tr>";}
echo "</table>";

So the question is how can I output column 4 and 5 data into a single cell or alternatively, echo out the data into different cells without repeating columns 1 to 3?

Store the previous value of (for example) message_by and if in the next loop it's the same value - don't output it. — Nigel Ren
– Nigel Ren, Commented Dec 18, 2017 at 8:10
rowspan and colspan will require me to assign them a number, how can I achieve that dynamically according to the number of people returned from the db? — Clint_A
– Clint_A, Commented Dec 18, 2017 at 8:15

Tim Biegeleisen · Accepted Answer · 2017-12-18 08:24:37Z

1

A typical way to handle your problem is to build a small state machine which keeps track of change in the column values. In your case, it appears that if any of the values of the first three columns change, then you want to print a full record. Otherwise, just print the last two columns. The trick used below is that we always drop 5 <td> tags down, but for repeat rows we only assign empty string into those cells.

$col1 = NULL;
$col2 = NULL;
$col3 = NULL;
echo "<table id='table'>";
while ($row = pg_fetch_assoc($result)) {
    $text1 = '';
    $text2 = '';
    $text3 = '';
    if ($row['message_by'] != $col1 ||
        $row['message_date'] != $col2 ||
        $row['message_text'] != $col3) {
        $col1 = $row['message_by'];
        $col2 = $row['message_date'];
        $col3 = $row['message_text'];
        $text1 = $col1;
        $text2 = $col2;
        $text3 = $col3;
    }
    echo "<tr>";
    echo "<td align='left' width='200'>" . $text1 . "</td>";
    echo "<td align='left' width='200'>" . $text2 . "</td>";
    echo "<td align='left' width='200'>" . $text3 . "</td>";
    echo "<td align='left' width='200'>" . $row['phone_number'] . "</td>";
    echo "<td align='left' width='200'>" . $row['recipient_name'] . "</td>";
    echo "</tr>";
}
echo "</table>";

Important: This answer will only work, and only makes sense, if your Postgres query sorted the result set using ORDER BY. In particular, the query should have been ordered with the three message columns being sorted first.

edited Dec 18, 2017 at 8:24

answered Dec 18, 2017 at 8:17

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clint_A Over a year ago

Thanks a tonne. It worked. And yes, my result set was already ordered.

Collectives™ on Stack Overflow

Php - PostgreSQL echo data into one cell (HTML Table)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related