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!

JQUERY: Handle a click event on an HTML element within another clickable element

Confusing title?

Let me explain:

I’m working on a project that will allow my employer’s senior team a patient census board. (I work for a hospital if the “patient” thing didn’t give it away).

As this project spans 4 different hospitals,  I present the initial data in collapsed tables,  populated by JQuery from an XML document passed from a PHP script.

In order to expand a table,  I allow the user to click the appropriate row.

Yesterday I was asked to add some new functionality that would allow the user to call up a list of patients by clicking on a column within that row.

The information in this column is wrapped in a span for display purposes,  so I hooked onto that.

Of course,  when I click on it,  both the span click event, AND the row click event fire!

I prevent this,  by placing the span click event handler above the row click event handler in the js file AND ending the span click even with return false;

This is how the table structure is set up


<table>
<tr>
<th>header row</th>
</tr>
<tr class="exoccclick">
<td>Something</td>
<td>Another Thing</td>
<td><span class="pdcold">53</span></td>
</tr>
</table>

And this is the click handling code for both the tr class (exocclick) and the span class (pdcold)


$(document).on('click','.pdcold',function(e){

var tbid = $(this).closest('table').attr('id');

console.log('pdcclick ' + tbid);

return false;  //<--don't forget this bit!
});

$(document).on('click','.exoccclick',function(e){

//some stuff

});

As this is internal I can’t link the actual page,  but here’s a screen shot of what the row looks like:

The Pend DC Old column is the span class where I added the click handler

The Pend DC Old column is the span class where I added the click handler (click to enlarge)

PHP: Tracking session variable content while developing

I like using session variables to track various and sundry as users navigate through my sites.

The problem comes when I forget to change, or load a session variable.

What I used to do was add var_dump($_SESSION) lines to my scripts…but of course, then you have to go remove them.

This weekend I thought of a cleaner way.

I create a whole new file, usually sessvar.php. I then point my browser to it, and whenever I need to see what the session variables are, I simply refresh the page.

So, you’d navigate to yourpageurl/sessvar.php in a tab besides what ever you’re using to check your work, and refresh the sessvar.php page whenever you needed to see what variables your stored were.

<?php

session_start();

var_dump($_SESSION);

?>

JQuery/HTML: Handling events for elements created after initial page load.

My regular readers already know that I add a lot of stuff to my pages programmatically using PHP. Naturally much of this code is added after the initial page load.

I used to defeat this by loading in the scripting with the new elements, but that’s very tedious, and honestly, sloppy.

The issue is that new elements aren’t registered with the Document Object Model (DOM) set up when the page loads.

To get around that you can sort of “pre-register” these elements using JQuery’s .on method.

Here’s the syntax:


$(document).on([event],[selector],function(e) {

//do your stuff here

});

Here’s a working example from an actual piece of code I’m working on.

In this example I’m posting data to a php script when the target element (id=”edname_1″) loses focus, provided it doesn’t match what’s there already. I do this by loading the contents of the control into txtval on the focusin event.

var txtval = '';    //global variable.
		
		
		    $(document).on("focusin","#edname_1",function(e){
		    	
		    	txtval = $(this).val();   //loads global
		    	
		    });
		
			$(document).on("focusout","#edname_1",function(e){
				
					if ($(this).val() == txtval) {
						console.log("doing nothing");
						return;
						
					}
				
					if($(this).val().length < 1) {
						
						alert("This shouldn't be blank");
						return;
						
					}
				console.log("updating");
				$.post('PHP/updateftable.php', {field: $("#activefld").val(), val: $(this).val()}) 
                                            
                                        /*
                                         In order to avoid having to add the db table primary key of the row I want to update to every element I add to the div,                         
                                         I created a hidden field and placed that key as the value.
                                         */

				.done(function(data){
					
					console.log(data);   //checks the output.
							
				});
				
	
			});
		


This example is from a piece of actual code for a project in development, so there’s some exception handling, etc. I haven’t done yet.

JQuery: Add Rows and Columns to Tables Programmatically

This turned out to be a bit of a bear to search for exactly what I wanted, in such a way that I understood it…so I’m posting here.

The Problem

To create a table which the user could add columns or rows as they required.
New rows must contain the columns added earlier.

