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

This is the standard every vendor should strive to!

Its not often that I gush over customer service.  I will express appreciation and fill out surveys when I’m happy, and leave it at that.

We have been using the iPeople Connect suite as our data repository for just over a year now.   Having our Meditech data available in SQL Server has made it possible to implement solutions that would’ve required expensive vendor assistance in the past.

The iPeople team has always provided stellar support and we’ve been very happy with them.  Today though, they blew us away with their dedication to customer service by reaching out proactively on an issue.

I am working on a project that uses data pulled from nursing interventions.  The project is not yet live and until an hour ago,  I hadn’t had a chance to work on it for the past week.

I noticed that there was no information crossing over from Meditech.   It did’t take me long to discover the reason.  I had screwed up the start date of the download query.

iPeople ECHO allows us to customize download queries in order to ensure the we can get only what we need.   I decided to take advantage of that,  but in doing so I had neglected to add a line of code.

I started the download,  and was immediately distracted by another, unrelated task.

As I was finishing that up my phone rang.   It was Drew Sher from iPeople.

I was a bit shocked as there were no major issues with our system.  I knew this because I was looking at it at the time.

Drew had noticed the error in the download I was working on and called me to help me get it working the way I wanted.

Seriously,  how awesome is that?!

It would be like looking out at your driveway and seeing your dealer’s mechanic pull up to fix something because your car called them!

This is a big kudo’s to iPeople and their excellent staff!

 

 

Mirth: Making sure that dev/test code doesn’t get into LIVE (Is this lazy, or just accommodating reality?)

In a perfect world database connections and other things that must be changed when you migrate code between environments are neatly stored in one place where they can be easily changed.

With Mirth you can do this using global maps,  however when you do that,  they are exposed to anyone who has appropriate access to Connect.

I get around this by declaring variables at the top of the filters or transformers where I make database calls.

One of the things with my position is that I wear many hats and am the organization’s ‘go to’ for a great many things.  This means that interruptions are frequent.

This means that I when I’m doing something like preparing an interface to move from TEST to PRODUCTION that I can miss things.

Some of you might scoff,  but that is the reality of my world,  and almost 50 years on this planet has taught me that reality is often very far from ideal,   so you plan for reality.

I’m working on a project where I reach out to a couple of databases to pull data that isn’t included in the HL7 message.

Some of the transformations are complex,  and things were complicated by very tight deadlines,  and a situation where the spec “needed clarification”.  (as in I built my end to spec but they wanted a change).

Those of you familiar with this reality thing will know that this often leads to kludged together solutions.

After spending several hours making sure that our iPeople Echo downloads were moving the correct data from our test environment and copying/altering stored procedures and SQL functions I finally got to combing through my Mirth interface to make the changes there.

To give you an idea of the complexity,  I have 13 transformers in my source connector.

I realized that when I moved this to Live that there were too many potential failure points and wanted to prevent them.

So I added this code to my source filter:


var channelName = ChannelUtil.getDeployedChannelName(channelId);

if (channelName.indexOf('LIVE') > -1) {

logger.error('******************** CHECK ' + channelName + ' FOR DEV/TEST SETTINGS! ****************');
 return false;
 
}

return true;

/* Changes for Live
-remove anonymizer
-change db settings in source PID, PV1, OBR and ZDR
-change db settings in destination MR PID transformer

*/

Hey look…a checklist of sorts!

My thinking is that if I forgot to make this very basic change that I’ll look and wonder why all my messages are being filtered!

But then I got to thinking of how many times I’ve been interrupted today and what would happen if I missed a single change…

So I wrote this:

function envcheck(sql,strTest,strLive) {

 var channelName = ChannelUtil.getDeployedChannelName(channelId);

 if (channelName.indexOf('LIVE') > -1) {

 logger.error(channelName + ' **** CHANGE SQL STATEMENT FOR LIVE ' + sql);

 sql = sql.replace(strTest,strLive);
 
 }

 return sql;
 
 
}



Mirth: Monitor and Send Alerts every X hours

Hi;

This is a very quick post as I want to link the code to someone asking a question on LinkedIn.

Any one who has dealt with Mirth alerts knows that you can get flooded by messages when there is a problem.

I wrote this channel to alert us every 3 hours if a problem occurred in a fairly important channel.

You can see in this code how I use the ChannelUtil class to collect data.

You can also use this class to control the channel and connectors.  Here’s the documentation.

Here’s the code that is in a javascript writer destination.


var olissr = '91d05f9d-6020-4ecc-b9fb-5301c77a1e0f'; //this is the target channel

var cstate = ChannelUtil.getChannelState(olissr).toString(); //declares the channel as a channel util object

//get the stats

var cQ = ChannelUtil.getQueuedCount(olissr);
var cE = ChannelUtil.getErrorCount(olissr);
var cS = ChannelUtil.getSentCount(olissr);

