The task: in a PHPRunner event, launch a PDF in a new tab/window.    Really??  Something that I had expected to be super-trivial ended up taking me hours of googling stack overflow just to figure out how to approach this.   Let’s start with why it was a problem and then get to the very quick one-liner solution.  This is more a PHP tip, than a PHPRunner tip, but I needed it recently in a PHPRunner application so maybe you do to?

First of all, even though we use PHP for web page interactions, it’s very easy to forget that PHP is a server-side scripting language. Especially as we have PHP script events surrounding all our data interactions in PHPRunner – it “feels” like PHP code is executing on the client. But it’s not! All of which means that PHP cannot make the browser do anything directly.

If you were building a web page in HTML, there are plenty of ways to set a hyperlink and tie an event/listener to it so that your user can click the href and you can control how the link opens a new target.  But what I (and many others apparently) were chasing was how to have a PHPRunner Add page accept data, and in an after_record_added event, have a PHP process run and generate a PDF dynamically based on data just added, then once the server processing is complete, and the PDF is available on the server, have it automatically open the PDF for the user.  In a new window or tab.  We should note here that the latter part isn’t something you get to decide – the user’s browser decides whether it will default to open the ‘window’ either as a new tab or as a new window.  All you can do is open the window.  Which is quite acceptable.

Continue Reading

Because it makes business sense!  If you could cut your business data application development cycle down by as much as 90%, and enable one full-stack developer to do the work that typically would need 3 or more specialists (database developer + back-end web services developer + front end web developer + QA for each).  That’s what I call a good set of reasons.  And … with very little downside.

Rapid Application Development (RAD) tools have been around for many years and in all different computing eras, from mainframe to minicomputer, and client server to web.  Powerbuilder was one of my favorites from the early days of client/server because it was so amazingly fast to build bug free Windows applications talking to complex enterprise grade SQL databases.  On the mini platform, I had many great times working with PACE on the Wang VS. They both did a full round trip soup to nuts rapid application build, taking care of all the plumbing in a tight yet flexible and extensible way that hasn’t really been equalled since.   And that meant that our QA was focused on did the application meet the business requirements?  Not on did a page or window render correctly!  Not only was our development cycle faster, cheaper, better, but we had a fraction of the QA people needed too.  Usually the business analyst/requirements team could do the needed QA.

But aren’t RAD tools just CRUD assemblers?  No. absolutely not.  Simple CRUD tools like Rails, or PHP Cake are better described as skeletons that you can edit with any text editor, and they save you some effort.  But you are still writing a lot of code by hand.  Code libraries are simply that too… libraries you incorporate in your code.

PHPRunner, and its sibling product ASPRunner, from XLineSoft, essentially inspect your database tables and relations and can generate a fully functional “80%” application in minutes.  The editing tool makes it full round trip, as you simply edit the fields using simple editors to choose fields on pages, set security options, change field labels in multiple languages, and so on.  then hit “build”, and you are done again.

And fully customizable too.  You have a full set of events that you can place your code in, and in several years working with PHPRunner, i have yet to find it lacking in appropriate events to put code, whether javascript or PHP.

It comes with jQuery embedded, so that’s easy to tap into for any custom DOM tweaking, or Ajax manipulations.  Although those are rarely needed.

Continue Reading

There are lots of ways you could approach this, if your needs are very specific. But I wanted a generic way of displaying some context sensitive user help in my PHPRunner applications. I have several PHPRunner projects that are using the same database, and the same help information.

Here were my requirements:-

  • accessible from a simple “Help” top level menu item at all times so I dont have to edit every single page that needs help
  • No matter which screen’set’ was active, the help menu item should open a new tab/window with the help content relevant to that screen set
  • I wanted to add the very bare minimum of application or PHP code to each app/project that was to use the help feature. Ideally, each screen set would set a help page ‘name’ as a session variable and that would be it
  • All code related to the help activity would be limited to the menu item, and then by the target help application. I did not want to have to write custom help code in every application
  • Ideally the target help ‘application’ would be a PHPRunner project because it would be easy to create and be pretty much ‘bug free’ taking care of all the normal rendering / html housekeeping. i.e., I could have written a custom PHP external page, but I wanted to try a couple of other techniques too
  • I wanted the help page content to be in a database table so that my subject matter experts could edit the page content, and do some basic formatting.  Essentially, enable it so that you didnt have to have PHPRunner editor to change static pages inside an application! Okay… busted… make it so that I didn’t have to edit help pages…

Challenges! And therefore what this tutorial will demonstrate!!

Continue Reading

Not everyone uses SQL triggers today. I’m a huge trigger fan. It allows you to implement business logic and rules at the appropriate model level in the application. Some rules should be client side, other rules should always be database side. Its more efficient, not to mention faster. I’ll put some more notes up soon about how denormalizing your physical schema can lead to massive gains in web tier productivity. But on with the topic de jour…

