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:
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.
$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);
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);
{
$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 performanceDo 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);
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);