Using PHP and Oracle

arnyinc · 08-26-2003, 09:23 PM

I have php and apache installed on one server (windows 2000). We have a shared oracle database on another server (unix). I can't figure out how to query the database and just get some test data to reply back.

Any tips? For starters, I don't understand the difference between the modules php_oci8.dll and php_oracle.dll so I don't know which one to use. I tried both of them at separate times and still couldn't get a response.

zeon · 08-27-2003, 10:55 AM

If i'm not mistaking myself, there's a whole section on PHP-functionality with Oracle in the PHP Manual...

arnyinc · 08-28-2003, 04:57 PM

I learn by example and the combination of php and oracle is not as common as things like mysql and php or asp and access. So it was quite difficult to track down any code. I think the complete lack of responses on this board proves that quite well (unless everyone thought I was just lazy due to your comment)

I eventually found some help and ended up with a very basic query like this. Hopefully someone else finds it useful.

<html>
<body>
<?
$db = "servicename";
$conn = OCILogon("fakeuser","fakepass", $db);

$stmt = OCIParse($conn,"select id, name from test order by id");

OCIDefinebyname($stmt,"ID",$yar);
OCIDefinebyname($stmt,"NAME", $buh);

OCIExecute($stmt);

while (OCIFetch($stmt)) {
echo "ID:".$yar."\n<br>";
echo "Name:".$buh."\n<br><br>";
}

OCIFreestatement($stmt);
OCILogoff($conn);
?>

</body>
</html>

bcarl314 · 08-28-2003, 05:23 PM

Thanks, I haven't used Oracle w php yet, but I know I will in the future. A copmany I work for is migrating from Unix / PERL / DB2 to Windows / ASP or JSP or PHP / SQL Server or Oracle.

So, since I have no interest in learning ASP, I'll probably go with the PHP / SQL Server or PHP / Oracle combo.

I have used PERL with oracle, see code below, it may help.

Code:

$authVar=0;
my $uname="";
my $pword="";
my $setCookie="no";
sub checkUser( $$ ) {
	$un = shift;
	$pwd = shift;
	$setCookie = shift;
	$dbh = DBI->connect("DBI:Oracle:host=$dbHost;sid=$dbSID;port=$dbPort","$dbUserName","$dbPwd", { RaiseError => 0, AutoCommit => 0}) or die (print "Location: http://localhost/loginErrDB.html\n\n");
	$sql = "select * from users where USERNAME=\'$un\'";
	$cursor = $dbh->prepare ("$sql");
	$cursor->execute();
	my $totalRes=0;
	my $passWord;
###Verify that there is only one user name match
	while ( @row = $cursor->fetchrow ) {
		$totalRes++;
		$passWord=$row[1];
	}
###If only one match, check to make sure the password is valid.
	if($totalRes==1) {
###Check password
###May need to add chomp on UNIX!!!
		if($pwd eq $passWord) {
###update lastlog
			$now = localtime();
			$month =substr($now,4,3);
			$day = substr($now,8,2);
			$year = substr($now,20,4);
			$oracleDate="$day-$month-$year";
			$logSQL="update users set lastlog=SYSDATE where username=\'".$un."\'";
			$cursor = $dbh->prepare ("$logSQL");
			$cursor->execute();
	
### SET USER NAME AND PASSWORD COOKIE
			my $val = $un . ':' . $passWord;
			my $c = new CGI::Cookie(-name=>"usr",-value=>"$val",-expires=>"+20s");
			print "Set-Cookie: ",$c->as_string,"\n";
			print "Content-type: text/html\n";
			print "\n";	
			$authVar=1;
			
		}
		else {
			$authVar=0;
### GO TO DESTROY COOKIE
		}
	}
	else {
		$authVar=0;
	}
	$cursor->finish;
	$dbh->disconnect;
}
sub getCookie() {
	#my $query = new CGI;
	#my $cookie_in = $query->cookie("usr");
	#if($cookie_in)
	%cookies = fetch CGI::Cookie;
	$cookie = $cookies{'usr'}->value;
	($uname, $pword) = split(/:/, $cookie, 2);
	&checkUser( $uname, $pword, 'no' );
}
sub verifyUser() {
	if ($ENV{'REQUEST_METHOD'} eq 'POST') {
		&Parse_Form();
		$uname=$formdata{'userName'};
		$pword=$formdata{'p'};
		&checkUser( $uname, $pword, 'yes');
	}
	elsif ($ENV{'HTTP_COOKIE'}) {
		&getCookie();

	}
	else {
		$authVar=0;
	}
}
1;

Or maybe not. PERL w/ Oracle is not too common either.