There is no requirement to isolate additional columns to specific rows.

The Solution

The example below uses a class selector as there are several tables (also added programmatically).

Adding a column.


$(".sbutc").click(function(e){
		
		var tid = this.id;
		var bid = tid.replace('sbutc_','stable_');   //This points to the appropriate parent table

                //first, I need to know how many rows there are so I can get the number of columns in the last one (skipping header rows)
                //Note that I point to the tbody object,  if you don't use these,  that should be the table id.

		var rows = document.getElementById(bid).getElementsByTagName("tbody")[0].getElementsByTagName("tr").length;
		

		var cols = $('#' + bid + ' tr:nth-child(' + rows + ') td').length;   //get's the number of columns in the last row
		console.log('cols: ' + cols);
		
		$("#" + bid).find('tr').each(function(){                            
						
                $(this).find('td').eq(cols-1).after('<td>new cell added</td>');      //adds the new cell. 
   		}); 

               /*
                You'll note that I subtract 1 from the column count when adding the column.  This is because when pulling the count, it starts with 1, while 
                object indexes always start at 0
               */

This adds the row.


$(".sbutr").click(function(e){
		
		var tid = this.id;
		var bid = tid.replace('sbutr_','stable_');
		var rows = document.getElementById(bid).getElementsByTagName("tbody")[0].getElementsByTagName("tr").length;
		var cols = $('#' + bid + ' tr:nth-child(' + rows + ') td').length; 
		
                
		
		var apstring = '<tr>';             //sets up the string used in the append statement
		
		for(var i=1;i<=cols;i++) {        //adds a td element for each row detected above and appends it to the apstring
			
			apstring += '<td ondrop="drop(event)" ondragover="allowDrop(event)">-</td>';
			
		}
		
		apstring += '</tr>';              //very important!
		
		$('#' + bid + ' > tbody:last').append(apstring);   //append the row to the bottom of the table
		
		
		
	});



I will post a link so you can see it in action when I get a chance (behind a firewall currently).

Set Selected Option when building HTML Select List in PHP

Wow…you know…the hardest thing about this whole exercise was coming up with a title for this post!

As you’ve gathered from previous entries, I build a lot of my HTML pages in PHP scripts.

When you are building a select list (dropdown, pulldown, picklist) from a database, how do you ensure that the user sees the value that’s in the database?

You could do it using a switch case routine and build your options list in it’s entirety for each option, or you could do it this way.

Scenario, you have an ENUM field in your database that you want to give the user the ability to edit. You also think its very handy for the user to know what’s actually in the database, so you have the select element default to the database value.

Short answer:

Use string replace (str_replace) to add the property selected to the appropriate option.


$options = str_replace('value="G"','value="G" selected',$options);

And now for the long version:

<?php
/*
A bunch of code that creates a dataset from a database and puts it in $row[]
*/

$row['ftype'] = 'T';      //you can change these to test the code.
$row['fname'] = 'Text';

$options = '<option value="0">Select a Type</option>';
$options .= '<option value="D">Date</option>';
$options .= '<option value="G">Group</option>';
$options .= '<option value="T">Text</option>';

switch ($row['ftype']) {

    case "D": 

        $options .= str_replace('value="D"','value="D" selected',$options);
        break;

   case "G":

      $options .= str_replace('value="G"','value="G" selected',$options);
      break;

  case "T":

     $options .= str_replace('value="T"','value="T" selected',$options);
     break;

  default:
     //nothing to do here
     break;
}

$output = '<html>';
$output .= '<body>';
$output .= '<form id="f1" method="post" action="">';
$output .= '<input type="text" id="f1a" name="f1at" value="'.$row['fname'].'"><br>';

$output .= '<select id="f1b" name="f1b">'.$options.'</select>';
$output .= '</form>';    //pro tip:  type this line immediately after typing the <form> line so you don't forget.

$output .= '</body></html>';
echo $output;

Some of you may have noticed that there is a much easier way to do this if things are as straightforward as in the example.
Simply concatenate the value used in the switch case. This will work great, unless you have additional properties or different formatting in the option tag.


 $options .= str_replace('value="'.$row['ftype'].'"','value="'.$row['ftype'].' selected',$options);

Happy coding…