Example 1
Fetching data from an Oracle database using PHP involves a few steps:
- Connect to the database using the
oci_connectfunction. This function takes three parameters: the username, password, and the hostname/database.
$conn = oci_connect('username', 'password', 'hostname/database');
- Prepare the SQL query using the
oci_parsefunction. This function takes two parameters: the connection resource and the query string.
$query = "SELECT * FROM tablename WHERE column1 = :bind1";
$stid = oci_parse($conn, $query);
- Bind any variables to the query using the
oci_bind_by_namefunction. This function takes three parameters: the statement resource, the bind variable name, and the variable that you want to bind to it.
oci_bind_by_name($stid, ':bind1', $val1);
- Execute the query using the
oci_executefunction. This function takes one parameter: the statement resource.
$result = oci_execute($stid);
- Check for errors using the
oci_errorfunction. This function takes one parameter: the statement resource.
if (!$result) {
$e = oci_error($stid);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
- Fetch the data using the
oci_fetch_arrayfunction. This function takes one parameter: the statement resource.
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
// Do something with the data
}
- Close the connection using the
oci_closefunction. This function takes one parameter: the connection resource.
oci_close($conn);
It is important to note that, the above example uses the deprecated oci_* functions, Oracle recommends the use of PDO_OCI extension to connect to oracle database instead of oci_* functions.
Also, it is recommended to use prepared statement to prevent SQL injection and it is more secure.
In the above example tablename and column1 should be replaced with the appropriate values for your Oracle database. and $val1 is the value you want to compare with the column1.
Example 2
In this example we use 2 file for Fetch Data using Oracle and PHP.
- database.php
- view.php
database.php
<?php
$conn=oci_connect("COMMON","ADMIN","172.16.16.20/WETDB");
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
?>
view.php
<!DOCTYPE html>
<html>
<head>
<title> Retrive data</title>
</head>
<body>
<table>
<tr>
<td>First Name</td>
<td>Last Name</td>
<td>City</td>
<td>Email id</td>
</tr>
<?php
$i=0;
while($row=oci_fetch_array($result)) {
?>
<tr>
<td><?php echo $row["first_name"]; ?></td>
<td><?php echo $row["last_name"]; ?></td>
<td><?php echo $row["city_name"]; ?></td>
<td><?php echo $row["email"]; ?></td>
</tr>
<?php
$i++;
}
?>
</table>
</body>
</html>
0 Comments