Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Oracle, PHP and SQL manipulation

K

kolax

macrumors G3
Original poster
Mar 20, 2007
9,181
115
Hello,

I'm producing a website as part of a uni project that will hold a load of recipes, and allow users to search for them. The database is hosted using Oracle and I have some basic PHP code to help me connect to it:

<?php
if ($c=ora_logon("username@orcl","password")) {
echo "Successfully connected to Oracle.n";
ora_commitoff($c);
ora_logoff($c);
} else {
echo "Oracle Connect Error " . ora_error();
}
?>

This works - and connects. But I need some guidance in how I take what is put into the search box and turn that into an SQL query, then display the results.

As I have little PHP experience - do I declare the search box as a global variable? Can I use that variable in an SQL query?

Also, after each query, do I disconnect from the database?
 
Thanks for reply.

I checked out the links and looked into forms and while the syntax is confusing, I understand the logic.

Now to get to basics - all I want to do is to list everything that is stored within the database in a table. No forms - just an actual SQL query (SELECT * FROM). I found some code that should do this, however I can't get it to work. Obviously, this is using MySQL which I can't use, it needs to be Oracle. But I'm guessing there is a similar statement that takes advantage of Oracle?


Code: 
<?php
   $c=OCILogon("user", "password1", "orcl");
   if ( ! $c ) {
     echo "Unable to connect: " . var_dump( OCIError() );
     die();
   }
   
mysql_query("SELECT * FROM recipe");

?>

Top line connects to Oracle, and then the mysql_query should grab all entries from the database.

However, I can't find anything that is that simple to use as an Oracle command.

I want to keep it a very simple right now.
 
This is advanced stuff because Oracle is not like MySQL.

The most common setup for access Oracle via PHP is with OCI, i.e. you'll be using commands like these:

PHP: 
putenv("ORACLE_SID=xxxx"); // If needed, Apache
putenv("ORACLE_HOME=/path/to/oraclehome");  // If needed, Apache

$cn = ociplogon('username', 'password'); // login to Oracle
$st = ociparse($cn, "select Col2,Col2 from table"); // Parse the query
$ex = ociexecute($st,OCI_COMMIT_ON_SUCCESS); // Execute the query
$Rrows = ocifetchstatement($st, $results); // Get our results into an array

// Process each row of the result array
for ($i = 0; $i < $Rrows; $i++) {
     echo $results["Col1"][$i]; // Case sensitive key names -- important!!!!
     echo $results["Col2"][$i];
};

Of course don't run that code as is. It's just a highly simplified example of the Oracle commands and the order in which they should be used. Obviously customize, edit accordingly and add error checking on your own in your final version. The code above is self explanatory with the comments if you know how Oracle works. Please note php.net does a great job explaining each command, be sure to visit there, search for the Oracle functions in the code above and read all the comments for error trapping examples, usage, common pitfalls. I repeat, this is not like MySQL.

If your head is spinning still, please talk to your DBA and learn how Oracle works, i.e ask about the listener and OCI. You actually mixed MySQL and Oracle functions in your previous posts so I assume the learning curve is a little steep right now, heh.

-jim
 
Hi Jim,

Thanks for such a detailed reply. I tried your code, and while I understand most of it, I'm not too familiar with OCI in Oracle, so I guess I'll need to do some research on that.

PHP: 
<?php

$cn = ociplogon('username', 'password'); // login to Oracle
$st = ociparse($cn, "select id, name, ingredients, equipment, method, photo_id from recipe"); // Parse the query
$ex = ociexecute($st,OCI_COMMIT_ON_SUCCESS); // Execute the query
$Rrows = ocifetchstatement($st, $results); // Get our results into an array

// Process each row of the result array
for ($i = 0; $i < $Rrows; $i++) {
     echo $results["id"][$i]; // Case sensitive key names -- important!!!!
     echo $results["name"][$i];
	 echo $results["ingredients"][$i];
	 echo $results["equipment"][$i];
	 echo $results["method"][$i];
	 echo $results["photo_id"][$i];
};

?>

I amended it like above (I put in my username and password obviously) however I just get a blank page. I'm uploading this to my Uni server which supports PHP etc. Also, I'm using Dreamweaver to edit it - unfortunately I can't seem to find any debugging stuff built in so I don't know if there's any bugs.

Thanks.
 
There are a million reasons for a white screen - here is a few:

Assuming no typos resulting in a simple PHP fatal error maybe the output is just blank as you added no debug code to display variable values. It also could be the Oracle OCI client is not installed making all those OCI commands inactive and PHP throws errors. You also left off the putenv statements which might be necessary after all. You might even need a new datasource (connection string) based on your version of Oracle.

