What's the proper way to output MySQL results to <table> format from PHP?

Question

How do you display your MySQL results when outputting to <table> format? I feel my own code could use some edits. Am I even doing this correctly?

Edit: My code is obviously wrong/ugly. What's the best way to do this while using only plain PHP instead of Smarty?

$items_per_row = 3; // How many <td> I want to add for every <tr>

// Query the MySQL db
$sthandler = $dbhandler->prepare("SELECT col1, col2 FROM sampletable");
$sthandler->execute();

// Save each row to array
$allitems = array();
while($row = $sthandler->fetch(PDO::FETCH_ASSOC)){
    $allitems[] = $row;
}

$markup = '';
foreach($allitems as $key=>$val){
    $col1 = $allitems[$key]['col1'];
    $col2 = $allitems[$key]['col2'];

    // START THE MARKUP HERE
    $markup .= $key % $items_per_row == 0 ? '<tr>' : '';
    $markup .= <<<EOD
    <td>
        <p>$col1</p>
        <p>$col2</p>
    </td>
EOD;
    $markup .= ($key + 1) % $items_per_row == 0 ? '</tr>' : '';
}

Then I can just <table><?php echo $markup; ?></table>.

Fyi, you can get rid of the first loop and just do $allitems = $sthandler->fetchAll(PDO::FETCH_ASSOC); — ThiefMaster
– ThiefMaster, Commented Dec 31, 2011 at 19:32
You spend several weeks learning the latest trendy framework and templating system, including the three intervening upgrades and bug fixes as well as shoeing in an ORM at which point by chaining 1/2 dozen objects together and factoring in the extras libraries you have a basic form that works but you are not sure why. Then you spend another two weeks fighting the framework/ORM/js libs to get functionality that the original setup didn't get round to implementing yet and it works. At this point though the client has changed their mind and decided it is a multi page set of forms all ajax driven. — PurplePilot
– PurplePilot, Commented Dec 31, 2011 at 19:39

Tom van der Woerdt · Accepted Answer · 2011-12-31 19:24:25Z

3

Separate your business logic from your presentation code!!!

I use a template parser, to separate HTML from PHP. For example, with Smarty I would use something like:

// Sample data
$tableData = array(
    array('col1', 'col2'),
    array('col1', 'col2'),
    array('col1', 'col2'),
    array('col1', 'col2'),
);

// Create the view
$tpl = new Smarty();
$tpl -> assign('table', $tableData);
$tpl -> display('myPage.tpl');

Then in the template file I build the table:

<table>
{foreach from=$table item=row}
    <tr>
        <td>{$row.0|escape}</td>
        <td>{$row.1|escape}</td>
    </tr>
{/foreach}
</table>

This separates your HTML from your PHP code, making it easier to add new features, and making your code more readable.

Edit
Yuck, did you really have to change your question that much?

Fine, use this template instead :

<table>
<?php foreach ($tableData as $row) { ?>
    <tr>
        <td><?php echo htmlspecialchars($row[0]); ?></td>
        <td><?php echo htmlspecialchars($row[1]); ?></td>
    </tr>
<?php } ?>
</table>

Usage:

// Sample data
$tableData = array(
    array('col1', 'col2'),
    array('col1', 'col2'),
    array('col1', 'col2'),
    array('col1', 'col2'),
);

// Create the view
include('myPage.php');

Of course, this solution is a lot dirtier than using a proper template engine.

edited Dec 31, 2011 at 19:24

answered Dec 31, 2011 at 19:15

Tom van der Woerdt

30.1k7 gold badges76 silver badges105 bronze badges

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

10 Comments

user895378 Over a year ago

+1 Here it comes! The religious nerd war over whether "PHP is its own templating system!" Oh the humanity!

Tom van der Woerdt Over a year ago

@rdlowrey Well, since Smarty simply converts TPL to PHP, I'd say you are correct. However, by using TPL you really separate view logic from business logic.

user895378 Over a year ago

I'm totally with you, I <3 Smarty ... I'm just preparing for the flame war that usually results from this topic :)

hakre Over a year ago

Why do you want to output an empty table?

Tom van der Woerdt Over a year ago

@hakre Where do you see an empty table?

|

Maxime Pacary · Accepted Answer · 2011-12-31 19:29:03Z

