Another Programmers Are Human Moment

I wrote a PHP script that takes the details of an HL7 interface stored as XML and presents it in a nice neat HTML table.

The formatting is customized so most XML to HTML table tools won’t work for this.

As I parse the details out of the XML structure, I write them to the string $output.

I had everything working except for the parameters.

I gather the parameters by storing the XQuery paths into an array and iterate through that adding the details to the html table.

That bit wasn’t working.

I added some code to ensure that I was stepping through the array, and that the information was correct.

It took me about 20 minutes to see it….can you see the problem?


$output .= '<tr class="mdiparms">';

foreach($nxqArr as $nxq) {
         $ouput .= '<td>'.getNodeValue($nxq).'</td>';
}

$outout .= '</tr></table>';

Definitely an end of the day /facepalm moment!

Advertisements

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

PHP: BREAK and CONTINUE from Nested Routines (Such as loops and switch statements)

One of the most frequent “gotchas” that I run into are breaking out of loops properly.

Its one of those things that I don’t really think about when I’m banging out code,  and that I can miss when I go back to see why things aren’t working.

I’m currently working on a project that requires me to parse through comments entered into our Meditech Hospital Information System,  looking for key phrases and performing certain actions based on these key phrases.

The issue is that the comments are a single string (meaning that users may enter several things into the same field (its a Meditech thing),  and that this is free text.

I won’t bore you with the details, but suffice it to say, “Yay Reg Ex!” 🙂

The routine I’m working on looks to see if the user entered a month if a particular code (EDD) has been entered into the field.

That was easy enough,  but what I found that some users would enter a numeric month day,  or day month.

So I did a preg_match to find that.   I found that the script would hit this every time,  even though I placed it where it should only run if the month string wasn’t found.

I quickly realized that the break statement I placed to break out of the month search loop was only breaking out of the case statement!  So when it went looking for the next month,  it wouldn’t find it and as such would run the digit match routine!

The way to handle this is really easy…both the break and continue statements in PHP have optional integer arguments where you can specify how many structures to break out of!

Don’t worry,  I’m not going to paste in my whole solution and let you find the interesting bits.  You can always contact me if you’d like more detail on the project I describe.

In this example I’ll show you how to use continue from loops and nested loops.
You can use the same logic for breaks.


foreach($results as $row) {

 if ($row['text'] == 'EDD') {
 
 continue; //this will skip to the next $results;
 
 }

 foreach($pattern as $check) {
 
 if ($check['action'] == 'edd') {
 
 //do something here
 continue; // continue to the next $pattern
 
 } else {
 
 if ($something == $thathing) {
 
 continue 2; // continue to the next $results
 
 } else {
 
 break 2; //breaks out of $results
 
 }
 }
 
 
 }

}

In this example I’ll show you how to break out of a switch case statement

foreach($results as $row) {
 
 switch($row['text']) {
 
 case 'edd':
 
 if ($datacheck == 'yes') { //something indicating that you're finished processing $results
 
 //some code here
 
 break 2; //breaks out of both switch and $results
 
 
 } else {
 
 
 continue; //jumps to next $results
 
 }
 
 
 break; // case break
 
 default:
 
 //switch value wasn't handled
 break;
 
 
 }
 
 
}

Hopefully you found this useful. Please feel free to ask questions or suggest improvements in the comments section!

PHP LDAP: Get All Active Directory Groups and other useful things….

As the suite of applications and other useful tools I’m developing for our organization expand,   I’ve found it increasingly necessary to integrate with our Active Directory.

With the assistance of our AD guru, Adam Witt,  and Professor Google,  I’ve finally managed to pull the information I need,  without breaking anything.

My goal is to maintain a database table of AD groups in order to control access to certain applications.

If you’re sitting there thinking that I’m sort of sort of idiot because I’m not pulling the AD groups as needed,  I explain all that at the end of this blog post….please read it before pointing out what is blindingly obvious.

I used the PHP LDAP library for this project.  Here is a link to the entry on the PHP site.   Please note the prerequisites.

This is how to connect to the LDAP server


$ldap = ldap_connect('867.530.09.00','389');  //its not a real IP....its a musical easter egg!
if (!$ldap) {
die('No LDAP');
exit;
}

That’s the easy part.

In order to successfully pull information from your Active Directory,  it is essential that you know how its laid out.

Note:  I specified to KNOW how its laid out,  not how you THINK its laid out.

(in other words:  learn from my experience!)

Using the Microsoft AD Browser helped me tie everything together,  and figure out exactly what I had to use as filters, and search strings.

Before I get to the big chunk of code that does things,  I’m going to cover each of the gotchas that I had to work past.

LDAP Operations Errors

After much flailing about I found that there were two things causing these.

Remember to set Options

ldap_set_option($ldap,LDAP_OPT_REFERRALS,0);
ldap_set_option($ldap,LDAP_OPT_PROTOCOL_VERSION,3);

If your AD administrator likes things like security,  you will have to use the ldap_bind command to register with the AD server!

Here’s the code to tell the AD server the credentials of your PHP script


ldap_bind($ldap,'xxxxxxx','thisisnotreallyapassword');

So now you can connect to your AD server,  AND your AD server knows what rights and privileges your PHP script has.

The next step is to tell the PHP script where to look for whatever it is you want to look for.

Here’s the command:


$result = ldap_search($ldap,$base_dn,$filter,$attributes);

The parameters can be a bit tricky,  and this is where the understanding your AD layout is crucial.

The $ldap parameter is your connection that you set up earlier.

The $base_dn is the Distinguished Name at the top of the part of the AD structure you want to search.

Note that the base_dn is NOT limited to DN’s,  you can add other attributes.

In this example,  I’m going to want to search within the OU (Organizational Unit) of STAFF, within the DCs (Domain Controller)  of MAIN,  SITE, CA.

Loading the $base_dn variable will look like this:

$base_dn = "OU=STAFF,DC=MAIN,DC=SITE,DC=CA";

I will confess that I haven’t spent a lot of time playing with filters and attributes.   I’ve seen some examples of fairly complex filters,  so if you’re looking for something like that,  you can always hit up Google.   If you find good, working examples, I’d invite you to post them in the comments here.

For my example,  we’re going to look for all “OU”, “CN”, and “DC” attributes within any child of our base that is an OU.

$filter = "(OU=*)";

$attr = array("OU","CN","DC");

$result = ldap_search($ldap,$base_dn,$filter,$attr);

$rescount = ldap_count_entries($ldap,$result);

$data = ldap_get_entries($ldap,$result);

echo '<pre>';

foreach($data as $row) {

print_r($row);

}

That’s a pretty basic example.  I don’t have any sample data to show you as that would require either sending you our actual AD structure (which I think would violate some terms of my employment contract),  or require me to go and build an AD server.

I will leave you with this bit of code that I used to examine and validate the information that I was getting.

I wanted to ensure that I was only working with groups that had users,  so, in my validation stage I used this bit of code:


foreach ($info as $k=>$v) { //$v['dn'] becomes my base domain as I want to search its children
$sres = ldap_search($ldap,$v['dn'],"(CN=*)",$attrib);

if (ldap_count_entries($ldap, $sres) > 0 ) {

echo $v['dn'].' has '.ldap_count_entries($ldap, $sres).' users<br>';

}

echo '<hr>';
}

Hopefully it helps.  If you’ve any questions or improvements,  please feel free to post them.

Now…for those of you who can’t think of any reason why I wouldn’t just pull AD groups from the server as users connected….

Yes…I know that I could just pull the AD groups on the fly (I’m not stupid),     I want to ensure that I can maintain security functionality in the event that the AD server isn’t available (or doesn’t respond fast enough),   and,  I want the ability to audit access,  meaning that I would want to have a record of a user’s AD groups at the time they logged in.

Just because I know some of you are thinking about how stupid I am to not just record the groups in some sort of audit table,  I’m going to explain why I’m doing that way.

The short answer is Database Normalization.   

Database Normalization refers to the practice of designing a database to avoid, as much as possible, storing redundant data.   Meaning that if you have a piece of information,  such as an address,  that would be associated to another entity (such as a person),  that you record that piece of information once,  and then associate it to the parent entities through a joining table.

I know some people have a hard time wrapping their heads around that,  but believe me,  its an actual thing.

In this specific case,  recording a user’s groups (as each would have several) each time they connect would create a row for each group,  for each user,  each time they logged in.

When I was testing the first part of this project,   I connected twice and created 16 rows…..8 rows twice.

Imagine how fast that audit table would grow with 100’s of users connecting multiple times a day.

So,  when tracking data like this for historical audit purposes,  I keep a table of all AD groups.   Each group,  in addition to having its name stored in the table,   it will also have a unique identifier field that would serve as this table’s primary key.

With this solution,  instead of having 16 rows with the name of the group,  I would have 16 rows of at least two columns.

Column 1 would contain the unique identifier of my row in the user table,   and Column 2 would contain the unique identifier of the row of the groups table containing the individual group.

This solution takes up much less space,  and,  with proper indexing,   makes database operations much faster and more efficient.

There is also the added bonus that other database programmers won’t make fun of me.

PHP: Function to display array contents with optional exit

Our hospital recently implemented the iPeople Echo Data Repository,  allowing us to pull data from our Meditech Hospital Information System,  into a SQL Server data base where we can access it like normal people,  instead of relying on Meditech’s internal report writing language (NPR) to spit out text based reports.

This has allowed me to do things that were previously next to impossible,  such as providing near real-time patient census data.

I will publish some entries on this,  even though they are industry specific,  but that’s not what I’m here to do today.

Because of how the data is stored,  and the complexity of some of the requests I get,  I usually end up storing information in associative arrays within my PHP script.

Many of these arrays are built programmatically.

As I test things,  I often need to check the structure and contents of these arrays.

Naturally,  I would plug in the following code:


<?php

echo '<pre>';  //this is html tag for 'pretty print'.  It displays output in a readable format

print_r($array);

echo '</pre>';

exit;  //  I usually threw in an exit as some of the scripts could take a while to run,  and they all spit out formatted XML

?>

On a recent high stress, short deadline project,  I was faced with data that made no sense at all.  I needed to associate data from several tables from Meditech’s payroll module,  but there wasn’t any clear association at the level I needed.

As I was constantly trying new queries in PHP,  I was tired of typing the code above,  so finally I created a function called “showarray()”.

Here’s the syntax:


showarray($array,[exit = true]);

So, instead of typing all that,  I type one line of code,  and if I don’t want it to exit when done,  I put false as a second parameter.

Here’s the function:


function showarray($out,$exit = true) {
echo '<pre>';

print_r($out);

echo '</pre>';

if ($exit == true) {

exit;

}

}

It really helped speed things along…

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);

