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: 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