Archive for the ‘PHP’ tag
mysqli Strict Standards
Six years ago I wrote a common lookup post to illustrate the effectiveness of things used throughout your applications. Now, I’m updating my student image with a more complete solution to show how to avoid update anomalies.
In the prior post, I used a while loop in PHP, like the following:
do { ... } while($stmt->next_result()); |
Using PHP Version 7.3.8 and MySQL 8.0.16, that now raises the following error message:
Strict Standards: mysqli_stmt::next_result(): There is no next result set. Please, call mysqli_stmt_more_results()/mysqli_stmt::more_results() to check whether to call this function/method in /var/www/html/app/library.inc on line 81 |
You can see this type of error when you set the following parameters in your file during testing:
ini_set('display_errors',1); ini_set('display_startup_errors',1); error_reporting(E_ALL); |
You can read more about error handling at this web page. The new and strict compliance standard for mysqli managing rows is:
do { ... } while($stmt->more_result()); |
As always, I hope this helps those looking for an answer.
Deprecated mysqli Functions
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_parammysqli_bind_resultmysqli_client_encodingmysqli_fetchmysqli_param_countmysqli_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.
Oracle 12c & PHP
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.
PHP DB Connection Class
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'); |
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.
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.
Nice “how-to” install OPAL on Ubuntu 8 Server
I noticed that somebody posted instructions and some scripts to install the OPAL (Oracle, Perl/PHP, Apache, Linux) in the OTN forum. I haven’t had a chance to run though it yet. Thought I’d point you to it directly. If you want the forum, go here. He’d like feedback in the forum.
PHP code to read a PL/SQL reference cursor
The following demonstrates how to read a PL/SQL reference cursor in a PHP program. The reference cursor function is defined in the Pipelined Functions & PL/SQL Tables blog page. I’ve commented it to the hilt for those new to PHP.
The reference cursor maintains a separate connection to the database to access the reference cursor. You also use the oci_fetch_assoc() function to get the data. That strip_special_characters() function lets you format your call to the PL/SQL program and remove non-parsing line returns and tabs before running the oci_parse() function.
<?php // Return successful attempt to connect to the database. if ($c = @oci_connect("plsql","plsql","orcl")) { // Declare input variables. (isset($_GET['table'])) ? $table = (int) $_GET['table'] : $table = 'ITEM'; (isset($_GET['column'])) ? $column = (int) $_GET['column'] : $column = 'ITEM_TYPE'; // Declare a PL/SQL execution command. $stmt = "BEGIN :return_cursor := get_common_cursor(:table,:column); END;"; // Strip special characters to avoid ORA-06550 and PLS-00103 errors. $stmt = strip_special_characters($stmt); // Parse a query through the connection. $s = oci_parse($c,$stmt); // Declare a return cursor for the connection. $rc = oci_new_cursor($c); // Bind PHP variables to the OCI input or in mode variables. oci_bind_by_name($s,':table',$table); oci_bind_by_name($s,':column',$column); // Bind PHP variables to the OCI output or in/out mode variable. oci_bind_by_name($s,':return_cursor',$rc,-1,OCI_B_CURSOR); // Execute the PL/SQL statement & reference cursor. oci_execute($s); oci_execute($rc); // Print the table header with known labels. print '<table border="1" cellpadding="3" cellspacing="0">'; // Set dynamic labels control variable true. $label = true; // Read the contents of the reference cursor. while($row = oci_fetch_assoc($rc)) { // Declare header and data variables. $header = ""; $data = ""; // Read the reference cursor into a table. foreach ($row as $name => $column) { // Capture labels for the first row. if ($label) { $header .= '<td class="e">'.$name.'</td>'; $data .= '<td class="v">'.$column.'</td>'; } else $data .= '<td class=v>'.$column.'</td>'; } // Print the header row once. if ($label) { print '<tr>'.$header.'</tr>'; $label = !$label; } // Print the data rows. print '<tr>'.$data.'</tr>'; } // Print the HTML table close. print '</table>'; // Disconnect from database. oci_close($c); } else { // Assign the OCI error and format double and single quotes. $errorMessage = oci_error(); print htmlentities($errorMessage['message'])."<br />"; } // 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 character only strings. return $out; } ?> |
Subscribe via RSS