//get the previous stats from the globalChannelMap for comparison purposes.
var tq = globalChannelMap.get('tq');
var te = globalChannelMap.get('te');
var ts = globalChannelMap.get('ts');
var lt = globalChannelMap.get('curtime');
var ltms = globalChannelMap.get('timelastmsgsent');
var ltas = globalChannelMap.get('activitylastalert');
var ltaserror = globalChannelMap.get('errorlastalert');
var curtime = java.util.Calendar.getInstance().getTimeInMillis();
//puts the variables into the channelMap in case they're needed by the mailer.

channelMap.put('cQ',cQ);
channelMap.put('tq',tq);
channelMap.put('ds',ds);
channelMap.put('e',cE);
channelMap.put('s',cS);
channelMap.put('lastime',lt);
var timediff = curtime - lt;

channelMap.put('tdiff',timediff);

globalChannelMap.put('tq',cQ);
globalChannelMap.put('te',cE);
globalChannelMap.put('ts',cS);
globalChannelMap.put('curtime',curtime);

//error

var errordiff = cE - te;
if (errordiff > 0) {

var leralert = ((curtime - ltaserror)/1000)/60;

if (leralert > 15) {

channelMap.put('SendAlert','yes');
channelMap.put('AlertSubject','Errors on OLIS-SR Channel!');

var body = "There are errors on the OLIS-SR Channel!\nErrorCount: " + te + "\nDifference in error count since last check: " + errordiff + "\n";
channelMap.put('AlertBody',body);
globalChannelMap.put('errorlastalert',curtime);
return;
}

}
//inactivity

ics = parseInt(cS);
its = parseInt(ts);

var ds = cS - ts;

if (ds != 0) {

ltms = curtime;
globalChannelMap.put('timelastmsgsent',curtime);

} else {
var timelag = ((curtime - ltms)/1000) / 60;

channelMap.put('timelaginmin',timelag);
channelMap.put('alertlag',alertlag);
if (timelag > 60) {
var alertlag = ((curtime - ltas)/1000) / 60;

if (alertlag > 120) {

channelMap.put('timelag',timelag);

channelMap.put('SendAlert','yes');
channelMap.put('AlertSubject','Inactivity on OLIS-SR Channel!');
channelMap.put('AlertBody','There has been no activity on the OLIS-SR Channel for ' + timelag + ' minutes!');

globalChannelMap.put('activitylastalert',curtime);
return;
}

}
}

You know you should take a break from coding when…

Recently,  I was trying to get something in our Mirth Integration Engine working.  A project that was behind for a variety of reasons.

I was exhausted,  and stressed when co-workers heard me exclaim:

WTF do you mean its undeclared!   Its declared right there you stupid machine!!!

I waved my fist at the monitor in a threatening manner,  yet this infernal contraption kept telling me “variable mytpe undeclared in line 936″

I thought of EVERYTHING!   I checked to see if scope was the issue,  I was pretty sure it wasn’t.  I thought maybe its a datatype thing,  which made no sense at all…

Finally, in a fit of frustrated rage I walked away, grabbed a coffee, and paced around for a bit before going back to it.

I then took another look at the code


var mtype = msg['MSH']['MSH.10']['MSH.10.1'].toString();

if (mytpe.indexOf('D') > -1) {

}

I checked everything except spelling!

There’s a few minutes of my life I’ll never get back.

Unintentionally Creepy Declarations #1

When we’re in the zone,  sometimes we don’t really think about our variable names,  so long as they’re meaningful.

I just typed out a table declaration for a new Table Value Function in SQL Server.

After I coded that bit I kept thinking,  there’s something wrong with that…

Then I saw why it was creeping me out…


DECLARE @table as TABLE (

DICTSECONDS varchar(30) NULL,
DRAFTSECONDS varchar(30) NULL,
FINALSECONDS varchar(30) NULL, -- do you see it?
SIGNEDSECONDS varchar(30) NULL
)

My Coding Playlist

Music is like religion to me.

A close friend once told me that she can tell how my life is really going by what music I’m listening to.

She refused to elaborate,  knowing full well it would change the algorithm.

My various playlists have everything from Five Finger Death Punch to Handel’s Water Music.

You will find they are heavy with Electronica though.   This surprises many as I’m months away from my 50th birthday and am not inclined to attend “Raves”  (most of them happen after my bedtime).

Electronica is my preferred “Coding Music”.   It is energetic,  powerful and driving,  however it mostly lacks any sort of lyric or message that will stop me dead in my tracks.  “Eat, Sleep, Rave Repeat” was an exception,  I was laughing when I first listened.

I thought I would share the favorites from my Coding Playlist.  Those that have lasted the longest.

That Miami Track – Tocadisco

You’ll Find Yourself featuring Sarah Howells   John O’Callaghan

Better Off Alone:  Alice Deejay

Galvanize: Chemical Brothers

And of course…

Eat Sleep Rave Repeat : Fatboy Slim

That should keep you going for awhile:)