If you have triggers in your database, you’ll know that 99% of the time the entire trigger code should always, always, be enforced. that’s the point of the trigger. The application cannot get around it – that’s why we put those hard and fast rules in the triggers and constraints. Integrity is a beautiful thing.
However, as with every rule, there is always an exception! One likely exception that I occasionally hit is when doing a system upgrade, or other IT driven update to records, I do NOT want to disrupt the business logic and mess up the last modified dates, or other triggered logic columns. I probably want to “fix” a couple of columns in every row, or a given set of rows for some reason.
In this Use Case, my goal is to have the update fire, have the trigger NOT fire, and thus my fix gets applied, without disrupting the normal integrity of the data.
So how can we do this? There is no way to pass a parameter to a trigger on an adhoc basis! Sure, you can DROP the trigger, do your fix, then reload the trigger, but because that affects every user of the system, you now need full system down/maintenance window with all the inconvenience that entails! Whether you are MySQL, SQLServer, Oracle, DB2… I’ve done them all… there is no way. You cannot ‘drop’ a trigger for just one user. Unless you make one!

Let’s say you have a table “subscriber”, and you have a before insert and before update trigger that sets the create_datetime and last_modified_datetime each time a new row is added or updated. Pretty standard trigger stuff.
And you need to “fix” another column’s value without the create/update date fields getting updated.

Step 1: create a new column in your table definition called “operation_name”. Make it nullable. and typically, I make it varchar(30) or similar. think of this as a transient field. It has no business value.

In your application code, or SQL statements, you can now set the operation_name to any value you like. For example, “NO TRIGGER”.
In your SQL Trigger, which will fire in a discrete transaction, you can now see the “new” value of field operation_name and can act accordingly. In my example I want to skip the entire trigger.
In MySQL, the elegant way to skip the trigger from any point within it is to use the LEAVE statement.
It seems not a lot of people know how to use labels and LEAVE in SQL procedures and triggers, but they are incredibly useful for assisting in structuring maintainable SQL.

Normally, in a trigger you’d code something like…

CREATE trigger bi_subscriber before insert on subscriber for each row
begin
    set new.create_date = NOW();
    set new.update_date = NOW();
    /* ipse lorem... more code... */
end
$$

If however you “label” your begin/end statements, you can do a wondrous thing… you can leave that named block of code!

CREATE trigger bi_subscriber before insert on subscriber for each row
thetrigger: begin
    if new.operation_name = "NO TRIGGER" then
        LEAVE thetrigger;
    end if;
    set new.create_date = NOW();
    set new.update_date = NOW();
    /* ipse lorem... more code... */

end
$$

Now isn’t that a wonderful thing? Obviously you can now use the operation_name to influence trigger behavior instead of just leaving the trigger altogether. Now the client application can dynamically change the way the trigger rules fire. Very cool.

And because MySQL InnoDB is transaction oriented, users cannot clash on these things any more than they can when both trying to update the same row. Each update runs as its own discrete operation. The trigger is still fully active for all other users and insert/updates, just for this one transaction, the trigger knows to exit and do nothing. the nice thing being that this approach works from command line SQL tools too! So for those oddball DBA “fix” jobs it’s ideal.
This example is for MySQL, but the same thing applies to SQLServer and Oracle and most other SQL platforms.

I started doing this trick back in 1992 with SQLServer! Oracle didn’t get triggers that were worth anything until later in the 90’s.

Hope you like it.

This is one of those tiny little tips for “how to… ?” that I run into occasionally.
In PHPRunner (or ASPRunner!) events, like CustomAdd, where you are often doing more complex SQL processing, like inserting records into the current or related tables, you often need to know the ID of the record you just manually inserted in a CustomQuery(). I’m assuming that you are using AutoIncrement ID primary keys in this case.
For example, in an Employee Add process, in the customAdd event, I may have an additional step that inserts a row into a “user” table which is associated to the employee row.

$sql = "insert into user (field1, field2) values (somevalue, somevalue)";
 CustomQuery($sql);

Now perhaps you need to set the userID column in the Employee record to the ID of the User record just inserted. How do you get that value?

Recall that all your database work is happening on a global object in PHPRunner called $conn.
If you declare that as global in your script (first line!) then you get access to $conn in your script.

global $conn;

// rest of your script.

Now you can use regular MySQL functions to get the insert id out of $conn. Its a similar process for other supported databases.

all you need to code in your script is:-

$last_inserted_id = mysqli_insert_id($conn);

That’s it! Now you know the value of the autoincremented ID for the last inserted record that was done using $conn.

enjoy!

I love elegant, clean, code solutions, and this is one my favorites.  We’ve all had the issue of how to compute a person’s age when you know their date of birth, and you have a date at which you need to know what their age will be.

For example, I might know DOB and want to compute someone’s age at the next billing date.

This is it… one line of PHP brilliance 😉

$age = date_diff(date_create($_SESSION["current_subscriber_birthdate"]), date_create($_SESSION["next_bill_date"]))->y;

Continue Reading

Okay, this is one of the most difficult things to do in any event driven, web development environment. And it’s no different in PHPRunner. Most of the event code we’re playing with is PHP code. The snag is, you can’t just stick an “alert” in any old event line and have it appear on the screen! javascript is interfering with things… PHP code is on the server not the browser, flow of control has to happen before that alert gets back to the browser, lots of things basically get in the way of tracing what’s really happening in your front end and back end code. Continue Reading

This topic is all about how you can put your PHP code into separate source files and use those functions in your event scripts in PHPRunner applications. When your applications start to get more complicated, a few things happen.

a) you start to get longer and more complicated event scripts, and in my personal opinion, once you are over a few dozen lines of PHP, you will probably do better structuring your scripts into functions.

b) you start to get script features you want to include in more than page or button, or even in more than one PHPRunner application – having your code in a separate PHP file makes this trivial and means you aren’t duplicating code. Continue Reading