Inserting data in Oracle database using PHP

By Sergey Skudaev

Before reading this page visit PHP: Migrating from MySQL to Oracle, which is the first part of my tutorial about using Oracle database with PHP. Here is an insert code example. First, you have to create connection.

To connect to Oracle database we use the following PHP code:

$conn=oci_connect($dbuser, $dbpassword, $dbhost);

Check if connection is created:

if (!$conn)
	{
	 $e = oci_error();   // For oci_connect errors pass no handle

	 echo "if not connection<br>";

	  echo htmlentities($e['message']);
	}
	else
	{
$isql="INSERT INTO usernames
VALUES(usernames_seq.nextval,
       'Smith',
       'James',
       'secret',
       'jamess@yahoo.com',
       'admin',
       '29-JAN-01',
       '12 west 15 STREET',
       'Dunedin',
       'FL',
       '34697');
$stmt = oci_parse($conn,$isql);
$rc=oci_execute($stmt);
	if(!$rc)
	{
	$e=oci_error($stmt);
	var_dump($e);
	}

	oci_commit($conn);

oci_free_statement($stmt);
oci_close($conn);

Using binding a PHP variable to Oracle placeholder for insert statement

It is considered as more efficient way to insert data in Oracle table by binding variable to Oracle placeholder. The data associated with a bind variable is never treated as part of the SQL statement and as a result it reduces possibility of SQL Injection. Besides, it increases performance

Do not use magic_quotes_gpc or addslashes() when you use oci_bind_by_name() as no quoting is needed. "oci_bind_by_name()" inserts data without removing quotes or escape characters.

$isql="insert into(userid, lastname, firstname, username, password, email, role, dob, address, city, state, zip)
values (:userid_bv, :lastname_bv, :firstname_bv, :username_bv, :password_bv, :email_bv, :role_bv, :dob_bv, :address_bv, :city_bv, :state_bv, :zip_bv)";
$stmt = oci_parse($conn,$isql);

$userid=7;
$lastname='Smith';
$firstname='John';
$username='johns;
$password='password';
$email='johns@yahoo.com';
$role='user';
$dob='10-MAR-09';
$address='12 East 19 street';
$city='New York';
$state='NY';
$zip='11200';

oci_bind_by_name($stmt, ":userid", $userid
oci_bind_by_name($stmt, ":lastname_bv", $lastname);
oci_bind_by_name($stmt, ":firstname_bv", $firstname);
oci_bind_by_name($stmt, "username_bv", $username);
oci_bind_by_name($stmt, ":password_bv", $password);
oci_bind_by_name($stmt, ":email_bv", $email);
oci_bind_by_name($stmt, ":role_bv", $role);
oci_bind_by_name($stmt, ":dob_bv", $dob);
oci_bind_by_name($stmt, ":address_bv",$address);
oci_bind_by_name($stmt, ":city_bv", $city);
oci_bind_by_name($stmt, ":state_bv",$state);
oci_bind_by_name($stmt, ":zip_bv",$zip);

oci_execute($stmt);
oci_free_statement($stmt);
oci_close($conn);

Using oci_bind_by_name for updating record

To update a record in Oracle table with PHP you should use binding PHP variable to Oracle placeholder as it is more efficient and safe way. This is an example of updating a user email address

$usql="update usernames set email=:email_bv where username=:username_bv";
$stmt = oci_parse($conn,$usql);
$username='johns;
$email='johns@gmail.com';

oci_bind_by_name($stmt, ":username_bv", $username);
oci_bind_by_name($stmt, ":email_bv", $email);

oci_execute($stmt);