Archive for the ‘PHP’ Category
Embedding Gists
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.
Common Lookup Tables 2
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).
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):
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:
- Use the natural key (the user name) to find the data in the
contacttable. - Use the
member_idforeign key column in thecontacttable to link to the same name column primary key in themembertable by joining the two rows. - Use the foreign keys in the new row (combining columns from the
contactandmembertables) 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.
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:
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:
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"> </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.
Common Lookup Tables 1
My students wanted an example of how to use a lookup table in the database. I thought it would be a great idea to create a simple example like this one. You can read further on my second post about common lookup tables.
A lookup table is a generalization that holds lists of values that support end-user selections. The following example uses a combination of the common_lookup_table and common_lookup_column columns to identify sets of value for drop down lists. The end-user selects a value from the list to identify a unique row, and returns a common_lookup_id surrogate key value.
The sample code uses the table defined in the previous illustration. It uses a simple HTML drop down list, a PHP library.inc file, and an HTML display form. Below is the drop down selection set for a table and column value.
The drop down list code uses an insecure and trivial GET method to keep the example simple, as shown below:
<html>
<header>
<title>Select Option Sample</title>
<style type="text/css">
/* Class tag element styles. */
.label {min-width:200px;text-align:left;}
.title {font-weight:bold;font-style:italic;font-size:125%;}
</style>
</header>
<body>
<?php
// Include libraries.
include_once("library.inc");
// Declare input variables.
$table_name = (isset($_GET['table_name'])) ? $_GET['table_name'] : $table_name = "item";
$column_name = (isset($_GET['column_name'])) ? $_GET['column_name'] : $column_name = "item_type";
// Call function.
get_lookup($table_name, $column_name);
?>
</body>
</html> |
The library.inc file contains the logic to use a PHP prepared statement to read and render a SELECT HTML tag. It uses OPTION tags for all values in the drop down list. The values for the OPTION tag are the surrogate key values from the common_lookup_id column of the common_lookup table, and text elements are the descriptive values from the common_lookup_meaning column.
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 | <?php /* || Program Name: library.inc */ function get_lookup($table_name, $column_name) { // Assign credentials to connection. $mysqli = new mysqli("localhost", "student", "student", "studentdb"); // 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 cl.common_lookup_id\n" . ", cl.common_lookup_meaning\n" . "FROM common_lookup cl\n" . "WHERE common_lookup_table = ?\n" . "AND common_lookup_column = ?\n" . "ORDER BY 2"; // Prepare statement. if ($stmt->prepare($sql)) { $stmt->bind_param("ss",$table_name,$column_name); } // Loop through a result set until completed. do { // 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 { // Fetch a row for processing. $result = $stmt->get_result(); // Print the opening select tag. print '<form method="post" name="myForm" action="submitItemType.php">'; print '<div class="title">Select unique from list:</div>'; print '<select name="item_type" size="1" onChange="change(this.form.item_type)">'; print "<option class=label value='' selected>Select </option>"; // Read through the rows of the array. while( $row = $result->fetch_array(MYSQLI_NUM) ) { print "<option class=label value='".$row[0]."'>".$row[1]."</option>"; } } } while($stmt->next_result()); // Print the closing HTML table tag. print '</select>'; print '<input name="submit" type="submit" value="Submit">'; print '</form>'; // Release connection resource. $mysqli->close(); } } ?> |
The display form action calls the submitItemType.php program, which displays the value from the OPTION tag selected in the prior form. The code for the display is:
<html> <head> </head> <body> <?php print "ITEM_TYPE -> [".$_POST['item_type']."]"; ?> </body> </html> |
It generates:
Hope this helps illustrate the value of and mechanics of lookup tables. As mentioned above, there’s a newer post on how to leverage common_lookup tables with more working code.
PHP for MySQL Striped View
Back in May I explained how to create MySQL striped views with session variables. A couple folks wanted to know how to implement them through PHP programs. The trick is sharing the same connection between a call to the function before a query against the striped view.
I’ve updated the MySQL example beyond the Hobbit analogy from the prior post. It now uses the following database components:
- An
APPLICATION_USERtable - A striped
AUTHORIZED_USERview - A
FUNCTION_QUERYview to optimize function calls - A
SET_LOGINfunction - A
GET_LOGIN_ID function - A
GET_USER_IDfunction
The definition of the APPLICATION_USER table is:
CREATE TABLE application_user ( user_id int(10) unsigned PRIMARY KEY AUTO_INCREMENT , user_name varchar(20) NOT NULL , user_role varchar(20) NOT NULL , user_group_id int(10) unsigned NOT NULL , user_type int(10) unsigned NOT NULL , first_name varchar(20) , middle_name varchar(20) , last_name varchar(20) , created_by int(10) unsigned NOT NULL , creation_date datetime NOT NULL , last_updated_by int(10) unsigned NOT NULL , last_update_date datetime NOT NULL , CONSTRAINT natural_key UNIQUE (user_name) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; |
You should note that the natural key is a user-defined user name (mind you in reality it is often set by the application administrator). This guarantees that the authorize_cursor in the set_login function below always returns only one row.
The following seeds five rows in the APPLICATION_USER table:
INSERT INTO application_user VALUES ( null, 'potterhj', 'System Admin', 2, 1, 'Harry', 'James', 'Potter', 1, NOW(), 1, NOW()) ,( null, 'weasilyr', 'Guest', 1, 1, 'Ronald', null, 'Weasily', 1, NOW(), 1, NOW()) ,( null, 'longbottomn', 'Guest', 1, 1, 'Neville', null, 'Longbottom', 1, NOW(), 1, NOW()) ,( null, 'holmess', 'DBA', 3, 1, 'Sherlock', null, 'Holmes', 1, NOW(), 1, NOW()) ,( null, 'watsonj', 'DBA', 3, 1, 'John', 'H', 'Watson', 1, NOW(), 1, NOW()); |
Before creating the striped view, you should create the functions that set the session variables and query them. The set_login function sets two session variables when successful, which requires a user name that matches a valid value in the user_name column of the application_user table. The function returns an integer of 1 on success and 0 on failure. The set_login function code follows.
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 | CREATE FUNCTION set_login(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED BEGIN /* Declare a local variable to verify completion of the task: || ========================================================== || a. Default value is zero, which means false. || b. Non-default value is one, which means true. || ========================================================== */ DECLARE lv_success_flag INT UNSIGNED DEFAULT 0; /* Declare local variables to hold the return values from the cursor. */ DECLARE lv_login_id INT UNSIGNED; DECLARE lv_group_id INT UNSIGNED; /* Declare a condition variable for zero rows fetched, selected, or processed. */ DECLARE no_rows_fetched CONDITION FOR 1329; /* Declare a cursor to return an authorized user id. */ DECLARE authorize_cursor CURSOR FOR SELECT a.user_id , a.user_group_id FROM application_user a WHERE a.user_name = pv_login_name; /* Declare a handler for the cursor when it fails to return a row. */ DECLARE EXIT HANDLER FOR no_rows_fetched BEGIN /* The return statement when the function is aborted through an error. */ RETURN lv_success_flag; END; /* Check whether the input value is something other than a null value. */ IF pv_login_name IS NOT NULL THEN OPEN authorize_cursor; FETCH authorize_cursor INTO lv_login_id, lv_group_id; CLOSE authorize_cursor; /* Set the success flag. */ SET @sv_login_id := lv_login_id; SET @sv_group_id := lv_group_id; /* Check whether the session variables are set. */ IF NOT ISNULL(@sv_login_id) AND @sv_login_id > 0 AND NOT ISNULL(@sv_group_id) AND @sv_group_id > 0 THEN SET lv_success_flag := 1; END IF; END IF; /* Return the success flag. */ RETURN lv_success_flag; END; $$ |
The following GET_LOGIN_ID function returns the value from the @sv_login_id variable.
1 2 3 4 5 6 | CREATE FUNCTION get_login_id() RETURNS INT UNSIGNED BEGIN /* Return the success flag. */ RETURN @sv_login_id; END; $$ |
The following GET_GROUP_ID function returns the value from the @sv_group_id variable.
1 2 3 4 5 6 | CREATE FUNCTION get_group_id() RETURNS INT UNSIGNED BEGIN /* Return the success flag. */ RETURN @sv_group_id; END; $$ |
Lastly, you create the MySQL striped AUTHORIZED_USER like this one. It looks ineffective because it includes four function calls to the get_group_id() and one to the get_login_id().
CREATE VIEW authorized_user AS SELECT au.user_id , au.user_name , au.user_role , CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name FROM application_user au WHERE (au.user_group_id = 1 AND au.user_group_id = get_group_id() AND au.user_id = get_login_id()) OR get_group_id() = 2 OR (get_group_id() > 2 AND au.user_group_id = get_group_id()); |
The prior view’s query lets you see the logic for the three types of access. You can eliminate the multiple function calls by using an inline view, like the following in a SQL statement:
CREATE VIEW authorized_user AS SELECT au.user_id , au.user_name , au.user_role , CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name FROM application_user au CROSS JOIN (SELECT get_login_id() AS login_id , get_group_id() AS group_id) fq WHERE (au.user_group_id = 1 AND au.user_group_id = fq.group_id AND au.user_id = fq.login_id) OR fq.group_id = 2 OR (fq.group_id > 2 AND au.user_group_id = fq.group_id); |
Unfortunately, the preceding query raises the following exception if you attempt to put it in a view:
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause |
MySQL raises the error because a SELECT statement can’t contain a subquery in the FROM clause, according to the Create View MySQL Reference material.
The solution to the limitation of the CREATE VIEW syntax requires that you breakup the SQL statement into queries, and put them into separate views. The following example shows the function_query view holding the function calls and the authorized_user view cross joining the function_query view.
CREATE VIEW function_query AS SELECT get_login_id() AS login_id , get_group_id() AS group_id; CREATE VIEW authorized_user AS SELECT au.user_id , au.user_name , au.user_role , CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name FROM application_user au CROSS JOIN function_query fq WHERE (au.user_group_id = 1 AND au.user_group_id = fq.group_id AND au.user_id = fq.login_id) OR fq.group_id = 2 OR (fq.group_id > 2 AND au.user_group_id = fq.group_id); |
The following PHP program calls the SET_LOGIN function before querying the AUTHORIZED_USER view. It uses a $_GET global parameter to simplify testing the concept but you should always run parameters through the $_POST global parameter. The $_GET and $_REQUEST global parameters are security risks.
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 | <html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
// Process the input parameter, which should be through a $_POST argument.
(isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = '';
// Assign credentials to connection.
$mysqli = new mysqli("localhost", "student", "student", "studentdb");
// Check for connection error and print message.
if ($mysqli->connect_errno) {
print $mysqli->connect_error."<br />";
print "Connection not established ...<br />";
}
else {
// Declare a dynamic function call.
$query = "SELECT set_login(?)";
// Attempt preparing statement.
if (!$stmt = $mysqli->prepare($query)) {
// Print failure to resolve query message.
print $mysqli->error."<br />";
print "Failed to resolve query ...<br />";
}
else {
// Bind variable to SQL statement and execute it.
$stmt->bind_param("s", $input);
$stmt->execute();
$stmt->close();
}
// Declare a static query.
$query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_user au" ;
// Loop through a result set until completed.
do {
// Attempt query and exit with failure before processing.
if (!$stmt = $mysqli->query($query)) {
// Print failure to resolve query message.
print $mysqli->error."<br />";
print "Failed to resolve query ...<br />";
}
else {
// Print the opening HTML table tag.
print '<table><tr><th class="ID">ID</th><th class="Label">User Name</th><th class="Label">User Role</th><th class="Label">Full Name</th></tr>';
// Fetch a row for processing.
while( $row = $stmt->fetch_row() ) {
// Print the opening HTML row tag.
print "<tr>";
// Loop through the row's columns.
for ($i = 0;$i < $mysqli->field_count;$i++) {
// Handle column one differently.
if ($i == 0)
print '<td class="ID">'.$row[$i]."</td>";
else
print '<td class="Label">'.$row[$i]."</td>";
}
// Print the closing HTML row tag.
print "</tr>";
}
}
} while( $mysqli->next_result());
// Print the closing HTML table tag.
print "</table>";
// Release connection resource.
$mysqli->close(); }
?>
</script>
</body>
</html> |
You can call this through a browser with the following type of URL. You have two possible striped values, and they are any user’s unique user name.
http://localhost/stripedquery1.php?user_name=potterhj |
It returns one row when the user isn’t in a privileged group, all rows when the user is the root privileged group and all rows for a privilege group when not in the root privileged group. Naturally, you can extend this level of individual and group membership.
You can test this in the web page or directly in MySQL. The MySQL test doesn’t require image files and thereby loads faster, which is why I’ve opted to show it to you that way.
- Test for the root privilege group:
SELECT set_login('potterhj'); |
You get the full five rows:
+---------+-------------+--------------+----------------------+ | user_id | user_name | user_role | full_name | +---------+-------------+--------------+----------------------+ | 1 | potterhj | System Admin | Potter, Harry James | | 2 | weasilyr | Guest | Weasily, Ronald | | 3 | longbottomn | Guest | Longbottom, Neville | | 4 | holmess | DBA | Sherlock, Holmes | | 5 | watsonj | DBA | John, Watson H | +---------+-------------+--------------+----------------------+ |
- Test for an individual in a non-privileged group:
SELECT set_login('weasilyr'); |
You get the single user’s row:
+---------+-----------+-----------+------------------+ | user_id | user_name | user_role | full_name | +---------+-----------+-----------+------------------+ | 2 | weasilyr | Guest | Weasily, Ronald | +---------+-----------+-----------+------------------+ |
- Test for a non-root privileged group:
SELECT set_login('holmess'); |
You get the two rows that belong to the non-root privileged group:
+---------+-----------+-----------+-------------------+ | user_id | user_name | user_role | full_name | +---------+-----------+-----------+-------------------+ | 4 | holmess | DBA | Holmes, Sherlock | | 5 | watsonj | DBA | Watson, John H | +---------+-----------+-----------+-------------------+ |
Naturally, it’s more effective to put these components into a function library. The trick to making this work in a session is to share the connection. The object approach to the MySQL Improved (mysqli) object holds the connection, and that’s whats passed in the following example files.
This is an optimistic setter function. It forks (starts) the MySQL SQL/PSM set_login function but it doesn’t wait see if it ran successfully. That’s the nature of optimistic programming solutions, and the principal problem with them.
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 | function set_login($mysqli, $user_name) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT set_login(?)"; // Attempt preparing statement. if (!$stmt = $mysqli->prepare($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Bind variable to SQL statement and execute it. $stmt->bind_param("s", $user_name); $stmt->execute(); $stmt->close(); $return = true; } // Return the string. return $return; } |
An optimistic setter function fails to synchronize behaviors between the PHP and MySQL coding levels. It should be rewritten to fork the MySQL SQL/PSM set_login function and evaluate it’s successful or unsuccessful completion, which makes it a pessimistic function.
There are two ways to solve this problem. One can write a wrapper that accesses the get_login_id stored function to confirm the session variable is set, and the other handles the return value from the native set_login_id stored function. The former requires knowledge of the internal workings of the database model, while the latter does not. That means the first is more tightly coupled than the latter.
The following set_login PHP function is rewritten to be pessimistic but dependent on a supplemental call to another get_login PHP function, which calls the get_login_id stored function in the MySQL Server:
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 | function set_login($mysqli, $user_name) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT set_login(?)"; // Attempt preparing statement. if (!$stmt = $mysqli->prepare($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Bind variable to SQL statement and execute it. $stmt->bind_param("s", $user_name); $stmt->execute(); $stmt->close(); // True only when query returns a row. if (get_login($mysqli)) { $return = true; } } // Return the string. return $return; } |
A more effective solution reads the return value from the set_login function, like this:
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 | function set_login($mysqli, $user_name) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT set_login(?)"; // Attempt preparing statement. if (!$stmt = $mysqli->prepare($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Bind variable to SQL statement and execute it. $stmt->bind_param("s", $user_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 { // Fetch a row for processing. $result = $stmt->get_result(); $row = $result->fetch_array(MYSQLI_NUM); } // Close the statement cursor. $stmt->close(); // True only when query returns a row. if (!is_null($row[0]) && ($row[0] > 0)) { $return = true; } } // Return the string. return $return; } |
This is the getter function:
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 | function get_login($mysqli) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT get_login_id()"; // Attempt preparing statement. if (!$stmt = $mysqli->query($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Fetch a SQL statement. $row = $stmt->fetch_row(); // Close the statement. $stmt->close(); // True only when query returns a row. if (!is_null($row[0]) && ($row[0] > 0)) { $return = true; } } // Return the string. return $return; } |
The get_authorized_user PHP function gets and displays the table result from the authorized_user striped view:
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 | function get_authorized_user($mysqli) { // Define return string. $out = ''; // Declare a static query. $query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_user au" ; // Loop through a result set until completed. do { // Attempt query and exit with failure before processing. if (!$stmt = $mysqli->query($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Print the opening HTML table tag. $out .= '<table><tr><th class="ID">ID</th><th class="Label">User Name</th>' . '<th class="Label">User Role</th><th class="Label">Full Name</th></tr>'; // Fetch a row for processing. while( $row = $stmt->fetch_row() ) { // Print the opening HTML row tag. $out .= "<tr>"; // Loop through the row's columns. for ($i = 0;$i < $mysqli->field_count;$i++) { // Handle column one differently. if ($i == 0) $out .= '<td class="ID">'.$row[$i]."</td>"; else $out .= '<td class="Label">'.$row[$i]."</td>"; } // Print the closing HTML row tag. $out .= "</tr>"; } } } while( $mysqli->next_result()); // Print the closing HTML table tag. $out .= "</table>"; // Return an HTML table of the results. return $out; } |
This is the modified program using the functions:
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 | <style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
// Include the credentials file if omitted.
include_once("striping.inc");
// Process the input parameter, which should be through a $_POST argument.
(isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = '';
// Assign credentials to connection.
$mysqli = new mysqli("localhost", "student", "student", "studentdb");
// Check for connection error and print message.
if ($mysqli->connect_errno) {
print $mysqli->connect_error."<br />";
print "Connection not established ...<br />";
}
else {
// After setting the session variable, get the striped view result.
if (set_login($mysqli,$input)) {
// Print the authorized list.
print get_authorized_user($mysqli);
}
// Release connection resource.
$mysqli->close();
}
?>
</script>
</body>
</html> |
Hope this helps those implementing MySQL Striped tables.
Free Oracle PHP Book
Six years ago, I wrote Oracle Database 10g Express Edition PHP Web Programming for the release of the express edition. It was a lot of fun to write because I enjoy the PHP programming language, but unfortunately sales didn’t measure up too well. That’s probably because the population of PHP developers working with Oracle was small.
Today it seems there are more PHP developers working with Oracle 11g. While the population of PHP community for Oracle 11g is still smaller than for MySQL, it continues to grow year-over-year.
The FREE Underground PHP and Oracle Manual can help those converting PHP to run in the Oracle Call Interface, which is the replacement for MySQLi Interface. Chris Jones (an Oracle Open Source Product Manager) and Alison Holloway (an Oracle Senior Product Manager) write and maintain this book. It’s a great place to start if you’re migrating to Oracle Database 11g from MySQL.
PHP/MySQL Query
Somebody wanted an example of how to handle column values using PHP to query a MySQL database. While I thought there were enough examples out there, they couldn’t find one that was code complete.
Well, here’s one that works using a static query. If you want to use a prepared statement, check this earlier post.
<html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
// Assign credentials to connection.
$mysqli = new mysqli("localhost", "student", "student", "studentdb");
// Check for connection error and print message.
if ($mysqli->connect_errno) {
print $mysqli->connect_error."<br />";
print "Connection not established ...<br />";
}
else {
// Declare a static query.
$query = "SELECT au.system_user_id, au.system_user_name FROM system_user au" ;
// Loop through a result set until completed.
do {
// Attempt query and exit with failure before processing.
if (!$stmt = $mysqli->query($query)) {
// Print failure to resolve query message.
print $mysqli->error."<br />";
print "Failed to resolve query ...<br />";
}
else {
// Print the opening HTML table tag.
print '<table><tr><th class="ID">ID</th><th class="Label">User Role Name</th></tr>';
// Fetch a row for processing.
while( $row = $stmt->fetch_row() ) {
// Print the opening HTML row tag.
print "<tr>";
// Loop through the row's columns.
for ($i = 0;$i < $mysqli->field_count;$i++) {
// Handle column one differently.
if ($i == 0)
print '<td class="ID">'.$row[$i]."</td>";
else
print '<td class="Label">'.$row[$i]."</td>";
}
// Print the closing HTML row tag.
print "</tr>";
}
}
} while( $mysqli->next_result());
// Print the closing HTML table tag.
print "</table>";
// Release connection resource.
$mysqli->close(); }
?>
</script>
</body>
</html> |
It prints the following image:
While you shouldn’t embed CSS, I’ve done it to keep this as simple as possible. You can also use the procedural approach to the MySQLi library, like this:
<html>
<header>
<title>Static Query Procedural Sample</title>
<style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
// Assign credentials to connection.
$mysqli = mysqli_connect("localhost", "student", "student", "studentdb");
// Check for connection error and print message.
if (mysqli_connect_errno()) {
print mysqli_connect_error()."<br />";
print "Connection not established ...<br />";
}
else {
// Initialize a statement in the scope of the connection.
$stmt = mysqli_stmt_init($mysqli);
// Declare a static query.
$query = "SELECT au.system_user_id, au.system_user_name FROM system_user au" ;
// Loop through a result set until completed.
do {
// Attempt query and exit with failure before processing.
if (!$stmt = mysqli_query($mysqli,$query)) {
// Print failure to resolve query message.
print mysqli_error($stmt)."<br />";
print "Failed to resolve query ...<br />";
}
else {
// Print the opening HTML table tag.
print '<table><tr><th class="ID">ID</th><th class="Label">User Role Name</th></tr>';
// Fetch a row for processing.
while( $row = mysqli_fetch_row($stmt) ) {
// Print the opening HTML row tag.
print "<tr>";
// Loop through the row's columns.
for ($i = 0;$i < mysqli_field_count($mysqli);$i++) {
// Handle column one differently.
if ($i == 0)
print '<td class="ID">'.$row[$i]."</td>";
else
print '<td class="Label">'.$row[$i]."</td>";
}
// Print the closing HTML row tag.
print "</tr>";
}
}
} while( mysqli_next_result($mysqli));
// Print the closing HTML table tag.
print "</table>";
// Free system resources.
mysqli_stmt_free_result($stmt);
// Release connection resource.
mysqli_close($mysqli);
}
?>
</script>
</body>
</html> |
It produces the same output as the object oriented approach with one exception the title of the web page.
Hope this helps some folks.
Display Errors with Zend
Someone asked how to display errors in the HTML output when they test PHP scripts with the Zend Server CE. Here are the two screen shots that help you enable the display of error and warning messages in your HTML display.
- Click the Server Setup tab and then click the Error Handling and Logging choice.
- Click the On radio button for Display errors and then restart PHP at the bottom right corner.
Hope this helps people looking for the navigation.
Zend CE has a Worm?
After updating the AVGFree virus definitions, I was surprised to find that Zend CE (Community Edition) 4.0.6 had a reported worm in the JavaServer.exe file. There was greater surprise when Zend CE 5.3.9 (5.6.0-SP1) also had the same reported worm.
This is the message identifying the worm (click on it to see a full size image), and you can read about this particular worm on the Mcafee site or the AVG threat labs site:
If you check AVGFree’s page, the actual infection isn’t a stated variant, but it appears the heuristics are a bit aggressive.
File Name: C:\Program Files (x86)\Zend\ZendServer\bin\JavaServer.exe
Infection: Win32/DH.FF860061{00000000-00080000-00000000} |
Unless you have the full version of AVGFree or another security program to try and fix the file, you can only quarantine the file. Quarantine or removal disables Zend CE from working. It begs the question: “How does Zend release a core file with a worm?” or “Is AVGFree reporting a false positive?”
Update: AVGFree was providing a false positive. In addition to the checks by Zeev at Zend, I created a new test instance with Norton 360 and it likewise found no virus/worm in Zend’s JavaServer.exe file. Hopefully the post will prevent others from spending more than a Google search to sort it out.
Since I use AVGFree on all my Windows 7 VM test instances, it seemed logical to illustrate how to work around this current false positive and annoying quarantining of the core JavaServer.exe file from the Zend Server. There are two sets of tasks, the first requires removing the file from quarantine and the second eliminates future scans from quarantining the file again.
Remove the file from the Virus Vault
- Launch AVGFree and navigate to the History menu option and choose the Virus Vault option, as shown below.
- Click the Virus Vault option in the list of the History, which displays the following screen. Click the Infection row and then click the Restore button to remove the file from the virus vault.
- A confirmation dialog opens and you click the Yes button to proceed.
- The Infection row is gone When you’re returned to the History dialog. Click the Close button to complete this task.
Exclude the file from future scans
- Select the Tools menu option and choose the Advanced settings … option, as shown below.
- Click the Excluded files option in the list of the History, which displays the following screen. Click the Add button to select the file for exclusion. Click the Apply button to effect the change and the OK button to complete the change.
All I can say, one the AVGFree false positive was annoying and it’s dark at 3 a.m. and light the next day. 😉
Thanks to those who knew or surmised it was AVGFree’s heuristics and took the time to add a comment.
PHP leveraging PL/SQL
Somebody wanted another example of how to leverage a true/false condition from a PL/SQL stored function in PHP. The first key is that you write the function as if you were using it in SQL not PL/SQL. That means you return a NUMBER data type not a PL/SQL-only BOOLEAN data type.
Here’s the schema-level PL/SQL function:
CREATE OR REPLACE FUNCTION like_boolean ( a NUMBER, b NUMBER ) RETURN NUMBER IS /* Declare default false return value. */ lv_return_value NUMBER := 0; BEGIN /* Compare numbers and return true for a match. */ IF a = b THEN lv_return_value := 1; END IF; /* Return value. */ RETURN lv_return_value; END; / |
Here’s the PHP that leverages the PL/SQL in an if-statement on line #24:
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 | <?php // Capture local variables when provided. $thingOne = (isset($_GET['thingOne'])) ? $_GET['thingOne'] : 1; $thingTwo = (isset($_GET['thingTwo'])) ? $_GET['thingTwo'] : 1; // Open a connection. if(!$c = oci_connect("student","student","localhost/xe")) { die; } else { // Parse a statement. $s = oci_parse($c,"BEGIN :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo); END;"); // Bind input and output values to the statement. oci_bind_by_name($s,":returnValue",$returnValue); oci_bind_by_name($s,":thingOne",$thingOne); oci_bind_by_name($s,":thingTwo",$thingTwo); // Execute the statement. if (@oci_execute($s)) { // Print lead in string. print "[".$thingOne."] and [".$thingTwo."] "; if ($returnValue) print "are equal.<br />"; else print "aren't equal.<br />"; } // Clean up resources. oci_close($c); } ?> |
If you run into a parsing error, which is infrequent now. You can wrap the multiple row PL/SQL anonymous block call with this function. It strips tabs and line returns. Alternatively, you can put all the lines of PL/SQL on a single line.
// Strip special characters, like carriage or line returns and tabs. function strip_special_characters($str) { $out = ""; for ($i = 0;$i < strlen($str);$i++) if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) && (ord($str[$i]) != 13)) $out .= $str[$i]; // Return pre-parsed SQL statement. return $out; } |
If you run into a parsing problem on Oracle XE 10g, you can wrap the PL/SQL call like the following. Alternatively, you can place the entire anonymous PL/SQL block on a single line without embedded tabs or return keys..
10 11 12 13 | $s = oci_parse($c,strip_special_characters( "BEGIN :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo); END;")); |
Hope that answers the question and helps some folks.
PHP Database Authentication
A few years ago I wrote a couple articles showing how to use PHP to connect to an Oracle Database 10g Express Edition instance. They’re still there on Oracle’s Technical Network but the source files are missing. It appears that Oracle may have migrated the articles to a new server but failed to migrate the source files.
Don’t forget that you’ll need to Install the Zend Community Server. Then, you need to create an IDMGMT1, IDMGMT2, and IDMGMT3 users and run the create_identity_db2.sql or create_identity_db3.sql seeding script for both database validation models. You’ll find links to the original articles, the source code in zip files. The newer version with CSS is the IDMGMT3 code.
The CSS sign-on form looks like this:
The portal page looks like this:
The add new user page looks like this:
The source files are as follows:
- Database Authentication #1 – IdMgmt1.zip
- Database Authentication #2 – IdMgmt2.zip
- Database Authentication #3 – IdMgmt3.zip includes the CSS but works with the same write-up as Idmgmt2.
Hope this helps those who wanted the files.
Subscribe via RSS