Example of all the procedures it takes for the most common setup, read this entire page from top to bottom.

Run phpinfo() to see the way PHP is setup for Oracle and compare that with the actual setup. Maybe you don't need OCI after all.

Get the idea - so many things can be wrong here. This is VERY advanced stuff for people who know how to compile PHP and experience with Oracle which has nothing to do with MySQL in support or setup.

If all else fails you, we need to know the following to assist you further:

1) Your web server type and version
2) Oracle version
3) PHP version and show us the phpinfo() dump (only the Oracle stuff)

-jim
 
1. Linux buddha 2.6.10-1.770_FC2custom #2 SMP (webserver?)

2. Oracle version 9.0

3. PHP Version 4.3.10

Oracle stuff:

Oracle Support enabled
Oracle Version 9.0
Compile-time ORACLE_HOME /usr/oracle
Libraries Used no value


Thanks again for help - I'm limited to what information I know about the Oracle setup.. I didn't set the server up, was set up already by the University.

I've done a select * from recipe; in SQL and there is data in the tables, so they aren't empty.

Cheers.

I added the putenv stuff:

PHP: 
<?php

putenv("ORACLE_SID=orcl"); // If needed, Apache
putenv("ORACLE_HOME=/usr/oracle");  // If needed, Apache
 
Okay, based on your very old version of PHP (4.3.10) and the info you provided, the Oracle extension you have is built into PHP4 - you are not using OCI8.

Here's what you need to know:

1) You can only use the commands on this page: http://phpbuilder.com/manual/en/ref.oracle.php
2) This extension has been moved to the PECL repository and is no longer bundled with PHP as of PHP 5.1.0.
3) If you upgrade to PHP5, it is suggested you also switch to OCI8 extensions (which means installation of the client on the server and recompiling PHP)

Bottom line is you're running very old stuff, Zend has deprecated "built in" Oracle support in future versions of PHP but you can still use commands like ora_login, etc. in 4.3.10.

So the following code is worth trying (untested sample code, i.e. same as before - edit based on your settings, use the link above to fix syntax issues):

PHP: 
<?php

$cn = ora_logon('username', 'password'); // login to Oracle
$st = ora_parse($cn, "select id, name, ingredients, equipment, method, photo_id from recipe"); // Parse the query
$ex = ora_exec($st); // Execute the query
$Rrows = ora_fetch_into($st, $results); // Get our results into an array

// Process each row of the result array
for ($i = 0; $i < $Rrows; $i++) {
     echo $results["id"][$i]; // Case sensitive key names -- important!!!!
     echo $results["name"][$i];
     echo $results["ingredients"][$i];
     echo $results["equipment"][$i];
     echo $results["method"][$i];
     echo $results["photo_id"][$i];
};

?>

I suspect the fatal errors were because you don't have OCI-8 compiled into PHP so all those functions didn't exist.

-jim
 
Hi Jim,

Thanks a lot for the help. I contacted tech support, and they reported back saying it is the OCI commands, and gave me some sample code to work with, which was basically the same as what you originally gave me.

This is what I have now:

PHP: 
<?php

$conn = OCILogon("username", "password", "orcl");

$statement = "select * from recipe";
$query =  OCIParse($conn, $statement);
$execute = OCIExecute($query);

$rows = OCIFetchStatement($query, $results);

for ($i = 0; $i < $rows; $i++) { 
     echo $results["id"][$i];
     echo $results["name"][$i]; 
     echo $results["ingredients"][$i]; 
     echo $results["equipment"][$i]; 
     echo $results["method"][$i]; 
     echo $results["photo_id"][$i]; 
};

?>

How do I test it for errors within Dreamweaver? I get a blank screen with that.

Thanks.
 
Prove to me PHP was compiled with OCI8 support, which means the instant client. Do a phpinfo() and see if "--with-oci8-instant-client" is found within the "Configure Command" section, and also further down there is a section called "OCI8" with status being enabled. This would explain fatal errors, as without the OCI being installed, those OCI functions don't exist.

As to debugging, this isn't app programming - just add in:

PHP: 
print "Hello World"; exit(0);

below a few lines to test if they're working, repeat until you find the error. Example, if you put that below line 4, run and see hello world, lines 1-3 are okay. Get the idea?

-jim
 
Hi Jim - thanks again for the help.

I spent an hour on it today, and discovered that the column names have to be in capital letters! As soon as I did that, everything worked.

I did notice you said it was case sensitive - even though the column ID's are all lowercase in Oracle, they need to be capital in PHP.

Thanks for your help though, you guided me well!
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back