,

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

Did you find information useful?
Send to your friend a link to this page

Please rate the tutorial

1 2 3 4 5 6 7 8 9 10



Learn Visual Basic 6.0 [Kindle Edition] $1.99

How to Build Your Own Web Site from Scratch [Kindle Edition] $1.99

Comments
 
Register to add comments ( 1000 char ) for php_oracle_insert.php.