Computer Programming web Web programming Tips



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);

My eBooks on Amazon.com

Subscribe to our mail list to receive free code examples.

Comments

No comments yet.

Add Comment

* Required information
(never displayed)
 
Smile Sad Huh Laugh Mad Tongue Crying Grin Wink Scared Cool Sleep Blush Unsure Shocked
 
1000
Enter the last letter of the word satellite.
 
Enter answer:
Captcha
Refresh
 
Enter code:
 
Notify me of new comments via email.
 
Remember my form inputs on this computer.
 
I have read and understand the privacy policy. *
 
I have read and agree to the terms and conditions. *
 
 
Powered by Commentics