This would be a basic function for displaying arrays it your HTML templates.

You could improve it as your convenience by adding optional parameters, allowing to control if htmlspecialchars() should be applied to values, add some style to even/odd rows, telling to echo or return the HTML result, etc.

function formatToTable($array)
{
  $str = "<table>\n";
  foreach($array as $row)
  {
    $str .= "<tr>\n";

    foreach($row as $value)
      $str .= "<td>".htmlspecialchars($value)."</td>\n";

    $str .= "</tr>\n";
  }
  $str .= "</table>\n";

  echo $str;
}

You can, as suggested by Tom, use a templating engine like Smarty (there are pros and cons however). In that case you can register formatToTable() as a new Smarty function:

$smarty->register_function('formatToTable', 'formatToTable');

// and then into templates simply do:
{formatToTable array=$array}

// note that the formatToTable() function has to be slightly modified:
function formatToTable($params)
{
  if (!isset($params['array']) || !is_array($params['array']))
    throw new Exception("Missing parameter 'array'"); // for example

  $str = "<table>\n";
  // etc.
}

hakre · Accepted Answer · 2011-12-31 20:45:25Z

Your code can be a little bit streamlined, right, but that's normal. There were already some hints about that you can fetch the data from the database more easily:

// Query the MySQL db
$sthandler = $dbhandler->prepare("SELECT col1, col2 FROM sampletable");
$sthandler->execute();
$allitems = $sthandler->fetchAll(PDO::FETCH_ASSOC);

Then you want to have both col1 and col2 in a paragraph of it's own. That's already per each sql result row, so this is fine.

Then you want to have three of those pairs per each table-row in the output. There is a function in PHP that does this for you:

array_chunk($allitems, $items_per_row, 1);

This will result in the table-rows you're looking for. As this might be empty but still would represent one table, I wrap it into an additional array again:

### PROCESS DATA (here obviously for viewing)

$allitems = array(array_chunk($allitems, $items_per_row, 1));

So now the data is in the right structure for the output. But for this data's presentation each element needs to get one HTML tag around it - recursively. From the outer to the inner the tags are:

### RENDERER
$tags = array('table', 'tr', 'td', 'p');

What's left is a decoration function to add these tags around each member:

$decorate = function($array, $tags, $f) {
    $tag = array_shift($tags);
    foreach($array as $element)
        echo "<$tag>", 
            is_array($element)
                ? $f($element, $tags, $f)
                : htmlspecialchars($element),
            "</$tag>";
};

It will wrap the tags to the appropriate level of the array recursively, that means the function calls another function if there are more items inside (see the is_array test).

Now everything is ready to be fired up, the array get's decorated with the tags, the inner elements of the array will use the same function, so it's passed as parameter as well:

$decorate($allitems, $tags, $decorate);

Done. Demo. Example code at a glance:

<?php

### CONFIGURATION

$items_per_row = 3; // How many <td> I want to add for every <tr>

### GET DATA FROM STORE

IF (0):
// Query the MySQL db
$sthandler = $dbhandler->prepare("SELECT col1, col2 FROM sampletable");
$sthandler->execute();
$allitems = $sthandler->fetchAll(PDO::FETCH_ASSOC);
ELSE:
// Static test data
$allitems = array(
    array('col1', 'col2'),
    array('col1', 'col2'),
    array('col1', 'col2'),
    array('col1', 'col2'),
    array('col1', 'col2'),
    array('col1', 'col2'),
);
ENDIF;

### PROCESS DATA (here obviously for viewing)

$allitems = array(array_chunk($allitems, $items_per_row, 1));

### RENDERER
$tags = array('table', 'tr', 'td', 'p');
$decorate = function($array, $tags, $f) {
    $tag = array_shift($tags);
    foreach($array as $element)
        echo "<$tag>", 
            is_array($element)
                ? $f($element, $tags, $f)
                : htmlspecialchars($element),
            "</$tag>";
};
$decorate($allitems, $tags, $decorate);

Not to offend you, but this looks like the worst solution I've ever seen for a very simple problem.
That might be because Smarty does not make it easy to deal with recursive structures even it targets HTML.

Collectives™ on Stack Overflow

What's the proper way to output MySQL results to <table> format from PHP?

3 Answers 3

10 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related