Using PHP to Connect to an Oracle Database

PhillipsPlastics
PhillipsPlastics used Ask the Experts™
on
I am trying to write a search query for the Oracle database using php but cannot seem to get either a success or an error running the php.  What am I doing wrong here I am really not sure how to properly use the connection strings.  I have the Oracle client installed and running under the user "sql" and have tested it to ensured it works from console.
<?php 
// No HTML required by this script!
// Validate that the page received $_GET['email']:
if (isset($_GET['email'])) {
	// Connect to the database.
	// Assumes you are using PHP 5, 
	// see the PHP manual for PHP 4 examples. 
	$c = oci_pconnect ('USERNAME', 'PASSWORD', '//appex1.xxx.com:1521//SERVICENAME') OR die('Unable to connect to the database. Error: <pre>' . print_r(oci_error(),1) . '</pre>');
	
	// Define the query.
	$q = "Select * from PHONE_MASTER_DATA;";
	// SELECT LAST_NAME, FIRST_NAME, PREFIX, DN, FACILITY_NAME, PHONE_NO from PHONE_MASTER_DATA, PHONE_FACILITY where ((UPPER(LAST_NAME) LIKE '"
		//& (strLastName.ToUpper) & "%' " & "AND UPPER(FIRST_NAME) LIKE '" & (strFirstName.ToUpper) & "%') AND FACILITY_ID = PHONE_FACILITY.ID) order by LAST_NAME";
	
	//  email='{$_GET['email']}'"

	// Parse the query.
	$s = oci_parse($c, $q);
	
	// Initialize the PHP variable:
	$rows = 0;

	// Bind the output to $rows:
	oci_define_by_name($s, "NUM_ROWS", $rows);

	// Execute the query.
	oci_execute($s);
	
	// Fetch the results.
	oci_fetch($s);
	
	// Close the connection.
	oci_close($c);
	/* Return a message indicating the status.
	if ($rows > 0) {
		echo 'Email address has already been registered!';
	} else {
		echo 'Email address is available!';
	}*/
}
?>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Do you receive any kind of error or just no output whatsoever?

Try enabling errors by placing the following line at the very beginning of your page:

error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);

In the connection string, line 8 above, try removing the first two slashes, and use a single slash, (in my code I see only single slash)

appex1.xxx.com:1521/SERVICENAME

Try also the simple example at http://www.php.net/manual/en/function.oci-connect.php , just update the connection settings and the SQL command.

Author

Commented:
Well I tried both suggestions including the error logging to no luck.  I think I will try and use the code provided on the php site but can only get up to "Hello line 6" to echo and no further.  With the error reporting enabled I also cannot seem to find where it outputs the error log to which would be helpful.
<html>
<body>
<?php 
error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
$db = "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = appex1.phillips-origen.com)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = apexdbp)))";
echo "Hello line 6";
$conn = oci_connect('production1', 'Prod1!', $db);
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
echo "Hello line 12";
$stid = oci_parse($conn, 'SELECT * FROM PHONE_MASTER_DATA');
oci_execute($stid);

echo "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

?>
<p>Hello is there anyone there?</p>
</body>
</html>

Open in new window

Do you set up tnsnames ?<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yes tnsnames is setup under $ORACLE_HOME/network/admin/ but I wanted to test without it which seems possible.
Firstly , try to setup tnsname.ora

for example :
// Connects to the MYDB database described in tnsnames.ora file,
// One example tnsnames.ora entry for MYDB could be:
//   MYDB =
//     (DESCRIPTION =
//       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
//       (CONNECT_DATA =
//         (SERVER = DEDICATED)
//         (SERVICE_NAME = XE)
//       )
//     )

Test it after that : tnsping mydb ....

if you are succesfully :

try code below and make sure you intall libary OCI ( check by phpinfo())



<html>
<body>
<?php 
$conn = oci_connect('production1', 'Prod1!', 'mydb');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
echo "Hello line 12";
$stid = oci_parse($conn, 'SELECT * FROM PHONE_MASTER_DATA');
oci_execute($stid);

echo "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

?>
<p>Hello is there anyone there?</p>
</body>
</html>

Open in new window

This link is helpful for you :

http://wiki.oracle.com/page/PHP+Oracle+FAQ
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">

Author

Commented:
So I didn't have the oci8.so installed but got that properly installed and linked and it now reports in phpinfo() now however everytime I hit the page with the above code I get a "[notice] child pid 15881 exit signal Segmentation fault (11)" error in my apache error log.  Ideas?

Author

Commented:
This error only happens if the correct service address and name are entered.  If the parameters are incorrect the error_log will report ORA-12154: TNS:coud not resolve teh connect identifier specified in (FILE).
Please send your tnsname.ora

Author

Commented:
# tnsnames.ora Network Configuration File: /app/oracle/product/11.1.0/client_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

APEXDBP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = appex1.phillips-origen.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = apexdbp)
    )
  )

Author

Commented:
Unreal.... someone checked and the account I was using to log in to run my query had an expired password... arg.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial