Array into html table using PHP MySQL

Question

I have 2 tables in a DB that I am joining with a PDO executes and I have it outputting in one form of table but I need it in a another format. Do I need some form of dynamic pivot?

The 2 tables are like this:

Tables in database

I am looking to get the following table from it:

enter image description here

The current query I am using to collate the data is:

$sql3 = $pdo->prepare('SELECT b.title, a.nameFROM poll_summary a INNER JOIN poll_answers b ON a.answers_id = b.id WHERE a.poll_id = ? ORDER BY title');
$sql3->execute([$_GET['id']]);
$testing = $sql3->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_ASSOC);

This is a sample array of what it gives:

Array ( [Knight ] => Array ( [0] => Array ( [name] => Dave ) [1] => Array ( [name] => Simon ) ) [Lieutenant ] => Array ( [0] => Array ( [name] => Tom ) ) )

I can get the table headers from the title column using:

<table border=1>
  <thead>
    <tr>
        <?php
            foreach($testing as $key => $val):
            ?>
                <th><?php echo $key;?></th>
             <?php endforeach; ?>
    </tr>
    </thead>
    <tbody>

        <tr>        


    </tbody>
</table>

But I can't figure out how to get the name values into the correct columns. I tried using this code but just can't get it to work how I want it to.

foreach($testing as $key => $val) {
    $arrlength = count($val);
    for($x = 0; $x < $arrlength; $x++) {
    //echo $key;

    echo $key;
    echo $val[$x]['name']; 

    }

}

Outputs:

Knight Dave Knight Simon Lieutenant Tom

@LeonKunštek Why? This is PDO, the prepare/execute code as shown is perfectly valid. — GrumpyCrouton
– GrumpyCrouton, Commented Dec 31, 2019 at 14:42
What would you bindParam to? This page will be used to various different polls / inputs / outputs so it will be different values every time. Would binding answer the question I asked? — SimonT
– SimonT, Commented Dec 31, 2019 at 14:44
OP, why would "Simon" have the title "Knight"? Why would "Tom" have the title "Lieutenant"? — GrumpyCrouton
– GrumpyCrouton, Commented Dec 31, 2019 at 14:45
@SimonT you would bindParam to this a.poll_id = ? which you did in execute, also my comment was more of a tip on improving the code. — Leon Kunštek
– Leon Kunštek, Commented Dec 31, 2019 at 14:47

Triby · Accepted Answer · 2019-12-31 18:09:44Z

1

Already having this structure in $testing:

Array ( [Knight ] => Array ( [0] => Array ( [name] => Dave ) [1] => Array ( [name] => Simon ) ) [Lieutenant ] => Array ( [0] => Array ( [name] => Tom ) ) )

Make the table

<?php
    // Just to keep same variable
    $output = $testing;
?>
<table border=1>
  <thead>
    <tr>
<?php
    // We need to know wich $title has more names
    $max = 0;
    foreach($output as $title => $names) {
       if(count($names) > $max) {
          $max = count($names);
       }
?>
                <th><?php echo $title;?></th>
<?php } // end foreach ?>
    </tr>
    </thead>
    <tbody>
<?php
    // Loop for creating table rows
    for($i = 0; $i < $max; $i++) {
?>
    <tr>
<?php
        // Loop for creating table cells
        foreach($output as $title => $names) {
?>
        <td><?php
             // Echo only if name exists
             if(isset($names[$i]['name'])) {
                echo $names[$i]['name'];
             }
        ?></td>
<?php
        } // end foreach
?>
    </tr>
<?php
    } // end for
?>
    </tbody>
</table>

edited Dec 31, 2019 at 18:09

answered Dec 31, 2019 at 16:58

Triby

1,7471 gold badge16 silver badges19 bronze badges

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

6 Comments

SimonT Over a year ago

print of $output has no data = Array ( [] => Array ( [0] => [1] => [2] => [3] => ) )

Triby Over a year ago

In your question you've posted Array ( [Knight ] => Array ( [0] => Array ( [name] => Dave ) [1] => Array ( [name] => Simon ) ) [Lieutenant ] => Array ( [0] => Array ( [name] => Tom ) ) ) and it's the same that $output that I've suggested, then go directly to make the table, just update variable names.

SimonT Over a year ago

There's nothing to update though as the variables are all the same....yet the print / vardump of $output is blank

SimonT Over a year ago

Actually I think I can see where you have gone wrong, ['title'] is in the array, it is the key.

SimonT Over a year ago

That's absolutely perfect! Thanks a lot Triby. Also seems cleaner than original answer. Apologies in how I said "gone wrong"

|

Collectives™ on Stack Overflow

Array into html table using PHP MySQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related