Home > Article > Backend Development > PHP database connection_PHP tutorial
PHP database connection_PHP tutorial
- WBOYOriginal
- 2016-07-21 16:05:53680browse
With PHP you can easily connect to the database, request data and display it on your web site, and even modify data in the database. MySQL is a very popular database, and there are many tutorials on PHP and MySQL on the Internet. MySQL is free, which may attract many people. Due to its wide application, I don't want to go into details about the use of MySQL here. Oracle is widely used in enterprise applications, so we will use Oracle to introduce the connection between PHP and database. We will certainly not mention the design principles of the Oracle database, as this is beyond the scope of our discussion.
PHP provides two sets of functions to connect to Oracle, namely ORA_ and OCI functions. The ORA_ function is slightly outdated. The OCI function update is said to be better. The usage syntax of the two is almost the same. As mentioned before, your PHP installation options should support the use of both.
For more knowledge about installing an Apache server supporting PHP3 on Microsoft Windows platform and more knowledge about Oracle database, please check the following URL: www.csoft.net/~vsbabu/articles/oraphp.html.
4.1 Connection
if ($conn=Ora_Logon("user@TNSNAME","password"))
{
echo "SUCCESS ! Connected to databasen";
}
else
{
echo "Failed :-( Could not connect to databasen";
}
Ora_Logoff($conn);
The above code connects to the database using the Oracle database name, username and password defined by TNSNAME (specified in your tnsnames.ora file). On the basis of a successful connection, the ora_logon function returns a non-zero connection ID and stores it in the variable. $conn.
4.2 Query
Assuming that the connection to the database is ready, let’s actually apply the query to the database.The following code demonstrates a typical example of connecting and querying:
/*
* Connecting to the database and executing the query
*/
function printoraerr($in_cur)
{
// Check if Oracle has an error
// If there is an error, it will be displayed
// When the pointer is activated, call this function after each request for Oracle
if(ora_errorcode($in_cur))
echo "Oracle code - ".ora_error($in_cur)."n";
return;
}
/**main program*/
if (!($conn=ora_logon("user@TNSNAME" ,"password"))) >echo "Opening cursor ...
n";
$cursor=ora_open($conn); printoraerr($cursor);
echo "Opened cursor - $cursor
n";
$qry="select user,sysdate from dual";
echo "Parsing the query $qry ...
n";
ora_parse($cursor,$qry,0); printoraerr($cursor );
echo "Query parsed
n";
echo "Executing cursor ...
n";
ora_exec($cursor); printoraerr($cursor);
echo "Executed cursor
n";
echo "Fetching cursor ...
n";
while(ora_fetch($cursor))
{
$user=ora_getcolumn($cursor ,0); printoraerr($cursor);
$sysdate=ora_getcolumn($cursor,1); printoraerr($cursor);
echo " row = $user, $sysdate
n";
}
echo "Fetched all records
n";
echo "Closing cursor ...
n";
ora_close($cursor);
echo "Closed cursor
n";
echo "Logging off from oracle...
n";
ora_logoff($conn);
echo "Logged off from oracle
n"; >
(Translator’s Note: The above code segment lacks comments, please refer to the Oracle database function section of PHP Manual)
4.3 Display results
The following code demonstrates how to query the database and Output the result:
function printoraerr($in_cur, $conn)
{
// Check whether Oracle has an error
// If there is an error, it will be displayed
// Every time when the pointer is activated This function is called after requesting Oracle once
// If it encountered an error, we exit immediately
if(ora_errorcode($in_cur))
{
echo "Oracle code - ".ora_error($in_cur) )."
n";
ora_logoff($conn);
exit;
}
return;
}
function exequery($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);
// 显示头部
echo "
n";
for ($i=0;$i<$w_numcols;$i++)
{
$align=(ora_columntype($cursor,$i)=="NUMBER")?"RIGHT":"LEFT";
echo "t ".ora_columnname($cursor,$i)." 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 " ".
ora_getcolumn($cursor,$i)."
n";
else
echo " ".ora_getcolumn($cursor,$i)." n";
printoraerr($cursor,$conn);
}
$numrows++;
echo "
n";
}
if ($numrows==0)
echo " Query returned no records
n";
else
{
echo " n";
echo " Count n";
echo " $numrows n";
echo "
n";
}
echo " n";
ora_close($cursor);
return;
}
// 主程序
if(!($conn=ora_logon("user@SID","password")))
{
echo "Error: Cannot connect to databasen";
exit;
}
$qry="SELECT
deptno "Dept"
,empno "Emp"
,empnm "Name"
,salary "Salary"
FROM
employee
ORDER BY 1,2";
exequery($qry);
ora_logoff($conn);
?>
（译者注：以上代码段缺少注释，请读者参考PHP Manual的Oracle数据库函数部分）
4.4 基于HTTP的Oracle登录
将以下代码加在PHP页面代码之前以确认Oracle登录。注意你必须正确设定$ SID。
if(!isset($PHP_AUTH_USER))
{
Header("WWW-authenticate: basic realm="$SID"");
Header("HTTP/1.0 401 Unauthorized");
$title="Login Instructions";
echo "
You are not authorized to enter the site
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 "
You are not authorised to enter the site
n";
exit;
}
}
?>