PHP and Oracle: Queries

(Page 1 of 2 )

In this third part of an article series on Oracle Database XE and PHP, you'll learn how to correctly parse a query to the database, and how to retrieve table rows. This article is excerpted from chapter 32 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Preparing, Binding, and Executing Statements

Before you can run a query against your Oracle database, you must first parse the query. You use oci_parse() to prepare the query; oci_bind_by_name() to optionally bind PHP variables to SQL variables; and then oci_execute() to run the query.

oci_parse()

You parse a query by making a call to oci_parse, with a syntax as follows:

resource oci_parse ( resource connection, string query )

After you establish a connection to the database, you use oci_parse() to prepare the SQL statement for execution and very basic validations such as correctly quoted strings. Determining the existence of the referenced objects and verifying that the syntax of the query is correct and whether the user has the privileges to access the objects in the SQL statement doesn't occur until the execution phase.

oci_bind_by_name()

Binding a variable is simply a variable substitution at run time. In other words, your SQL text contains a placeholder, and you substitute an actual value for the placeholder using oci_bind_by_name with this syntax:

bool oci_bind_by_name ( resource statement, string query, mixed variable,
                          [, int maxlength [, int type]])

Binding variables in OCI8 has two distinct advantages: security and performance. Using bound variables prevents SQL injection attacks. Your PHP code has control over the SQL statement executed by ensuring that user input maps to constants in the WHERE clause rather than column names in the SELECT clause, for example. Performance of subsequent executions of SQL statements with bind variables is improved because the parse phase only needs to occur once.

You bind values to variables in the SQL statement for variable names preceded by a colon. We'll show you an example after we introduce oci_execute(). You use the optional maxlength parameter to provide a maximum length for a value returned from PL/SQL procedures and functions; otherwise, maxlength defaults to the current length of the bound PHP variable. You use the other optional parameter, type, for abstract datatypes such as LOBs (large objects).

oci_execute()

The oci_execute() function submits the SQL statement to Oracle for execution. You fetch the results of the query using one of the many ..._fetch_...() functions we present in the following sections. Here is the syntax:

bool oci_execute ( resource statement [, int mode] )

In a default installation of Oracle Database XE, you get several sample schemas for training and testing and that highlight various Oracle features. The HR schema's table LOCATIONS contains a list of the cities and countries where the company does business; here is a list of the columns in the LOCATIONS table:

SQL> describe locations

--------------------------------------------
Name                   Null?    Type
---------------------- -------- -----------
LOCATION_ID            NOT NULL NUMBER(4)
STREET_ADDRESS                  VARCHAR2(40)
POSTAL_CODE                     VARCHAR2(12)
CITY                   NOT NULL VARCHAR2(30)
STATE_PROVINCE                  VARCHAR2(25)
COUNTRY_ID                      CHAR(2)

In this example, we'll check the HR user's LOCATIONS table for cities whose name begins with B or S, and give you a sneak peek at the oci_fetch_array() function in Listing 32-3.

Listing 32-3. Retrieving City and Province Information from the LOCATIONS Table

<?php
   $c = oci_connect('hr', 'hr', '//localhost/xe');
   $s = oci_parse($c,
      "select city, state_province from locations where city like :city_prefix"); 
   $cp = 'B%'; // Cities beginning with B
   oci_bind_by_name($s, ':city_prefix', $cp);
   oci_execute($s);
   echo 'Cities beginning with ' . $cp . '<br /><br />';
   while ($res = oci_fetch_array($s)) {
      echo $res['CITY'] . ', ' . $res['STATE_PROVINCE'] . "<br />";
   }
   echo '<br /><br />';
   $cp = 'S%'; // Cities beginning with S
   oci_bind_by_name($s, ':city_prefix', $cp);
   oci_execute($s);
   echo 'Cities beginning with ' . $cp . '<br /><br />';
   while ($res = oci_fetch_array($s)) {
      echo $res['CITY'] . ', ' . $res['STATE_PROVINCE'] . "<br />";
   }

   oci_close($c);
?>

Here is the output you see from executing this script:

--------------------------------------------
Cities beginning with B%

Beijing,
Bern, BE
Bombay, Maharashtra

Cities beginning with S%

Sao Paulo, Sao Paulo
Seattle, Washington
Singapore,
South Brunswick, New Jersey
Southlake, Texas
Stretford, Manchester
Sydney, New South Wales

--------------------------------------------

The percent sign, %, is a wildcard character that matches zero or more occurrences of any character. In this example, S% would even match a city name of S, however unlikely it is that there is a city with that name. Notice that we did not have to execute oci_parse() more than once.

Next: Retrieving Table Rows >>
 

More Database Articles Articles
More By Apress Publishing

blog comments powered by Disqus