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)

Our dog needs urgent surgery, and the cost is overwhelming.

Any help, big or small, would mean the world to us. Thank you for supporting Oscar on his journey to recovery!

Oscar Story.

Oscar wasn’t just any puppy—he was a gift from a mother who trusted us with her smallest one.

For five years, my wife worked at the Indian Medical Center in Arizona, deep in Navajo Nation. Near her clinic, she often saw a homeless dog wandering the area. Over time, she began feeding her, and the dog grew fond of her. Then, one day, that same dog brought her newborn puppies to my wife—as if proudly showing them off.

Among them was the smallest, most delicate pup. My wife couldn’t resist. She brought him home, and we named him Oscar.

Oscar thrived in the house provided by the medical center, enjoying the big backyard where he lived. I built him a sturdy wooden doghouse, and we often took him on walks along the Window Rock Trail. He became our adventure companion, making the vast desert feel like home.

After my wife’s contract ended, we moved back to Florida, bringing Oscar with us. He adjusted to his new surroundings, but he never lost his adventurous spirit.

Now, Oscar faces a tough challenge—he needs urgent surgery, and the cost is overwhelming. We want to give him the best care possible, just as he’s given us years of joy and loyalty.

Any help, big or small, would mean the world to us. Thank you for supporting Oscar on his journey to recovery!