This is a little function that I knocked up for basic outputting of MySQL queries as tables. On a site that outputs lots of lists (e.g. customers, products, orders and invoices…) it can save a lot of time. It’s also handy for prototyping.
As parameters, it takes a MySQLi resorce identifier, a MySQLi query string and, optionally, a header formatting array and a table formatting array.
Basically, it returns an HTML formatted table with the output results, using th header cells and td data cells. It also adds striping using ‘rowEven’ and ‘rowOdd’ classes. You’d then use CSS to format the table appropriately.
The optional headerformat and tableformat arrays allow you to do some slightly more sophisticated formatting of the output using sprintf(): You pass values returned by the query as keys to your array, and then the sprintf() ‘format’ string you’d like to use as the values to your array… the function will use the values returned by the query as the sprintf() argument.
Sounds horribly complex, but it’s easy in practice.
So, for example, here’s my query:
$theQuery = "SELECT `ID`, `name`, `address`, `telephone`, `email`, `ID` as 'deleteme' WHERE `ID`=$foo";
You’ll note that `ID` appears twice, the second time as ‘deleteme’. That’s because I’m going to use the second instance of ID (now in a column called ‘deleteme’) to turn into a ‘delete me’ button for each record.
While I’m making an array for my function, I might pretty up some of the column titles, too:
$headFormat = array(
'ID' => "<th class='someClass'>Record ID</th>",
'deleteme'=> "<th class='dontDisplay'> </th>"
);
$tableFormat = array(
'ID'=>'<td><div class="buttons"><a href="admin.php?action=detail&ID=%1$s"><img src="icons/magnifier.png" alt=""/>%1$s</a></div></td>',
'deleteme'=>"<td><div class='buttons'><a href='admin.php?action=delete&ID=%u' class='negative'><img src='/ckc/icons/record_delete.png' alt=''/>Delete Me</a></div></td>"
);
So now, my table’s header will be built from the values returned from MySQL, except that ‘ID’ will be replaced by the less curt ‘Record ID’, and the second instance of ID now called ‘deleteme’ doesn’t get a header title at all, just an empty cell.
In the body of the table ‘ID’ will be replaced with a nice button linked to some ‘look at the record’s detail’ function with the record ID as its label. The ‘deleteme’ record ID will be replaced by a button which links to the ‘delete’ administrative function (with the ID passed as a $_GET value in the url), and a nice friendly ‘Delete Me’ label… see?
‘%u’ is the sprintf() placeholder for an integer, ‘%s’ for a string and the format ‘%1$s’…’%1$s’ allows you to use the same argument twice in the same format string.
You can read up on sprintf() at http://www.php.net/manual/en/function.sprintf.php
Now, assuming I have a MySQLi database link stored in the global variable “$GLOBALS['db']” all I need to do is build the table and output it:
echo "<h3>Check out my groovy list of records!</h3>\n";
$theTable = mysqli_query2table($GLOBALS['db'], $theQuery, $headFormat, $tableFormat);
echo $theTable;
So, without any more ado, here’s my little function in all its glory.
Do with it as you will. I hope it proves useful to someone.
(If it does, please let me know with raucous, loud and public adulation. If you spot a bug, security hole or other foolishness, please let me know in a quiet and discrete email!)
<?php
/*
** Takes a mysqli link resource, a query string and, optionally, arrays for the header
** and the table rows which indicate formatting.
** Returns a formatted HTML table with the results of the query.
** In the absence of formatting arrays, the table comprises <th> cells for the header
** and <td> for the body rows.
** Formatting arrays contain the column header string (as output by the query) as a key,
** and a 'sprintf()' style formatted tokenised string in which to place it as the
** sole argument...
**
** e.g: array( 'Outstanding' => '<th><i>%s</i></th>', 'Total Due' => '<th><b>%s</b></th>' )
*/
function mysqli_query2table($link, $theQuery, $headFormat=NULL, $tableFormat=NULL) {
$return=false;
if (isset($theQuery) && isset($link)) {
$result = @mysqli_query($link, $theQuery);
if (mysqli_connect_errno()) {
// $GLOBALS["debug"] .= sprintf("Error: %s\n", mysqli_error($link));
// or other such debugging stuff
}
if (@mysqli_num_rows($result) > 0) {
$noCols = mysqli_field_count($link);
$noRows = mysqli_num_rows($result);
$class='rowEven';
$table="<table>\n";
for ($row=1; $row <= $noRows; $row++) {
$resultRow = mysqli_fetch_assoc($result);
if ($row==1) {
// get the fieldnames for column headings (using the key of the array)
$table.= "<tr>";
foreach ($resultRow as $key => $value) {
if (@array_key_exists($key, $headFormat)) {
$table.= sprintf($headFormat[$key], $key);
} else {
$table.= "<th>$key</th>";
}
}
$table.= "</tr>\n";
}
$table.= "<tr class='$class'>";
// put the row into columns and add the alternating class
foreach ($resultRow as $key => $value) {
// get the field values for columns (using the values of the array)
if (@array_key_exists($key, $tableFormat)) {
$table.= sprintf($tableFormat[$key], $value);
} else {
$table.= "<td>$value</td>";
}
}
$table.= "</tr>\n";
if($class == 'rowEven'){
// swap the row class for stripey tables
$class = 'rowOdd';
} else {
$class = 'rowEven';
}
}
$table .= "</table>\n";
$return = $table;
}
}
return $return;
}
?>
