4. Connection to the database

4. Database Connection


With PHP, you can easily connect to the database, request data and display it on your web site, or 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. Because of its wide application, I don't want to repeat MySQL usage here. Oracle is widely used in enterprise applications, so we use Oracle to introduce the connection between PHP and database. Of course we will not mention the design principles of Oracle databases, because this is beyond our scope of discussion.

PHP provides two sets of functions connected to Oracle: ORA _ and OCI. The ORA _ function is somewhat outdated. OCI function updates are said to be better. The syntax is almost the same. As mentioned above, Your PHP installation options should support both.

For more information about installing an Apache server that supports PHP3 on Microsoft Windows and about Oracle databases, see the following URL: www.csoft.net /~ Vsbabu/articles/oraphp.html.



4.1 connection



<?

If ($ conn = Ora_Logon ("user @ TNSNAME", "password "))

{

Echo "<B> SUCCESS! Connected to database <B> \ n ";

}

Else

{

Echo "<B> Failed:-(cocould not connect to database <B> \ n ";

}

Ora_Logoff ($ conn );

Phpinfo ();

?>

The above Code uses the Oracle database name, user name, and password defined by TNSNAME (specified in your tnsnames. ora file) to connect to the database. Based on successful connections, the ora_logon function returns a non-zero connection ID and stores it in the variable $ conn.



4.2 Query



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

*/

Function printoraerr ($ in_cur)

{

// Check for Oracle errors

// If an error exists, it is displayed.

// This function is called after each request to Oracle when the pointer is activated

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 database failed \ n ";

Exit;

}

Echo "Connected as connection-<B> $ conn </B> <br> \ n ";

Echo "Opening cursor... <br> \ n ";

$ Cursor = ora_open ($ conn); printoraerr ($ cursor );

Echo "Opened cursor-<B> $ cursor </B> <br> \ n ";

$ Qry = "select user, sysdate from dual ";

Echo "Parsing the query <B> $ qry </B>... <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 = <B> $ user, $ sysdate </B> <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, see the Oracle database function section of PHP Manual)



4.3 Display Results



The following code describes how to query a database and output the result:

<?

Function printoraerr ($ in_cur, $ conn)

{

// Check for Oracle errors

// If an error exists, it is displayed.

// This function is called after each request to Oracle when the pointer is activated

// 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;

}



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 );

// Display the header

Echo"

<Table width = "100% \" BORDER = \ "0 \" CELLSPACING = \ "1 \"
CELLPADDING = \ "2 \">

<TR> \ n ";

For ($ I = 0; $ I <$ w_numcols; $ I ++)

{

$ Align = (ora_columntype ($ cursor, $ I) = "NUMBER ")? "RIGHT": "LEFT ";

Echo "\ t <th valign = top align = $ align>". ora_columnname ($ cursor, $ I). "</TH> \ n ";

}

Echo "</TR> \ n ";

While (ora_fetch ($ cursor ))

{

Echo "<TR> \ n ";

For ($ I = 0; $ I <$ w_numcols; $ I ++)

{

$ Align = (ora_columntype ($ cursor, $ I) = "NUMBER ")? "RIGHT": "LEFT ";

If (ora_columntype ($ cursor, $ I) = "LONG ")

Echo "<td valign = top align = $ align> <PRE> ".

Ora_getcolumn ($ cursor, $ I). "</PRE> </TD> \ n ";

Else

Echo "<td valign = top align = $ align>". ora_getcolumn ($ cursor, $ I). "</TD> \ n ";

Printoraerr ($ cursor, $ conn );

}

$ Numrows ++;

Echo "</TR> \ n ";

}

If ($ numrows = 0)

Echo "<TR> <td colspan = \" $ w_numcols \ "> <B> Query
Returned no records

</B> </TD> </TR> \ n ";

Else

{

Echo "<TR> \ n ";

Echo "<th colspan = \" ". ($ w_numcols-1)." \ "ALIGN = RIGHT> Count </TH> \ n ";

Echo "<th align = RIGHT> $ numrows </TH> \ n ";

Echo "</TR> \ n ";

}

Echo "</TABLE> \ n ";

Ora_close ($ cursor );

Return;

}



// Main program

If (! ($ Conn = ora_logon ("user @ SID", "password ")))

{

Echo "Error: Cannot connect to database \ n ";

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, see the Oracle database function section of PHP Manual)



4.4 HTTP-based Oracle Login



Add the following code before the PHP page code to confirm Oracle login. Note that you must set $ SID correctly.

<?

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 not authorised to enter the site

</Blockquote> \ n ";

Exit;

}

}

?>