PHP and Oracle: Christopher Jones

Our PHP Quebec Talks are on http://db360.blogspot.com/2008/03/my-presentations-at-php-quebec-2008.html

Thanks for the ElePHPants given away at PHP Quebec go to Oracle Technology Network - our fantastic team bringing you all the OTN content and leaders themselves in the use of new technology.

<?php

// I often see questions about using Oracle's Spatial functionality in
// PHP. Oracle Spatial is a powerful library for adding location
// awareness to applications. This script uses the core subset of
// Spatial, called Oracle Locator, which is included in all Oracle
// Database editions.
//
// A great introduction to Oracle Locator is in the Database Express
// Edition "2 Day Plus Locator Developer Guide". Other Oracle XE guides
// are on the Oracle XE homepage.
//
// The first thing to determine is what functionality you need to do
// in PHP. Is there really a need to fetch the data to PHP? Or can
// you use pre-supplied (or your own) PL/SQL routines? It's often
// faster to keep the processing close to the data and return only the
// minimum amount of data to PHP. This lets Oracle do the data
// processing efficiently, and reduces network traffic between PHP and
// Oracle.
//
// Inserting values is simply a matter of executing the appropriate
// INSERT statement in PHP.  This is left as an exercise for the reader!
//
// If you want to fetch values, here are some methods you can use.
//
// This example uses the tables created in the Oracle Database Express
// Edition "2 Day Plus Locator Developer Guide" sample scenario.
// Create these in SQL Developer or SQL*Plus before running this script.

$c = @oci_connect('hr', 'hrpwd', '//localhost/XE'); ;
if (!$c) {
 $m = oci_error();
 echo $m['message'], "\n";
 exit;
}

echo "Simple Column Types\n\n";

// Queries using basic types are no different to other PHP queries.
// The first query finds the three closest customers to the store
// with ID of 101. The query is only of NUMBER and VARCHAR2 columns,
// using an in-built Spatial function SOD_NN() to determine the
// relationship of the customers.
 
$sql = "SELECT /*+ ordered */ 
 c.customer_id, 
 c.first_name, 
 c.last_name
 FROM stores s, customers c
 WHERE s.store_id = :sid 
 AND sdo_nn(c.cust_geo_location, s.store_geo_location, :nres) = 'TRUE'"; 
$sid = 101;
$nres = 'sdo_num_res=3';

$s = oci_parse($c, $sql);
oci_bind_by_name($s, ":sid", $sid);
oci_bind_by_name($s, ":nres", $nres);
oci_execute($s);
oci_fetch_all($s, $res); // or your favorite fetch function
var_dump($res);

// The output is:
//
// array(3) {
//   ["CUSTOMER_ID"]=>
//   array(3) {
//    [0]=>
//    string(4) "1001"
//    [1]=>
//    string(4) "1003"
//    [2]=>
//    string(4) "1004"
//   }
//   ["FIRST_NAME"]=>
//   array(3) {
//    [0]=>
//    string(9) "Alexandra"
//    [1]=>
//    string(6) "Marian"
//    [2]=>
//    string(6) "Thomas"
//   }
//   ["LAST_NAME"]=>
//   array(3) {
//    [0]=>
//    string(7) "Nichols"
//    [1]=>
//    string(5) "Chang"
//    [2]=>
//    string(8) "Williams"
//   }
// }

echo "\nSDO_UTIL.GETVERTICES\n\n";

// If you want to return data to PHP, there are some inbuilt functions
// like SDO_UTIL.GETVERTICES, which return the coordinates from a
// geometry.

$sql = "select t.x, t.y 
 from customers, 
 table(sdo_util.getvertices(customers.cust_geo_location)) t 
 where customer_id = :cid";
$cid = 1001;

$s = oci_parse($c, $sql);
oci_bind_by_name($s, ":cid", $cid);
oci_execute($s);
oci_fetch_all($s, $res); // or your favorite fetch function
var_dump($res);

// The output is:
//
// array(2) {
//  ["X"]=>
//   array(1) {
//    [0]=>
//    string(9) "-71.48923"
//   }
//  ["Y"]=>
//   array(1) {
//    [0]=>
//    string(8) "42.72347"
//   }
// }

echo "\nCustom function\n\n";

// Since PHP can't fetch complex Oracle types as PHP objects or
// arrays, sometimes you may want to create a PL/SQL function to
// de-compose the data into simple types. This example uses the
// COLA_MARKETS table from example 1-8.  Create this table and
// insert the sample data using SQL Developer or SQL*Plus. Then
// continue with this PHP script.
//
// The example row has a polygon of (x,y) ordinates in the
// SDO_ORDINATE_ARRAY array:
//
// INSERT INTO cola_markets VALUES(
//  301,
//  'polygon',
//   SDO_GEOMETRY(
//    2003, -- two-dimensional polygon
//    NULL,
//    NULL,
//    SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
//    SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
//   )
// );
//
// By looking at the SDO_GEOMETRY object structure you can query just
// the ordinate collection and create a decomposition function. Use
// an alias (also known as a "correlation name") for the table in the
// query. Thanks to Bryn LLewellyn for pointing this trick out.

$s = oci_parse($c, 
"create or replace procedure myproc(
 p_id in number, p_o out sdo_ordinate_array) as
 begin
 select v.shape.sdo_ordinates 
 into p_o 
 from cola_markets v 
 where mkt_id = p_id;
 end;");
oci_execute($s);

// Now PHP can fetch the geometry data as a collection

$s = oci_parse($c, "begin myproc(:id, :ords); end;");
$id = 301;
oci_bind_by_name($s, ":id", $id);
$ords = oci_new_collection($c, "SDO_ORDINATE_ARRAY");
oci_bind_by_name($s, ":ords", $ords, -1, OCI_B_NTY);
oci_execute($s);

for ($i = 0; $i < $ords->size(); $i++) {
 $v = $ords->getElem($i);
 echo "Value: $v\n";
}

// The output is the list of coordinates matching the inserted data:
//
// Value: 5
// Value: 1
// Value: 8
// Value: 1
// Value: 8
// Value: 6
// Value: 5
// Value: 7
// Value: 5
// Value: 1

?>