Advanced PHP and ODBC/MySQL Database Connection
28-February 2006.
3,950
0 Sometime ago I had posted a tutorial on Connecting PHP and MySQL. Well, today let expand that tutorial to something more challenging. Wouldn't it be nice if you can have the database connection support varies database other than just to depend on PHP's mysql_connect() all alone.
ODBC connection is one of the essential database connection method available nowadays and PHP support this type of database connection other than MySQL direct forward connection. So are we going to change to PHP's odbc_connect() or are we going to use both of them.
Why don't we use both? All you need is just a configuration file to store general information on database connection such as database host, database data source name, database username, database password and database name. Let start with that in dbconfig.php.
<?php
$info["DBCONN"] = "odbc"; // Database connection type to be used (Select between 'mysql' or 'odbc').
$info["DBUSER"] = "root"; // Database user.
$info["DBPWD"] = ""; // Database password (only IF needed).
$info["DBHOST"] = "localhost"; // Database hostname or IP address (for MySQL direct connection).
$info["DB"] = "abc"; // Database database in use (for MySQL direct connection).
$info["DBDS"] = "abc.mysql"; // Database Data Source (for ODBC connection).
?>
Make sure all information are insert correctly depending on your database configuration. For those who intend to test your application in localhost you might need to download MySQL Connector/ODBC before proceeding.
Next let see what we need to connect using PHP's mysql_connect function. Let use mysql-connect.php
<?php
class DBConn {
function DBConn() {
global $info;
// Establish general database connection.
mysql_connect($info["DBHOST"], $info["DBUSER"], $info["DBPWD"]) or die($this->db_error(mysql_error()));
// Choose the database in use.
mysql_select_db($info["DB"]) or die($this->db_error(mysql_error()));
}
function run_query($x) {
// Run query command.
$y = mysql_query($x) or die($this->db_error(mysql_error()));
return $y;
}
function total_row($result) {
// Count total result (total rows).
$x = mysql_num_rows($result) or die($this->db_error(mysql_error()));
return $x;
}
function get_array($result) {
$row = mysql_fetch_array($result);
return $row;
}
function get_object($result) {
$row = mysql_fetch_object($result);
return $row;
}
function db_error($the_error) {
// Display error notification (if there any).
print "<html>
<head>
<style type='text/css'>
<!--
body {
font-family:Tahoma;
font-size:0.8em;
color:#333;
}
-->
</style>
<title>ChronoSight.CMS Database Error Notification</title>
</head>
<body>
Sorry for the interuption! It seem that there an error.<br />
".$the_error."<br />
Try refresh <a href='".$_SERVER['PHP_SELF']."'>this page</a> again.
</html>";
}
}
?>
All we need is PHP's odbc_connect() function inside odbc-connect.php. See the different between both filename will determine with connection will be executed when the system run (depend on $info["DBCONN"])
<?php
class DBConn {
var $connection;
function DBConn() {
global $info;
// Establish general database connection.
$this->connection = odbc_connect($info["DBDS"], $info["DBUSER"], $info["DBPWD"]) or die($this->db_error(odbc_error()));
}
function run_query($x) {
// Run query command.
$y = odbc_exec($this->connection, $x) or die($this->db_error(odbc_error()));
return $y;
}
function total_row($result) {
// Count total result (total rows).
$x = odbc_num_rows($result) or die($this->db_error(odbc_error()));
return $x;
}
function get_array($result) {
$row = odbc_fetch_array($result);
return $row;
}
function get_object($result) {
$row = odbc_fetch_object($result);
return $row;
}
function db_error($the_error) {
// Display error notification (if there any).
print "<html>
<head>
<style type='text/css'>
<!--
body {
font-family:Tahoma;
font-size:0.8em;
color:#333;
}
-->
</style>
<title>ChronoSight.CMS Database Error Notification</title>
</head>
<body>
Sorry for the interuption! It seem that there an error.<br />
".$the_error."<br />
Try refresh <a href='".$_SERVER['PHP_SELF']."'>this page</a> again.
</html>";
}
}
?>
So how do we exactly run this script. Inside the main file such as index.php you will need to add this following code.
<?php
require $loc["INCLUDE"]."config.php";
include $loc["INCLUDE"].$info["DBCONN"]."-connect.php";
include $loc["INCLUDE"]."function.php";
// Declare BD and Func OOP object.
$GlobalDB = new DBConn();
?>
Let say you need to display total rows inside table users, what you need is...
<?php
$result = $GlobalDB->run_query("SELECT count(*) FROM users");
$row = $GlobalDB->get_array($result);
print $row[0];
?>
