MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘PHP’ Category

Popular Programming Languages

with 3 comments

First of all, Happy New Year!

IEEE Spectrum published a ranking of the most popular programming languages. Computational journalist Nick Diakopoulos wrote the article. While it may surprise some, I wasn’t surprised to find SQL in the top ten.

07dataflow-1403643424680Nick weighted and combined 12 metrics from 10 sources (including IEEE Xplore, Google, and GitHub) to rank the most popular programming languages.

  • Compiled programming languages (Java [#1], C [#2], C++ [#3], C# [#4], Objective-C [#16])
  • Interpreted programming languages (Python [#5], JavaScript [#6], PHP [#7], Ruby [#8], Perl [#11], HTML [#12])
  • Data languages (SQL [#9], MATLAB [#10], R [#13])

I couldn’t resist including Objective-C because it shows how the iPhone, iPad, and Mac OS impact our daily lives. At the same time, Assembly [#15] is actually more popular than Objective-C. Shell [#17] follows Objective-C. While the Visual Basic [#14] programming language still remains very popular.

There are many “why” questions raised by this list of popular programming languages. The “why” from my perspective deals with what are the market drivers for their popularity. The money drivers I see are as follows:

Business Intelligence (BI) software manages most high-level data analysis tools and they’ll continue to get better over time. However, if SQL has shown us anything over 30 years it’s that ultimately we revert to it to solve problems. The conclusion from the reality of BI probably means the programming languages that develop those tools will continue to rise and so will the underlying data languages.

It’s also interesting to note that nine out of ten of the popular programming languages work with databases, like Oracle, MySQL, PostgreSQL, or SQL Server. While JavaScript doesn’t access the database typically, it’s JSON (JavaScript Object Notation) is supported in all the databases.

Written by maclochlainn

January 1st, 2015 at 9:46 pm

Popular PHP Frameworks

with 2 comments

My students often ask me about popular PHP frameworks and MySQL approaches. I wish a PHP framework choice was as clear as a JavaScript framework, like AngularJS. Unfortunately, PHP frameworks aren’t that clear cut.

It seems that the most popular PHP frameworks are: Laravel (1st), Phalcon (2nd), and Symphony2 (3rd). I found the following graphic (from December 2013) that highlights popularity by percentage of the market (though I can’t guarantee its accuracy). As far as jobs go, on Dice.com only Laravel had more than 3 positions referring to the framework by name. There were actually 42 PHP developer positions that mention Laravel out of 2,115 PHP developer positions. So, it seems learning a specific framework for PHP doesn’t yet have much market appeal.

PHPFrameworkPopularity-1024x853

While learning the basics of PHP are generic, frameworks expedite process and control pattern implementation. My suggestion to students is to target three to five employers that use a specific framework and start learning how to use the framework. I’d like to get opinions from those in the field whether this is the best advice.

As to MySQL, I suggest they learn native Mysqli and MySQL PDO. Any other suggestions on that from readers? Thanks as always.

Written by maclochlainn

December 23rd, 2014 at 1:55 pm

Deprecated mysqli Functions

without comments

PHPDeprecation5_4

Having noticed the release of PHP 5.5.10 last week while I was speaking at UTOUG, I checked the deprecated mysqli functions web page. There weren’t any deprecated by 5.5. Unfortuantely, there were six mysqli functions deprecated in 5.3 and removed in 5.4. Unfortunately, many of my posted code examples use 5.2 or 5.3 where they would have worked. The deprecated mysqli functions are:

  • mysqli_bind_param
  • mysqli_bind_result
  • mysqli_client_encoding
  • mysqli_fetch
  • mysqli_param_count
  • mysqli_send_long_data

Unfortunately, that puts a lot of updates on my queue of things to do. I imagine it likewise imposes limits on those running commercially on PHP 5.3 or older that plan an upgrade.

It does remind me that I need to pay close attention to the deprecation of mysqli functions with each release. These are actually the largest change since PHP 5.0.

Written by maclochlainn

March 18th, 2014 at 12:28 pm

Posted in MySQL,mysqli,PHP

Tagged with , ,

MySQL Image Architecture

with 14 comments

The LinkedIn MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.

Since there’s a significant difference between the backup of transactional data and image data, they should be placed in different databases. The imagedb database is where you would place the images and large text descriptions, as shown in the MySQL Workbench ERD:

imagedb ERD

The imagedb ERD splits the foreign key references back to the system_user table, which contains the individual user credentials. The system_user table serves as the Access Control List (ACL) for the application.

Until I get a chance to write the code for this model, you can refer to the generic PHP/MySQL solution from several years back (its code source was last tested with PHP 5.3). As always, I hope this helps.

Written by maclochlainn

February 23rd, 2014 at 2:02 am

Oracle 12c & PHP

with one comment

This answers “How you connect PHP programs to an Oracle 12c multitenant database. This shows you how to connect your PHP programs to a user-defined Container Database (CDB) and Pluggable Database (PDB). It presupposes you know how to provision a PDB, and configure your Oracle listener.ora and tnsnames.ora files.

CDB Connection:

This assumes you already created a user-defined c##plsql CDB user, and granted an appropriate role or set of privileges to the user. Assuming the demonstration database Oracle TNS Service Name of orcl, you would test your connection with this script:

PDB Connection:

This assumes you already created a user-defined videodb PDB, and video user in the PDB, and granted an appropriate role or set of privileges to the video user. Assuming the user-defined videodb PDB uses an Oracle TNS Service Name of videodb, you would test your connection with this script:

Line 3 above uses the TNS Service Name from the tnsnames.ora file, which is also the SID Name from the listener.ora file after the slash that follows the localhost. That’s the only trick you should need.

You should note that because the tnsnames.ora file uses a video service name, the connection from the command line differs:

sqlplus video@video/video

Hope this helps those trying to sort it out.

Written by maclochlainn

August 31st, 2013 at 12:46 pm

Posted in Oracle,Oracle 12c,PHP,TNS

Tagged with , , ,

PHP PL/SQL Function Return

with 5 comments

Somebody wanted an example of how to capture the return value of a PL/SQL function in a PHP script running against the Oracle Database. The trick is embedding the call inside an anonymous block program unit, like a prior example that I posted on handling an OUT mode variable in a PL/SQL Procedure.

Here’s the sample PHP code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "localhost/orcl");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Initialize incoming message whether or not parameter sent.
    $msg_in = (isset($_GET['msg'])) ? $_GET['msg'] : "Cat got your keyboard?";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN :b := echo(:a); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement, you MUST size OUT only variables.
    oci_bind_by_name($stmt, ":a", $msg_in);
    oci_bind_by_name($stmt, ":b", $msg_out, 80, SQLT_CHR);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print $msg_out;
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
 
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

As noted by Chris, you should size input parameters too. It could be qualified as a best practice when code re-executes with different values without rebinding.

It depends on this echo function:

1
2
3
4
5
6
CREATE OR REPLACE FUNCTION echo
( message IN     VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  RETURN 'Message ['||message||'] received.';
END;
/

Hope that this is what they needed, or you’re looking for as an answer to a search.

Written by maclochlainn

May 28th, 2013 at 12:36 pm

PHP DB Connection Class

without comments

PHP namespaces are new to PHP 5.3 (2012), but PHP class files have been around since PHP 5 was introduced. However, a couple students had problems creating working code from the many fragments published else where. Here’s my attempt to qualify it in a single post, running Zend Server Community Edition 6 and Oracle Database 11g.

The first thing you need to understand is a namespace. Namespaces exist to disambiguate (tell the difference between) class files that share the same name. After all, there are only so many obvious things to call class files. ;-) You can put classes, interfaces, functions, and constants in namespaces.

Let’s say you qualify your namespace as:

namespace Oracle\Db;

You would make that the first thing in a PHP file, and shouldn’t include any HTML. You would then use a require(), require_once(), include(), or include_once() to add the class to a PHP file that uses the namespace qualified file. Then, you would construct a new instance of your PHP class. Together, these two steps would look like this:

  require_once('Db.php');
  $db = new \Oracle\Db\Db("Test Example","Author");

Notice the back slash in front of the Oracle namespace, and then you provide the namespace qualified file name (minus the file extension) and the class name. Since the namespace qualified file name and class name are the same, you see the double Db.

Here is a basic (starter) Oracle database connection class file, which you should store as Db.php in the Apache’s htdocs\Oracle directory:

<?php
/* Declare a namespace, available from PHP 5.3 forward. */
namespace Oracle\Db;
 
/* Create a Database Connection class. */
class Db {
 
  /* Declare class variables. */
  protected $conn = null;
  protected $stmt = null;
  protected $prefetch = 100;
 
  /* Declare the default construction function. */
  function __construct($module, $cid) {
 
    // Construct a connection and suppress errors and warnings.    
    $this->conn = @oci_connect(SCHEMA, PASSWD, TNS_ID, CHARSET);
 
    // Check for a connection, and process the work.
    if (!$this->conn) {
      // Assign Oracle error message.
      $msg = oci_error();
 
      /* The \ preceding Exception is necessary because of the
         introduction of namespaces in PHP 5.3. Without it, the
         program would attempt to call \Oracle\Exception rather
         than our little runtime example. */
      throw new \Exception('Cannot connect to database: '.$msg['message']);
    }
 
    /* Set Oracle Call Interface parameters.
     * =========================================================
     *  - The oci_set_client_info() function replaces a call
     *    to the DBMS_APPLICATION_INFO package, and much more
     *    efficient.
     *  - The oci_set_module_name() function allows you to 
     *    register the function name that calls the Db class.
     *  - The oci_set_client_identifier() function and you 
     *    use it with DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE,
     *    which can be enabled with a call to the 
     *    DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE.     
     * =========================================================
     */
    oci_set_client_info($this->conn, "Administrator");
    oci_set_module_name($this->conn, $module);
    oci_set_client_identifier($this->conn, $cid);
  }
 
  /* Declare execute function. */  
  public function execute($sql, $action, $bindvars = array()) {
 
    // Parse statement.
    $this->stmt = oci_parse($this->conn, $sql);
 
    // Check for a prefetch value greater than zero.
    if ($this->prefetch >= 0) {
      oci_set_prefetch($this->stmt, $this->prefetch);
    }
 
    // Read the list of bind variables and bind them.
    foreach ($bindvars as $bv) {
      oci_bind_by_name($this->stmt, $bv[0], $bv[1], $bv[2]);
    }
 
    // Set the action name for Oracle tracing and execute statement.
    oci_set_action($this->conn, $action);
 
    // Set to auto commit.
    oci_execute($this->stmt);
  }
 
  /* Declare function that fetches all. */
  public function execFetchAll($sql, $action, $bindvars = array()) {
    $this->execute($sql, $action, $bindvars);
    oci_fetch_all($this->stmt, $res, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
 
    // Free statement resources.
    $this->stmt = null;
    return($res);
  }
 
  /* Declare the default destructor function. */
  function __destruct() {
    if ($this->stmt)
      oci_free_statement($this->stmt);
    if ($this->conn)
      oci_close($this->conn);
  }
}
?>

Here is a credential file for Oracle, where the network SID is orcl (change orcl to xe when using the Oracle Express Edition):

1
2
3
4
5
6
7
<?php
  // Connection variables.
  define('SCHEMA',"student");
  define('PASSWD',"student");
  define('TNS_ID',"localhost/orcl");
  define('CHARSET',"AL32UTF8");
?>

If you do not know your the character set of your database, you can find it by logging in as the SYSTEM user, and running this query:

SELECT VALUE$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

Here’s the test program for the database connection class, save it as TestDb.php in your Apache’s htdocs\Oracle directory:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?php
  // Require once the namespace identified class and credentials files.
  require_once('Db.php');
  require_once('credentials.php');
 
   // Process the input parameter, which REALLY should be through a $_POST argument.
  (isset($_GET['last_name'])) ? $input = $_GET['last_name'] : $input = '';
 
  /* Establish new connection.
   * ======================================================
   *  The namespace (PHP 5.3) is set in Db.php as follows:
   *    namespace Oracle\Db;
   *
   *  The namespace syntax needs to qualify the following
   *  when you call it:
   *  - A \ (back slash) before the namespace.
   *  - The file name but not the file extension.
   *  - The class name from the Db.php file.
   */
  $db = new \Oracle\Db\Db("Test Example","Author");
 
  // Assign query.
  $sql = "SELECT * FROM contact c WHERE c.last_name = :bv";
 
  // Assign fetch to a result array.
  $result = $db->execFetchAll($sql, "Query Example", array(array(":bv", $input, -1)));
 
  // Open table and provide headers.
  print "<table border='1'>\n";
  print "<tr><th>First Name</th><th>Last Name</th></tr>\n";
 
  // Iterate through the rows.
  foreach ($result as $row) {
    $fname = htmlspecialchars($row['FIRST_NAME'], ENT_NOQUOTES, 'UTF-8');
    $lname = htmlspecialchars($row['LAST_NAME'], ENT_NOQUOTES, 'UTF-8');
    print "<tr><td>$fname</td><td>$lname</td></tr>\n";
  }
 
  // Close the table.
  print "</table>";
?>

If you get the call to the namespace wrong, you’ll get a strange set of errors. Just make sure you understand the differences between declaring a namespace and calling a namespace.

You test the database connection class with the following URL on your localhost (substitute a server name if it’s not a development environment), provided you’ve created a table contact with a row where the last_name equals 'Sweeney':

http://localhost/Oracle/TestDb.php?last_name=Sweeney

The following creates and seeds the contact table:

CREATE TABLE contact
( contact_id  NUMBER
, first_name  VARCHAR2(10)
, last_name   VARCHAR2(10));
INSERT INTO contact VALUES (1,'Meghan','Sweeney');
INSERT INTO contact VALUES (2,'Matthew','Sweeney');
INSERT INTO contact VALUES (3,'Ian','Sweeney');

Written by maclochlainn

May 23rd, 2013 at 11:25 pm

Posted in OPAL,Oracle,Oracle 11g,PHP

Tagged with ,

Zend 6 & Timezones

with one comment

Just went through all my PHP testing against a fresh instance of Oracle with Zend Server Community Edition 6, and found these warnings, guess that’s pretty clean for the Oracle part of the installation. I didn’t notice it before because generally I do most of my PHP development against a MySQL database. I should have been configuring the php.ini file routinely, as qualified in this PHP forum discussion.

Warning: oci_set_client_info(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 47
Warning: oci_set_module_name(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 48
Warning: oci_set_action(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 69

Turns out Zend 6 doesn’t automatically set the [Date] elements in the php.ini file, which is required for the oci_set_client_info(), oci_set_module_name(), and oci_set_action() functions of the OCI. You can find the php.ini file in the C:\Program Files (x86)\Zend\ZendServer\etc folder on Windows:

[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
;date.timezone =
 
; http://php.net/date.default-latitude
;date.default_latitude = 31.7667
 
; http://php.net/date.default-longitude
;date.default_longitude = 35.2333
 
; http://php.net/date.sunrise-zenith
;date.sunrise_zenith = 90.583333
 
; http://php.net/date.sunset-zenith
;date.sunset_zenith = 90.583333

You can find the values for date.timezone here. Update the date.timezone as follows:

date.timezone = America/Denver

Then, reboot the Zend Server, and it fixes the warning messages.

Written by maclochlainn

May 23rd, 2013 at 11:21 am

Embedding Gists

with one comment

Several years ago, I decided hosting my own blogs made sense because WordPress didn’t allow me to leverage plug-ins to format code blocks with GeSHi formatting. That was fine to a point. Although, the GeSHi formatting plug-in had some limits that I didn’t like and didn’t want to take the time to fix. The future was moving reusable code artifacts to a GitHub Gist repository.

I created this https://gist.github.com/maclochlainn Gist repository for the blog and implemented in this Common Lookup post from yesterday. As time allows, I’ll start shifting the reusable artifacts to the Gist repository from my blog. The change will be virtually unnoticeable because I’m leveraging a sweet piece of JQuery written by my son Joseph that lets you embed Gists in a web page.

There was just a little housekeeping to get Joseph’s JQuery to work smoothly with my WP-Syntax (contributed by Ryan McGeary – kudos for great work). More or less, beyond Jospeh’s instructions, I needed to add these into the WordPress theme’s style.css file when it was a relative reference. The JSON no longer requires this CSS modification, and it’s here in case somebody wants to do something different on their own.

/* Added for Gist code highlighting */
.gist{color:#000; font-size:13px}
.gist .gist-file{border:1px solid #dedede;font-family:"Menlo", Consolas, "Liberation Mono", Courier, monospace;margin-bottom:1em}
.gist .gist-file .gist-data{overflow:auto;word-wrap:normal;background-color:#f8f8ff;border-bottom:1px solid #ddd;font-size:100%}
.gist .gist-file .gist-data .line_data{padding:.5em !important}
.gist .gist-file .gist-data pre{font-family:Consolas, "Liberation Mono", Courier, monospace;background:transparent !important;border:none !important;margin:0 !important;padding:0 !important}
.gist .gist-file .gist-data .gist-highlight{background:transparent !important}
.gist .gist-file .gist-data .line_numbers{background-color:#ececec;color:#aaa;border-right:1px solid #ddd;text-align:right;padding:.5em}
.gist .gist-file .gist-data .line_numbers span{clear:right;display:block}
.gist .gist-file .gist-data .gist-line-numbers span{clear:right;display:block}
.gist-syntax{background:#ffffff}
.gist-syntax .c{color:#999988;font-style:italic}
.gist-syntax .err{color:#a61717;background-color:#e3d2d2}
.gist-syntax .k{color:#000000;font-weight:bold}
.gist-syntax .o{color:#000000;font-weight:bold}
.gist-syntax .cm{color:#999988;font-style:italic}
.gist-syntax .cp{color:#999999;font-weight:bold}
.gist-syntax .c1{color:#999988;font-style:italic}
.gist-syntax .cs{color:#999999;font-weight:bold;font-style:italic}
.gist-syntax .gd{color:#000000;background-color:#ffdddd}
.gist-syntax .gd .x{color:#000000;background-color:#ffaaaa}
.gist-syntax .ge{color:#000000;font-style:italic}
.gist-syntax .gr{color:#aa0000}
.gist-syntax .gh{color:#999999}
.gist-syntax .gi{color:#000000;background-color:#ddffdd}
.gist-syntax .gi .x{color:#000000;background-color:#aaffaa}
.gist-syntax .go{color:#888888}
.gist-syntax .gp{color:#555555}
.gist-syntax .gs{font-weight:bold}
.gist-syntax .gu{color:#aaaaaa}
.gist-syntax .gt{color:#aa0000}
.gist-syntax .kc{color:#000000;font-weight:bold}
.gist-syntax .kd{color:#000000;font-weight:bold}
.gist-syntax .kp{color:#000000;font-weight:bold}
.gist-syntax .kr{color:#000000;font-weight:bold}
.gist-syntax .kt{color:#445588;font-weight:bold}
.gist-syntax .m{color:#009999}
.gist-syntax .s{color:#d14}
.gist-syntax .na{color:#008080}
.gist-syntax .nb{color:#0086B3}
.gist-syntax .nc{color:#445588;font-weight:bold}
.gist-syntax .no{color:#008080}
.gist-syntax .ni{color:#800080}
.gist-syntax .ne{color:#990000;font-weight:bold}
.gist-syntax .nf{color:#990000;font-weight:bold}
.gist-syntax .nn{color:#555555}
.gist-syntax .nt{color:#000080}
.gist-syntax .nv{color:#008080}
.gist-syntax .ow{color:#000000;font-weight:bold}
.gist-syntax .w{color:#bbbbbb}
.gist-syntax .mf{color:#009999}
.gist-syntax .mh{color:#009999}
.gist-syntax .mi{color:#009999}
.gist-syntax .mo{color:#009999}
.gist-syntax .sb{color:#d14}
.gist-syntax .sc{color:#d14}
.gist-syntax .sd{color:#d14}
.gist-syntax .s2{color:#d14}
.gist-syntax .se{color:#d14}
.gist-syntax .sh{color:#d14}
.gist-syntax .si{color:#d14}
.gist-syntax .sx{color:#d14}
.gist-syntax .sr{color:#009926}
.gist-syntax .s1{color:#d14}
.gist-syntax .ss{color:#990073}
.gist-syntax .bp{color:#999999}
.gist-syntax .vc{color:#008080}
.gist-syntax .vg{color:#008080}
.gist-syntax .vi{color:#008080}
.gist-syntax .il{color:#009999}
/* End gist highlighting */
 
/* To resolve Geshi plug-in conflict. */
td.line-numbers {background:rgb(236,236,236)!important; border-right: 1px solid rgb(170,170,170)!important;text-align:right!important;padding:6px!important;color:rgb(170,170,170)!important;}
td.line-data {padding:6px!important;}

Also, Joseph and I discovered that there was a problem with changing style sheets on the github.com site. Joseph added the following but may not update his blog right away, so here’s the JavaScript for your WordPress header:

After completing this, I noticed that there’s a WordPress Plugin for embedding gists. Naturally, it’s probably simple to use it but I like the control and performance of this solution. I hope this make the pages load a bit faster, and readers use code that they’d like to test.

Written by maclochlainn

February 2nd, 2013 at 2:54 pm

Common Lookup Tables 2

with 2 comments

Last October I posted an example and description of a common_lookup table. It was intended to show how common_lookup tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.

This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.

Let’s start with data stored in join between two tables – the member and contact tables. The internal lookup uses the customers name from the contact table to find the membership account information in the member table.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT   m.account_number
,        m.member_type         -- A fk to common_lookup table.
,        m.credit_card_number
,        m.credit_card_type    -- A fk to common_lookup table.
,        c.first_name
,        c.middle_name
,        c.last_name
,        c.contact_type        -- A fk to common_lookup table.
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id
WHERE    c.first_name = 'Harry'
AND      c.middle_name = 'James'
AND      c.last_name = 'Potter'\G

It returns the results on the left, while a set of joins against the common_lookup table returns the results on the right (both use the \G in SQL Monitor to display the data vertically).

CommonLookupQueryResults01CommonLookupQueryResults01b

The member_type, credit_card_type, and contact_type columns in the data set on the left hold foreign key values. They’re copies of values found in the primary key column of the common_lookup table. You have the option of using these values to connect the data through a join or through function calls. A join requires three copies of the common_lookup table and yields the data displayed on the right above. The query to get the meaningful business information from the common_lookup table is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT   m.account_number
,        cl1.common_lookup_meaning
,        m.credit_card_number
,        cl2.common_lookup_meaning
,        c.first_name
,        c.middle_name
,        c.last_name
,        cl3.common_lookup_meaning
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN common_lookup cl1
ON       cl1.common_lookup_id = m.member_type INNER JOIN common_lookup cl2
ON       cl2.common_lookup_id = m.credit_card_type INNER JOIN common_lookup cl3
ON       cl3.common_lookup_id = c.contact_type
WHERE    c.first_name = 'Harry'
AND      c.middle_name = 'James'
AND      c.last_name = 'Potter'\G

The data returned from any query is symmetrical, which means all columns return the same number of rows. The results of the preceding query are the business results. Although, they’re not what you’d want to display in a web form that presents the ability to change values, like the member, credit card, or contact types. You need to get that information by using the foreign key as a parameter to a function call, and in this case three function calls. One for each of the foreign keys from the original query results. The result is an asymmetric collection of data would look like the following conceptually with Lily Luna Potter as the customer (note the green row is the symmetrical return set from the preceding query):

AsymmetricalQueryData

The f(x) represents a generic function call where the x substitutes for the foreign key value as a lookup key to the primary key value of the common_lookup table. The function in this case is a query that returns a two column list. One column holds the primary key value of the (common_lookup_id) and the other holds the customer facing description (from the common_lookup_meaning column).

These steps describe the process:

  1. Use the natural key (the user name) to find the data in the contact table.
  2. Use the member_id foreign key column in the contact table to link to the same name column primary key in the member table by joining the two rows.
  3. Use the foreign keys in the new row (combining columns from the contact and member tables) as call parameters to a PHP function that returns all possible foreign key values and their business descriptions in a web form.

The vertical choices displayed above map to OPTION tag elements of an HTML SELECT tag. The blue highlighted value contains an instruction, Select Type, in the display of an HTML OPTION tag, and it holds a null as the value of the VALUE attribute for the OPTION tag. The other displayed rows are the possible values. The green highlighted value is the currently selected value and the yellow highlighted values are alternate possibilities for an end-user to select. The logic for that process is in the PHP get_common_lookup function below.

Having discussed the structure of the data and problem, we’ll present the result in a dynamically constructed web form below. The display form shows a member account with customer information.

CommonLookupQueryResults02

You should note that the primary and foreign keys aren’t displayed because they’re irrelevant to the business process. Primary and foreign keys only serve to support relationships when we use surrogate keys as the primary key of a table. Only the meaningful information from the common_lookup table are displayed in the preceding form. Behind the web form, the primary and foreign key values are critical to maintaining anomaly free table data.

Each of the drop downs contains the full selection of possibilities from the common_lookup table, and an end-user could choose a new value by clicking on any of the drop down fields. For example, the following displays the selection of a type of credit card:

CommonLookupQueryResults03

The user can click on the drop down, and then navigate from the selection to a new selection. Assuming we don’t change anything, submitting the form should transmit the foreign key column values. The following shows that’s exactly what it does:

CommonLookupQueryResults04

As you can see from the screen shot it works easily. Below is the PHP code for a MySQL credentials file and the get_lookup function. The function lets you find an existing value or returns a set of unique values for you to choose from.

You should rename the following credentials.php file as MySQLCredentials.inc for it to work in the file below it.

Assuming you’ve implemented the credentials.php file as the MySQLCredentials.inc file, you can now implement the following file. The get_common_lookup function returns a <SELECT> tag with a list embedded of <OPTION> tags with values; one <OPTION> tag is selected when the foreign key matches a valid primary key value in the common_lookup table; and no <OPTION> tag is selected when the foreign key doesn’t match a a valid primary key value in the common_lookup table. The last possibility means a user must choose a new valid value for the foreign key column when the foreign key column is constrained as a mandatory or not null column.

The code for the web form is a bit more complex, as shown below. It contains three separate calls to the modified get_common_lookup function (on lines 104, 111, and 126). Each call to the get_common_lookup function selects the list of possible values and highlights the value associated with the foreign key value.

Here’s the web form code. You should note that it only returns a single row of data from the query by using a natural key from the contact table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
<html>
<header>
<title>Select Option Sample</title>
<style type="text/css">
  /* Class tag element styles. */
  .box {border:1px solid;padding:0px;width:392px;background-color:silver;}
  .bottomBox {border-left:1px solid;border-right:1px solid;border-bottom:1px solid;padding:5px;width:380px;background-color:silver;}
  .middleBox {border:1px solid;padding:5px;width:380px;background-color:silver;}
  .topBox {border-left:1px solid;border-right:1px solid;border-top:1px solid;padding:5px;width:380px;background-color:silver;}
  .button {margin:5px;background-color:lightblue;font-weight:bold;align:right;}
  .clear {clear:left;}
  .dropDown {min-width:50px;display:block;float:left;text-align:left;color:black;}
  .formDownLabel {width:90px;display:block;float:left;margin:5px;text-align:right;vertical-align:bottom;color:black;}
  .formAcrossLabel {width:80px;display:block;float:left;padding-bottom:0px;margin:5px 5px 0px;text-align:left;vertical-align:bottom;font-style:italic;font-size:90%;color:black;}
  .formInput {min-width:150px;margin:5px;text-align:left;}
  .formShortInput {width:80px;margin:5px;text-align:left;}
  .title1 {margin-left:0px;font-weight:bold;font-style:italic;font-size:125%;color:black;}
  .title2 {margin-left:5px;font-weight:bold;font-style:italic;font-size:105%;color:black;}
</style>
</header>
<body>
<?php
 
  // Include libraries.
  include_once("MySQLCredentials.inc");
  include_once("library.inc");
 
  // Define a HTML page string.
  $out = '';
 
  // Declare input variables.
  $first_name = (isset($_GET['first_name'])) ? $_GET['first_name'] : $first_name = "Harry";
  $middle_name = (isset($_GET['middle_name'])) ? $_GET['middle_name'] : $middle_name = "James";
  $last_name = (isset($_GET['last_name'])) ? $_GET['last_name'] : $last_name = "Potter";
 
  // Declare output variables.
  $member_account_number = null;
  $credit_card_number = null;
 
  // Declare lookup input and output (initialized as nulls to suppress warnings) variables.  
  $member_table = 'member';
  $member_type = 'member_type';
  $member_type_id = null;
  $credit_card_type = 'credit_card_type';
  $credit_card_type_id = null;
  $contact_table = 'contact';
  $contact_type = 'contact_type';
  $contact_type_id = null;
 
  // Assign credentials to connection.
  $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE);
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
    // Initial statement.
    $stmt = $mysqli->stmt_init();
 
    // Declare a static query.
    $sql = "SELECT   m.account_number\n"
         . ",        m.member_type\n"
         . ",        m.credit_card_number\n"
         . ",        m.credit_card_type\n"
         . ",        c.first_name\n"
         . ",        c.middle_name\n"
         . ",        c.last_name\n"
         . ",        c.contact_type\n"
         . "FROM     member m INNER JOIN contact c\n"
         . "ON       m.member_id = c.member_id\n"
         . "WHERE    c.first_name = ?\n"
         . "AND      c.middle_name = ?\n"
         . "AND      c.last_name = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param("sss",$first_name,$middle_name,$last_name); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }
    else {   
      // This query only returns one row, and an empty block follows the while logic.
      $stmt->bind_result($member_account_number, $member_type_id, $credit_card_number, $credit_card_type_id, $first_name, $middle_name, $last_name, $contact_type_id);
      while ($stmt->fetch()) {}
    }
  }
 
  // Print the query form.
  $out .= '<form method="post" name="myForm" action="submitItemType.php">';
  $out .= '<label class="title1">Membership Account Information</label><br />';
  $out .= '<div class="box">';
  $out .= '<div class="topBox">';
  $out .= '<label class="formDownLabel">Account #</label><input class="formInput" type="text" value="'.$member_account_number.'" />';
  $out .= '<select name="member_type" size="1" onChange="change(this.form.member_type)">';
 
  // Get dynamic membership type lookup string fragment.
  $out .= get_common_lookup($member_table, $member_type, $member_type_id);
 
  $out .= '</select><br />';
  $out .= '<label class="formDownLabel">Credit Card #</label><input class="formInput" type="text" value="'.$credit_card_number.'" />';
  $out .= '<select name="credit_card_type" size="1" onChange="change(this.form.credit_card_type)">';
 
  // Get dynamic credit card type lookup string fragment.
  $out .= get_common_lookup($member_table, $credit_card_type, $credit_card_type_id);
 
  // Print the closing HTML table tag.
  $out .= '</select><br />';
  $out .= '</div>';
  $out .= '<div class="middleBox">';
  $out .= '<label class="formDownLabel">&nbsp;</label>';
  $out .= '<label class="formAcrossLabel">First Name</label><label class="formAcrossLabel">Middle Name</label><label class="formAcrossLabel">Last Name</label><br class="clear" />';
  $out .= '<label class="formDownLabel">Customer</label><input class="formShortInput" type="text" value="'.$first_name.'" />';
  $out .= '<input class="formShortInput" type="text" value="'.$middle_name.'" />';
  $out .= '<input class="formShortInput" type="text" value="'.$last_name.'" /><br />';
  $out .= '<label class="formDownLabel">Type</label>';
  $out .= '<select style="margin:5px" name="contact_type" size="1" onChange="change(this.form.contact_type)">';
 
  // Get dynamic membership type lookup string fragment.
  $out .= get_common_lookup($contact_table, $contact_type, $contact_type_id);
 
  $out .= '</select><br />';
  $out .= '</div>';
  $out .= '<div class="bottomBox">';
  $out .= '<input class="button" style="margin-left:300px" name="submit" type="submit" value="Submit">';
  $out .= '</div>';
  $out .= '</form>';
  $out .= '</body>';
  $out .= '</html>';
 
  print $out;
?>
</body>
</html>

The submit button simply runs another web page that prints the actual values for the drop down selections. Here’s the code to print that:

1
2
3
4
5
6
7
8
9
10
11
12
<html>
<head>
</head>
<body>
<?php
  // Print the surrogate key values for the common_lookup_id column.
  print "MEMBER_TYPE -> [".$_POST['member_type']."]<br />";
  print "CREDIT_CARD_TYPE -> [".$_POST['credit_card_type']."]<br />";
  print "CONTACT_TYPE -> [".$_POST['contact_type']."]<br />";
?>
</body>
</html>

I hope this helps those who are interested in sorting how to implement a common_lookup table.

Written by maclochlainn

February 1st, 2013 at 1:47 am