Tag: PHP

Oracle – Collections and IN or LIKE Queries

I’ve been retrofitting a lot of PHP/SQL queries to use oci_bind_by_name recently. When using “IN” clauses, you can iterate through your array twice, but it’s an inefficient approach.

// Build the query string with a bunch of placeholders
$strQuery = "select Col1, Col2, Col3 from TableName where ColName IN (";
for($i=0; $i < count($array); $i++){
    if($i > 0){
        $strQuery = $strQuery . ", ";
    $strQuery = $strQuery . ":bindvar" . $i;
$strQuery = $strQuery . ")";
// Then bind each placeholder to something
for($i=0; $i < count($array); $i++){
    oci_bind_by_name($stmt, ":bindvar".$i, $array[$i]);

Building a table from the array data and using an Oracle collection object creates cleaner code and avoids a second iteration of the array:

$strQuery = "SELECT indexID, objName FROM table WHERE objName in (SELECT column_value FROM table(:myIds))";
$stmt = oci_parse($conn, $strQuery);

$coll = oci_new_collection($kpiprd_conn, 'ODCIVARCHAR2LIST','SYS');
foreach ($arrayValues as $strValue) {
oci_bind_by_name($stmt, ':myIds', $coll, -1, OCI_B_NTY);
oci_set_prefetch($stmt, 300);

A simple like clause is quite straight-forward

$strNameLikeString = "SomeName%";
$strQuery = "SELECT ds_dvrsty_set_nm from ds_dvrsty_set WHERE ds_dvrsty_set_nm LIKE :divsetnm ORDER BY ds_dvrsty_set_nm DESC fetch first 1 row only";

$stmt = oci_parse($connDB, $strQuery);
oci_bind_by_name($stmt, ":divsetnm", $strNameLikeString);
oci_set_prefetch($stmt, 300);

But what about an array of inputs essentially reproducing the LIKE ANY predicate in PostgreSQL? There’s not a direct equivalent in Oracle, and iterating through the array twice to build out a query WHERE (Field1 LIKE ‘Thing1%’ OR Field1 LIKE ‘Thing2%’ OR Field1 LIKE ‘Thing3%’) is undesirable. The with EXISTS allows me to create a LIKE ANY type query and only iterate through my array once to bind variables to placeholders using the same collection approach as was used with the IN clause.

$arrayLocs = array('ERIEPAXE%', 'HNCKOHXA%', 'LTRKARXK%');
$strQuery = "SELECT location_id, clli_code FROM network_location WHERE EXISTS (select 1 FROM TABLE(:likelocs) WHERE clli_code LIKE column_value)";
$stmt = oci_parse($connDB, $strQuery);

$coll = oci_new_collection($connDB, 'ODCIVARCHAR2LIST','SYS');
foreach ($arrayLocs as $strLocation) {
oci_bind_by_name($stmt, ':likelocs', $coll, -1, OCI_B_NTY);
print "<table>\n";
print "<tr><th>Loc ID</th><th>CLLI</th></tr>\n";
while ($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
    print "<tr><td>" . $row['LOCATION_ID'] . "</td><td>" . $row['CLLI_CODE'] . "</td></tr>\n";
print "</table>\n";

There are many different collection types in Oracle which can be used with oci_new_collection. A full list of the system collection types can be queried from the database.


Updating JQuery

We’ve got to upgrade some Javascript modules at work — JQuery, Bootstrap, etc. Problem is that changes between the versions mean there’s a lot of rewriting required before we can update. And we pull in these modules using a shared header file. While we could stage all of the changes and update the entire website at once … that means we’re all dedicated to updating our components & are delaying the update until we’re finished.

That’s not ideal — and has the potential to break a lot of things at once. I plan, instead, of putting a default version in the shared header file. And some mechanism to source in a newer version by setting a variable in the individual tool’s PHP code before the header is pulled in. So each tool within the site has a $strJQueryRev, $strBootstrapRev, etc variable. Then the shared header file looks for that variable — loads a newer version when requested or loads the currently used older version when no version is indicated.

if($strJQueryRev == "3.5.1"){
 echo "<script src=\"https://code.jquery.com/jquery-3.5.1.min.js\">\n";   
elseif($strJQueryRev == "3.1.1"){
 echo "<script src=\"https://code.jquery.com/jquery-3.1.1.min.js\">\n";   
 echo "<script src=\"https://code.jquery.com/jquery-2.2.4.min.js\">\n";        # Old, in use, version is default

Or even

echo "<script src=\"https://code.jquery.com/jquery-$strRevisionNumber.min.js\">

Each developer can add a version number to a single tool, test it, push it up through production using the newest modules. Move on to the next tool. The site still isn’t done until we’re all done, but we can slowly roll out the update as people are able to test their tools.

Listing Modules In Dynamically Linked Shared Object Libraries

We had to rebuild a server over the weekend — it’s a lot harder to get Apache and PHP set up when you don’t have root access to just install things from the yum repository. And, unlike the servers where I built httpd and php from source … we basically relayed requests to the Unix admin to have packages installed. One of the confusions during the whole process was that we didn’t know what to use as the module name for PHP to load in the httpd.conf file. The line from our old server (LoadModule php5_module /etc/httpd/modules/libphp5.so) produced an error that there was no such thing to load.

When a library fails to load with some error, I know to use ldd … but I didn’t know there was a way to list out the modules in a library. Fortunately, one of my coworkers had already run nm and listed out the modules — nm -D –defined-only sharedLibraryFile | grep module — and we were able to identify that the libphp5.so that we had wasn’t anything like the one on the old server. By listing the modules for each of the shared object libraries installed by the php package, we got the proper module name for httpd.conf

PHP Sub-Second Sleep

I needed to add a sleep to a PHP process, but I didn’t want to waste a whole second on each cycle. That’s usleep:


        $t = microtime(true);
        $micro = sprintf("%06d",($t - floor($t)) * 1000000);
        $d = new DateTime( date('Y-m-d H:i:s.'.$micro, $t) );

        print $d->format("Y-m-d H:i:s.u") . "\n";                                                                       

        $t = microtime(true);
        $micro = sprintf("%06d",($t - floor($t)) * 1000000);
        $d = new DateTime( date('Y-m-d H:i:s.'.$micro, $t) );

        print $d->format("Y-m-d H:i:s.u")  . "\n";                                                                      
        $t = microtime(true);
        $micro = sprintf("%06d",($t - floor($t)) * 1000000);
        $d = new DateTime( date('Y-m-d H:i:s.'.$micro, $t) );

        print $d->format("Y-m-d H:i:s.u")  . "\n";                                                                      

Run the script, and you’ll see sub-second sleeps.

[tempuser@564240601ac2 /]# php testSleep.php
2020-07-09 14:06:20.641449
2020-07-09 14:06:20.741952
2020-07-09 14:06:21.742347

Composer Hangs

I don’t use composer often, and it generally just works … so I don’t know much about it beyond “another package manager”. But every once in a while, it just hangs. Nothing happening, nothing instructive in strace. Fortunately, composer has several levels of verbosity on the output. While the default output is minimal and offers absolutely no clue that it’s doing something … adding -vvv is a nicely verbose output that lets me see that the package install isn’t actually hung. It’s just going to take a long time.

Apache — Switching to PHP-FPM

A few system updates ago, PHP fell over completely because of some multi-processing module. The quick fix was to change the multi-processing module and avoid having to figure out what changed and how to use php-fpm. Part of moving my VM’s to the new server, though, is cleaning up anything I’ve patched together as a quick fix. And, supposedly, php-fpm is a lot faster than the old-school Apache handler. Switching was a lot less involved than I had expected.

Install php-fpm:

dnf install php-fpm

Edit 00-mpm.conf

My quick fix was to switch to a non-default multi-processing module. That change is reverted to re-enable the ‘event’ module

vim /etc/httpd/conf.modules.d/00-mpm.conf

Configure Apache PHP Module

Verify the socket name used in /etc/php-fpm.d/ — Fedora is configured from /etc/php-fpm.d/www.conf with a socket at /var/run/php-fpm/www.sock

cp /etc/httpd/conf.modules.d/15-php.conf /etc/httpd/conf.modules.d/15-php.conf.orig
vi /etc/httpd/conf.modules.d/15-php.conf

# Handle files with .php extension using PHP interpreter

# Proxy declaration
<Proxy "unix:/var/run/php-fpm/www.sock|fcgi://php-fpm">
    	ProxySet disablereuse=off

# Redirect to the proxy
<FilesMatch \.php$>
	SetHandler proxy:fcgi://php-fpm

# Allow php to handle Multiviews
AddType text/html .php

# Add index.php to the list of files that will be served as directory
# indexes.
DirectoryIndex index.php

Enable php-fpm to auto-start, start php-fpm, and restart Apache

systemctl enable php-fpm
systemctl start php-fpm
systemctl restart httpd

Voila — phpinfo() confirms that I am using FPM/FastCGI

We’ll see if this actually does anything to improve performance!

Modifying Shared PHP Function

We needed to modify a shared function to include additional information … but didn’t want to coordinate changing all of the calls to the function as one change. Simplest way to accomplish that was to set a default value for the new parameter — either to NULL and just not do the new thing when the parameter is NULL or some value that indicates that we’re not yet gathering that data.


function testFunction($strOldParameter, $strNewParameter=NULL){
     echo "The old parameter is |$strOldParameter|\n";
          echo "The new parameter is |$strNewParameter|\n";

testFunction("first", "second");


Handling PHP Execution Timeout

There’s no straight-forward way to handle execution timeout in PHP 5.x — it’s not like you can try/except or something. The execution time-limit is exceeded, the program terminates. Which, thinking from the perspective of the person who maintains the server, is a Good Thing … bugger up the ‘except’ component and now that becomes an infinite loop.

But I’m looking to throw a “pretty” error to the end user and have them try again with a data set that will take less time to process. Turns out, you can use a shutdown function to display something other than the generic PHP time limit exceeded page.


function runOnShutdown(){
     $arrayError = error_get_last();

     if( substr($arrayError['message'], 0, strlen("Maximum execution time of")) === "Maximum execution time of"   ){
          echo "<P>Maximum execution time";

function noOp($iInteger){
     for($z = 0; $z < $iInteger; $z++){
          $a = $iInteger * $iInteger;
     return $iInteger;

ini_set('display_errors', '0');
ini_set('max_execution_time', 2);

// for($i = 0; $i < 10; $i++){
for($i = 0; $i < 10000; $i++){
     $j = noOp($i);
     print "<P>$j</P>\n";
print "<P>Done</P>\n";


And the web output includes a customized message because the max execution time has been exceeded.


Testing Procedural Code with PHPUnit

You can use PHPUnit to test procedural code — in this case, I’m testing the output of a website. I have some Selenium tests for UI components but wanted to use the shell executor for functional testing. In the test code, you can populate the _SERVER and _POST (or _GET) arrays and simulate the web environment.

    namespace phpUnitTests\CircuitSearch;
    class CircuitExportTest extends \PHPUnit_Framework_TestCase{
        private function _execute(array $paramsPost = array(), array $paramsServer = array() ) {
            $_POST = $paramsPost;
            $_SERVER = $paramsServer;
            include "../../myWebSitePage.php";
            return ob_get_clean();
        public function testUsageLogging(){
            $argsPost = array('strInput'=>'SearchValue', 'strReportFormat'=>'JSON');
            $argsServer = array("DOCUMENT_ROOT" => '/path/to/website/code/html/', "HOSTNAME" => getHostByName(),
                                "SERVER_ADDR" => getHostByName(php_uname('n')), "PWD" => '/path/to/website/code/html/subcomponent/path');
            $this->assertEquals('{}', $this->_execute($argsPost, $argsServer));

Running the test, my web output is compared to the static string in assertEquals. In this case, I am searching for a non-existent item, nothing is returned, and I expect to get empty braces. I could use AssertsRegExp or or AssertsStringContainsString to verify the specifics of a real result set.

Corrupted Spreadsheets From PHPSpreadsheet (andPHPExcel)

I need to deliver Excel files to the browser, so used php://output as the save location. Does exactly what I want except …

Excel says it has a problem with some of the file content. It’s recoverable – click “Yes” and you’ll see all of the spreadsheet data. But no one is going to want to run a repair on every single file they download from my site!

I confirmed the buffer was being cleared, that I didn’t have any extraneous PHP errors getting inserted into the spreadsheet data. My output was clean – it was also corrupt. I’d actually started using the old PHPExcel module, installed and changed over to PHPSpreadsheet because I know PHPExcel is not maintained. But the problem persisted. I started reading through the docs for PHPSpreadsheet to see if I could find a hint.



Make sure not to include any echo statements or output any other contents than the Excel file. 
There should be no whitespace before the opening <?php tag and at most one line break after the closing ?> tag 
(which can also be omitted to avoid problems). 
Make sure that your script is saved without a BOM (Byte-order mark) because this counts as echoing output. 
The same things apply to all included files. 
Failing to follow the above guidelines may result in corrupt Excel files arriving at the client browser, 
and/or that headers cannot be set by PHP (resulting in warning messages).

Do I have more than one newline after the closing “?>” tag? Sure do!

Got rid of the extra newline, and the downloaded file is fine.