PHP: using PDO to access Oracle database

Install on your PC Oracle Database 11g Express Edition http://www.oracle.com/technetwork/database/database-technologies/express-edition/overview/index.html and oracle client: instantclient-basic-nt-11.2.0.4.0.zip http://www.oracle.com/technetwork/topics/winsoft-085727.html

While installation oracle database, you will be prompt to enter username and password. Write it down for the future use.

After installation the Orcale Database and client, open command prompt and type:"sqlplus". Below is a copy from the command prompt window

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\Sergey>sqlplus


Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: sergey_s

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Now you can copy and paste the following create table statement to create myusernames table.

CREATE TABLE myusernames(
usernameid NUMBER(11),
lastname VARCHAR2(20),
firstname VARCHAR2(20),
username VARCHAR2(20),
password VARCHAR2(50) NOT NULL,
email VARCHAR2(100) NOT NULL,
role VARCHAR2(20) NOT NULL,
active NUMBER(4),
CONSTRAINT pk_emp PRIMARY KEY (usernameid)
);

After creating the table, create sequence to use it as autoincrement id field.

CREATE SEQUENCE myusernames_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  NOCACHE;

Ctreate connect.php file with code for PDO connection to the oracle database. On my PC it look like that:

≶?php
//connect.php file code start

$mydb="
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = sergeysk)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )";

  $conn_username = "your user name";
  $conn_password = "your password";

  $opt = [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM,

];

  try{
      $conn = new PDO("oci:dbname=".$mydb, $conn_username, $conn_password, $opt);
  }catch(PDOException $e){
      echo ($e->getMessage());
}

//connect.php file code end
?>

A host name "HOST =sergeysk" in the connection is my PC name:(sergeysk).

Now, let´ insert users on the myusernames table.

pdo_insert_users.php

≶?php
//pdo_insert_users

include('connect.php');

$sql="insert into myusernames (usernameid, lastname, firstname, username, password, email, role, active) values
(myusernames_seq.nextval, :lastname, :firstname, :username, :password, :email, :role, :active)";

 try {

  $stmt = $conn->prepare($sql);

//$id=5;
$lastname='Wiliams';
$firstname='James';
$username='jamesw';
$password='5f4dcc3b5aa765d61d8327deb882cf99';  // md5 hash encrypted word: "password"
$email='myemail.gmail.com';
$role='user';
$active=1;

       $stmt->bindParam(':lastname', $lastname);
       $stmt->bindParam(':firstname', $firstname);
       $stmt->bindParam(':username', $username);
       $stmt->bindParam(':password', $password);
       $stmt->bindParam(':email', $email);
       $stmt->bindParam(':role', $role);
       $stmt->bindParam(':active', $active);

   $stmt->execute();

   echo "New records created successfully";
    }
        catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }
?>

The myusernames_seq.nextval sequence will be increased by one with inserting each record.

After inserting few records, we can try to view our users.

pdo_select_users.php

<?php
//pdo_select_users

include('connect.php');

try{
	$stmt = $conn->query('select usernameid, lastnames, firstname, username, email FROM myusernames');

	while ($row = $stmt->fetch())
	{
    	echo $row[0] .",".  $row[1] . ", " . $row[2] .", " . $row[3] .", " . $row[4] ."
";
	}

 }catch(PDOException $e){
     echo ($e->getMessage());
}
?>

OUTPUT

| 1 | Wiliams, James | jamesw | myemail.yahoo.com |

Misspell a colum name to produce error. For example, change "lastname" to "lastnames". The error message will be displsyed.

SQLSTATE[HY000]: General error: 904 OCIStmtExecute: ORA-00904: "LASTNAMES": invalid identifier (ext\pdo_oci\oci_statement.c:148)

If you comment try/catch statements, the message will be:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]:
General error: 904 OCIStmtExecute: ORA-00904: "LASTNAMES": invalid identifier (ext\pdo_oci\oci_statement.c:148)' in
C:\xampp\htdocs\oracle\pdo_select_users.php:8 Stack trace: #0
C:\xampp\htdocs\oracle\pdo_select_users.php(8): PDO->query('select username...')
#1 {main} thrown in C:\xampp\htdocs\oracle\pdo_select_users.php on line 8,

In the php.ini file, set display_errors=off and log_errors=on on live server and set display_errors=on and error_reporting on a developement server.

Now, let´ update a user record.

pdo_update-user.php

<?php
//pdo_update_users

include('connect.php');

$username='jamesw';
$role='admin';

 try {

$stmt3 = $conn->prepare('UPDATE myusernames SET role= :role  WHERE username = :username');
 $stmt3->execute(['role' => $role, 'username' => $username]);

 echo "The records updated successfully";
    }
        catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }

?>

To delete a user use the following code:

<?php
//pdo_delete_users

include('connect.php');

$username='jamesw';

 try {

$stmt3 = $conn->prepare('delete from myusernames WHERE username = :username');
 $stmt3->execute(['username' => $username]);

 echo "The records deleted successfully";
    }
        catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }

?>

About using PDO with MySQL, you can read the following tutorial: (The only proper) PDO tutorial (about using PDO with MySQL)

My eBooks on Amazon.com

US    UK    BR    CA

US   UK   BR   CA

US    UK    BR    CA