Database Result Set to HTML Table Function in PHP

Hi All;

I’m looking at a situation where I need to have several database tables view-able on a web front end without a lot of time to do it.

I created a quick and dirty function to accomplish this.  I thought others might find it useful.


function resultset2html($tblclass,$tblid,$headers,$resultset,$title = null,$titleclass = null) {

//$tblclass:  The html class for the table
//$tblid:  The html id for the table
//$headers:  An array of column headers
//$resultset:  The resultset from the PDO query
//$title: Optional table title
//$titleclass: Optional table title class
$output = "<table class='$tblclass' id='$tblid'>";   //sets up the output.

//creates a table title if specified
if ($title != null) {
if ($titleclass == null) {
$titleclass = '';
} else {
$titleclass = "class='$titleclass'";
}
$output .= "<tr $titleclass>";
$colcount = count($headers);
$output .= "<th colspan='$colcount'>".$title."</th>";
$output .= '</tr>';
}

//header row
$output .= "<tr>";
foreach($headers as $colh) {

$output .= "<th>".$colh."</th>";  //creates header row

}

$output .= '</tr>';

foreach($resultset as $row) {   //iterates through result set

$output .= '<tr>';

foreach ($row as $line) {  //iterates through each column of result set

$output .= "<td >".$line.'</td>';


}

$output .= '</tr>';


}

$output .= '</table>';

return $output;

}

Here is the code where I retrieve the result set from a PDO connection ($dbh) and send it to the function.

I’m using Bootstrap for most of my formatting.


$sql = "SELECT rsndmsg,odatetime,lresenddt,attempts,discard,discardreason,notes FROM hrm_resends WHERE sent = 0";

$q = $dbh->prepare($sql);
$q->execute();

$resends = $q->fetchAll(PDO::FETCH_ASSOC);

$headers = array('Message Id','Original Date/Time','Last Attempt','# attempts','discard','Discard reason','Notes');

$resendtable = resultset2html('table-striped table-bordered', 'resends',$headers, $resends,"Messages Queued for Resend","tbltitle");

This is the non Bootstrap css I levered in:


td,th {
padding-left:5px;
padding-right:5px;
}

.tbltitle th {
background-color:#0073EA !important;
color:#FFF;
text-align: center;
font-weight: bold;
font-size:large;
}

and, here is the output!

php_resultset2html

SQL SERVER: Conditional selects

In a recent project I needed to come up with a way to return information from one of two tables, based on whether or not the value of the first column was null.

In situations like this,  the SELECT CASE statement is the best option.

This is the syntax:


(

SELECT CASE WHEN

[condition]

THEN

[return if true]

ELSE

[return if false]

END

)

Its important to know that you can use SELECT CASE in both the selects AND the WHERE CLAUSES of a sql statement.

Here is a full query,  where,  if the person’s address is null in table a,  it will provide the address from table b.

You would use the same syntax in the WHERE CLAUSE.


SELECT

a.name as Name,

(

SELECT CASE

WHEN

a.address IS NULL

THEN

b.address

ELSE

a.address

END

) as Address

FROM

users AS a

INNER JOIN

userdata AS b ON a.UID = b.UID

PHP: Efficient handling of conditional database queries

I have several database tables where I may have to only update one particular field,  based on conditions.

Except for that,  everything else in the statement is the same.

Instead of writing a bunch of different sql statements and calling them in a switch case,  I simply assign the target field to a variable,  and concatenate it into the sql statement that I send to the database.

Here’s an example where the value stored in $conditional dictates the target field for the update query


$p = array('newvalue',$mtindex);

$conditional = 'seven';

switch($conditional) {

case 'one':

$field = 'first';
break;

case 'two':

$field = 'second';
break;

case 'seven':

$field = 'seventh';
break;

default:

die('Unknown target field!');
break;


}

$sql = 'UPDATE mytable SET '.$field.' = ? WHERE mtindex = ?';

$q = $dbh->prepare($sql);
$q->execute($p);