Section 12th using PHP to operate Oracle databases

Use PHP to operate Oracle Database 11. database connection in the previous section, we have introduced some basic operation knowledge about PHP and MySQL databases, and the most relevant PHP and MySQL tutorials on the Internet. MySQL is free, which may attract many people. Because of its wide application, I don't want to repeat MySQL usage here.

UsePHPTo operateOracleDatabase

11.Database Connection

In the previous section, we have introducedPHPAndMySQLSome basic database operation knowledge, related to the InternetPHPAndMySQL.MySQLIt is free, which may attract a lot of people. Because of its wide application, I don't want to repeat it hereMySQL.OracleIs widely used in enterprise applications, so we useOracleTo further introducePHPConnection to the database. Of course we won't mentionOracleThe database design principle is that this is beyond our scope of discussion.

　　PHPProvides two functions andOracleConnection, which isORA _AndOCIFunction. WhereORA _The function is somewhat outdated.OCIFunction updates are said to be better. The syntax is almost the same. As mentioned above, yourPHPThe installation options should support both.
For more information aboutMicrosoft WindowsPlatform installation supportPHP3OfApacheServer knowledge and moreOracleFor database knowledge, refer to the followingURL:Www.csoft.net /~ Vsbabu/articles/oraphp.html.

11.1Connection

If($Conn=Ora_Logon("User @ TNSNAME "," password"))
{
Echo"SUCCESS! Connected to databaseN ";
}
Else
{
Echo"Failed:-(cocould not connect to databaseN ";
}
Ora_Logoff($Conn);
Phpinfo();
?>
Use the above codeTNSNAME(In yourTnsnames. oraSpecified in the file)OracleDatabase name, user name, and password. Based on successful connections,Ora_logonFunction returnA non-zeroConnectionIDAnd stored in the variable$Conn.

11.2Query

If the connection to the database is ready, we will query the database using the actual application. The following code demonstrates a typical connection and Query example:
/*
*Connect to the database and perform query
*/
FunctionPrintoraerr($In_cur)
{
//CheckOracleError?
//If an error exists, it is displayed.
//Every request when the pointer is activatedOracleAnd then call this function.
If (Ora_errorcode($In_cur))
Echo "Oracle code -".Ora_error($In_cur). "N ";
Return;
}
/**Main program*/
If (! ($Conn=Ora_logon("User @ TNSNAME "," password")))
{
Echo "Connection to databaseFailedn";
Exit;
}
Echo "Connected as connection-$Conn<Br> N ";
Echo "Opening cursor... <Br> N ";
$ Cursor =Ora_open($Conn);Printoraerr($ Cursor );
Echo "Opened cursor-$ Cursor<Br> N ";
$Qry= "SelectUser, sysdateFrom dual ";
Echo "Parsing the query$Qry... <Br> N ";
Ora_parse($ Cursor, $ qry, 0 );Printoraerr($ Cursor );
Echo "Query parsed <Br> N ";
Echo "Executing cursor... <Br> N ";
Ora_exec($ Cursor );Printoraerr($ Cursor );
Echo "Executed cursor <Br> N ";
Echo "Fetching cursor... <Br> N ";
While (Ora_fetch($ Cursor ))
{
$ User =Ora_getcolumn($ Cursor, 0 );Printoraerr($ Cursor );
$Sysdate=Ora_getcolumn($ Cursor, 1 );Printoraerr($ Cursor );
Echo "row =$ User, $Sysdate <Br> N ";
}
Echo "Fetched all records <Br> N ";
Echo "Closing cursor... <Br> N ";
Ora_close($ Cursor );
Echo "Closed cursor <Br> N ";
Echo "Logging off from oracle... <Br> N ";
Ora_logoff($Conn);
Echo "Logged off from oracle <Br> N ";
?>
Note: The above code segment lacks comments. For more information, seePHP ManualOfOracleDatabase functions)

11.3Display result

The following code describes how to query a database and output the result:
FunctionPrintoraerr($In_cur, $Conn)
{
//CheckOracleError?
//If an error exists, it is displayed.
//Every request when the pointer is activatedOracleAnd then call this function.
// If it encountered an error, we exit immediately
If (Ora_errorcode($In_cur))
{
Echo "Oracle code -".Ora_error($In_cur). "<Br> N ";
Ora_logoff($Conn);
Exit;
}
Return;
}

FunctionExequery($W_qry, $ conn)
{
$ Cursor =Ora_open($Conn);Printoraerr($Cursor, $ conn);
Ora_parse($ Cursor, $ w_qry, 0 );Printoraerr($Cursor, $ conn);
Ora_exec($ Cursor );Printoraerr($Cursor, $ conn);
$Numrows= 0;
$W_numcols=Ora_numcols($ Cursor );
//Show header
Echo"

N ";
For ($I= 0; $ I <$W_numcols; $ I++)
{
$ Align = (Ora_columntype($Cursor, $ I) = "NUMBER ")? "RIGHT": "LEFT";
Echo "t N ";
}
Echo" N ";
While (Ora_fetch($ Cursor ))
{
Echo" N ";
For ($I= 0; $ I <$W_numcols; $ I++)
{
$ Align = (Ora_columntype($Cursor, $ I) = "NUMBER ")? "RIGHT": "LEFT";
If (Ora_columntype($Cursor, $ I) = "LONG ")
Echo" N";
Else
Echo" N ";
Printoraerr($Cursor, $ conn);
}
$Numrows++;
Echo" N ";
}
If ($Numrows= 0)
Echo" N ";
Else
{
Echo" N ";
Echo" N ";
Echo" N ";
Echo" N ";
}
Echo"

".Ora_columnname($Cursor, $ I)."
". ora_getcolumn($cursor,$i)."	".Ora_getcolumn($Cursor, $ I)."
Query returned no records
Count	$Numrows

N ";
Ora_close($ Cursor );
Return;
}

//Main program
If (! ($Conn=Ora_logon("User @ SID "," password")))
{
Echo "Error: Cannot connectDatabasen";
Exit;
}
$Qry= "SELECT
Deptno"Dept"
,Empno"Emp"
,Empnm"Name"
, Salary "Salary"
FROM
Employee
Order by 1, 2 ";
Exequery($Qry);
Ora_logoff($Conn);
?>
Note: The above code segment lacks comments. For more information, seePHP ManualOfOracleDatabase functions)

11.4Based onHTTPOfOracleLogin

Add the following codePHPConfirm before page codeOracleLog on. Note that you must set it correctly$ SID.
If (!Isset($ PHP_AUTH_USER ))
{
Header ("WWW-authenticate: basic realm =" $ SID "");
Header ("HTTP/1.0 401 Unauthorized ");
$ Title = "Login Instructions ";
Echo "<Blockquote>
You are not authorized to enter the site
Blockquote> N ";
Exit;
}
Else
{
If (! ($Conn=Ora_logon("$ PHP_AUTH_USER @ $ SID", $ PHP_AUTH_PW )))
{
Header ("WWW-authenticate: basic realm =" $ SID "");
Header ("HTTP/1.0 401 Unauthorized ");
$ Title = "Login Instructions ";
Echo "<Blockquote>
You are notAuthorisedTo enter the site
Blockquote> N ";
Exit;
}
}
?>