PHP: Ensuring that you can reference the last row inserted into a database by a user

One of the biggest challenges in writing applications that allow users to insert data into a database, is making sure that if you have to break up the sql statements,  such as if you need to update multiple tables and don’t use a function or stored procedure,  that you reference the correct row.

Sure,  you can rely on PDO’s  LastInsertid  however,  if you have a busy database environment,  you can run into a situation where suddenly your script is referencing the wrong row.

Here’s how that happens..

Users A  and B initiate an insert function at the same time.

User A’s instance runs,  but the LastInsertid is pulled after User B’s instance updates the table.

So now,  A’s instance is referencing User B’s row.

Another method I’ve seen used is pulling a specific row using the user id,  insert time,  and ORDER BY DESC.

That too can be effective,  however,  if there is a change to the database structure,  or there are delays,  you could still end up with problems.

Recently,  I came up with a way that I think works better.   Inserting a unique id generated by the php script.

I added a field to the table,  insertuid.  (Insert Unique Identifier)

This allows me to reference the row with a known value.   If there is a delay and the row doesn’t exist when I call it,  I can use error/exception handling to deal with that.

Here’s the code


$inuid = uniqid();

$sql = "INSERT INTO mytable (uid,insertuid) VALUES (?,?)';

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

$q->execute(array($uid,$inuid));

$sql = "SELECT mtidx FROM mytable WHERE insertuid = ?";

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

$r = $q->fetchAll();

$mtidx = $r['mtidx'];

//you can now reference the mytable row accurately