Lab 9 PHP

1
Lab 9PHP Oracle

• COMP231 Database Management Systems
• Spring 2009

2
Getting Start

• Use SSH to connect comp231.cs.ust.hk
• Create public_html under your home directory
• The premission of the following directory should
  be executable to others (i.e. chmod 705)
• /homes/your_account
• /homes/your_account/public_html
• The .php files

3
First PHP file

• Create test.php with the following
  code
• Upload test.php to your public_html directory.
• Open IE and browsehttp//comp231.cse.ust.hk/yo
  ur_account/test.php
• Congratulation if you see Hello world in IE.

4
Oracle Call Interface

• To access Oracle database in PHP, we can use OCI
  functions.
• Common OCI functions
• ocilogon() and ociconnect() Establish a
  connection to Oracle database
• ociparse() prepare an Oracle statement with SQL
  query
• ociexecute() Execute an oracle statement
• ocifetch() Fetch a row from the data returned
  from the database
• ociresult() Retrieve a value of a specific
  field
• ocinewcursor() Create a cursor for storing data
  returned from the database
• ocibindbyname() Bind a cursor to an output
  cursor parameter of an Oracles stored procedure
• oci_feltch_array() Fetch a row into an array

5
Establishing a Connection to Oracle

• To establish a connection to Oracle database, you
  can use
• resource oci_connect(string username, string
  password, string db)
• resource ocilogon(string username, string
  password, string db)
• oci_connect and ocilogon are identical.
• Exampleconn ocilogon(comp231, ab1234,
  comp231.cse.ust.hk)
• You can find the example code

6
Executing Query through PHP (1/2)

• After established a connection to Oracle
  database, we run execute any SQL query like using
  SQLPlus.
• To execute an query, we need to prepare Oracle
  statement by using ociparse().parse
  ociparse(conn, query)
• And then use ociexecute() to execute the oracle
  statement.ociexecute(parse, OCI_COMMIT_ON_SUCCES
  S)
• You can control whether PHP will commit the
  statement by using OCI_COMMIT_ON_SUCCESS
• You may not want to commit the statement
  immediate, so you use OCI_DEFAULT and use
  oci_commit() later.

7
Executing Query through PHP (2/2)

• With ociparise() and ociexecute(), you can do the
  followings
• Create / drop tables
• Insert / update / delete records
• Create procedures
• Etc.

8
Retrieving Data using SELECT statement

• To retrieve data from the database, we need to
  use SELECT statement.
• So, we need to perform the following steps
• Establish a connection to the data using
  ocilogon().conn ocilogon(comp231, ab1234,
  comp231.cse.ust.hk)
• Prepare the query using ociparse() and
  ociexecute()query select table_name from
  user_tablesparse ociparse(conn,
  query)ociexecute(parse, OCI_DEFAULT)
• Fetch a row from the database returns using
  ocifetch()ocifetch(parse)
• Retrieve specific field from the fetched row
  using ociresult()echo ociresult(parse,
  TABLE_NAME)
• You can find an example code here

9
Retrieving Data using Cursor with Stored
Procedure (1/2)

• We usually create stored procedures in the
  database for data retrieval.
• To use the data returned from a procedure, we
  need to perform the following steps
• Establish a connection using ocilogon()
• Create a cursor object using ocinewcursor()curs
  ocinewcursor(conn)
• Prepare Oracle statement using ociparse()parse
  ociparse(conn, begin showprod(data)
  end)// showprod is a procedure // data is
  a output cursor parameter

10
Retrieving Data using Cursor with Stored
Procedure (2/2)

• Bind the cursor object to the procedure parameter
  using ocibindbyname()ocibindbyname(parse,
  data, curs, -1, OCI_B_CURSOR)
• Execute the Oracle statements using
  ociexecute()ociexecute(parse)ociexecute(curs)
  
• Fetch a row from procedure returns using
  oci_fetch_row()row oci_fetch_row(curs)echo
  row0 // first fieldecho row1 // second
  field
• You can find an example code here

11
Suggestion

• To increase your implementation speed of your
  project, I suggest you to install software on
  your own PC.
• Oracle Express Editionhttp//www.oracle.com/techn
  ology/software/products/database/xe/htdocs/102xewi
  nsoft.html
• Apache http serverhttp//ftp.cuhk.edu.hk/pub/pack
  ages/apache.org/httpd/binaries/win32/apache_2.2.11
  -win32-x86-no_ssl.msi
• PHP packagehttp//hk.php.net/get/php-5.2.9-2-win3
  2-installer.msi/from/this/mirror
• You need to install Oracle first, and then
  Apache, and finally PHP package.

12
PHP Package Installation

• For the PHP package installation, you need to
  ensure
• Select Apache 2.2.X Module
• Select Will be installed on local hard disk for
  Oracle (8) item under Externsions
• If you want to use short open tags (setting in php.ini file.
• Find php.ini in the php installation directory
  (default directory is c\program files\php)
• Find the following line in php.inishort_open_tag
  off
• Change it as followsshort_open_tag on
• After you can any setting for either PHP and
  Apache, you need to restart Apache service.

13
References

• Passing information using _GET and _POST
• Demohttp//comp231.cse.ust.hk/mandelc/logon.htm
  l
• Source code http//course.cse.ust.hk/comp231/spr
  ing09/labs/php/logon.txt http//course.cse.ust.hk
  /comp231/spring09/labs/php/connect2.txt
• Apache HTTP server documentationhttp//httpd.apac
  he.org/docs/2.2/
• PHP package installation documentationhttp//www.
  php.net/manual/en/install.windows.php
• Oracle PHP Apache installation
  documentationhttp//www.oracle.com/technology/tec
  h/php/htdocs/inst_php_apache_windows.html
• OCI8 functions in PHPhttp//hk2.php.net/manual/en
  /ref.oci8.php