Building a DB Class - Codewalkers

Codewalkers Forums Sponsor:

February 4th, 2004, 09:22 PM

al042077

Join Date: Apr 2007

Location: Omaha, NE

Posts: 23

Time spent in forums: 13 sec
Reputation Power: 0

Building a DB Class

As a means to be more efficient as well as learning more about OO/class design and use in PHP, I'm working on a database class. The idea is that I could change one line in my settings file to set which database to use.

php Code:

Original - php Code
$DB_TYPE='mysql'

$DB_TYPE='mysql'

I then name each respective class the same and create objects by:

php Code:

Original - php Code
$db = new $DB_TYPE;

$db = new $DB_TYPE;

It seems to be connecting fine, but I'm having a lot of trouble with the queries. I'm trying to create a function to run a SELECT query and return the results to a class variable according to whether I wanted a numerical index, associative, or as objects. Here are the three files:

php Code:

Original - php Code

----test_settings.php----
<?php
$DB_TYPE = 'mysql';
$DB_HOST = 'localhost';
$DB_DATABASE = 'test';
$DB_USERNAME = 'user';
$DB_PASSWORD = 'pass';
?>

----test.php----
<html>
<head>
    <title>TEST</title>
</head>
<body>
<?php
require_once('test_settings.php');
require_once("_classes/class_$DB_TYPE.php");
$db = new $DB_TYPE("$DB_HOST","$DB_DATABASE","$DB_USERNAME","$DB_PASSWORD");
$db->setstrQuery("SELECT host,user,password FROM user");
$db->strSelect();
while($db->rsNumArray) {
	echo $db->rsNumArray[0] ." : ". $db->rsNumArray[1] ."<br />";
}
?>
</body>
</html>

----_classes/class_mysql.php---
<?php
/*#################################################  ############################
This class manages a database instance which handles all database operations.
It is currently configured for a MySQL 4.x database.
Need to first include a configuration file with connection information.
  $DB_HOST --> host name or address on which to connect
  $DB_DATABASE --> name of database to connect to
  $DB_USERNAME --> username to connect with
  $DB_PASSWORD --> password to connect with
*/
class mysql {
    var $hostConn = -1;
	var $databaseConn = -1;
	var $strQuery = '';    // string for the query
	var $rsPref = 0;    // indicates how to return rs
    var $rs;
    var $rsNumArray;
	var $rsAssocArray;
	var $rsObject;
    var $rsNumRows = -1;    // number of rows returned
    var $rsAffRows = -1;    // number of rows affected/inserted
/*#################################################  ############################
mysql Constructor
Establishes connection to server and database.
*/
    function mysql($h,$d,$u,$p) {
		$this->hostConnect($h,$u,$p);
		$this->databaseConnect($d);
	}
/*#################################################  ############################
Connect to host server
*/
    function hostConnect($h,$u,$p) {
		$this->hostConn = mysql_connect($h,$u,$p) or die("Could not connect to host.");
	}
/*#################################################  ############################
Connect to database
*/
    function databaseConnect($d) {
		$this->databaseConn = mysql_select_db($d) or die("Could not connect to database.");
	}
/*#################################################  ############################
Prepare a string for a query.
*/
    function strPrepare() {
        if (!get_magic_quotes_gpc() )
		    $this->strQuery = mysql_escape_string($this->strQuery);
	}
/*#################################################  ############################
Run SELECT query.
*/
    function strSelect() {
        if (!$this->strQuery) {
            return 0;
	}
		$this->rs = mysql_query($this->strQuery) or die(mysql_error() );
        switch ($this->rsPref) {
            // Numerical Index, returns items as $row[0]
            case "0" :
                $this->rsNumArray = mysql_fetch_row($this->rs);
                break;
            // Associative Index, returns items as $row['column']
            case "1" :
                $this->rsAssocArray = mysql_fetch_array($this->rs);
                break;
            // Objects
            case "2" :
                $this->rsObject = mysql_fetch_object($this->rs);
                break;
            default :
                break;
        }
        $this->rsNumRows = mysql_fetch_rows($this->rs);
	}
/*#################################################  ############################
ACCESSOR METHODS
*/
    function getstrQuery() {
		return $this->strQuery;
	}
    function setstrQuery($strQuery) {
		$this->strQuery = $strQuery;
	}
}
?>

----test_settings.php----
<?php
$DB_TYPE = 'mysql';
$DB_HOST = 'localhost';
$DB_DATABASE = 'test';
$DB_USERNAME = 'user';
$DB_PASSWORD = 'pass';
?>
 
----test.php----
<html>
<head>
    <title>TEST</title>
</head>
<body>
<?php
require_once('test_settings.php');
require_once("_classes/class_$DB_TYPE.php");
$db = new $DB_TYPE("$DB_HOST","$DB_DATABASE","$DB_USERNAME","$DB_PASSWORD");
$db->setstrQuery("SELECT host,user,password FROM user");
$db->strSelect();
while($db->rsNumArray) {
    echo $db->rsNumArray[0] ." : ". $db->rsNumArray[1] ."<br />";
}
?>
</body>
</html>
 
----_classes/class_mysql.php---
<?php
/*#################################################  ############################
This class manages a database instance which handles all database operations.
It is currently configured for a MySQL 4.x database.
Need to first include a configuration file with connection information.
  $DB_HOST --> host name or address on which to connect
  $DB_DATABASE --> name of database to connect to
  $DB_USERNAME --> username to connect with
  $DB_PASSWORD --> password to connect with
*/
class mysql {
    var $hostConn = -1;
    var $databaseConn = -1;
    var $strQuery = '';    // string for the query
    var $rsPref = 0;    // indicates how to return rs
    var $rs;
    var $rsNumArray;
    var $rsAssocArray;
    var $rsObject;
    var $rsNumRows = -1;    // number of rows returned
    var $rsAffRows = -1;    // number of rows affected/inserted
/*#################################################  ############################
mysql Constructor
Establishes connection to server and database.
*/
    function mysql($h,$d,$u,$p) {
        $this->hostConnect($h,$u,$p);
        $this->databaseConnect($d);
    }
/*#################################################  ############################
Connect to host server
*/
    function hostConnect($h,$u,$p) {
        $this->hostConn = mysql_connect($h,$u,$p) or die("Could not connect to host.");
    }
/*#################################################  ############################
Connect to database
*/
    function databaseConnect($d) {
        $this->databaseConn = mysql_select_db($d) or die("Could not connect to database.");
    }
/*#################################################  ############################
Prepare a string for a query.
*/
    function strPrepare() {
        if (!get_magic_quotes_gpc() )
            $this->strQuery = mysql_escape_string($this->strQuery);
    }
/*#################################################  ############################
Run SELECT query.
*/
    function strSelect() {
        if (!$this->strQuery) {
            return 0;
    }
        $this->rs = mysql_query($this->strQuery) or die(mysql_error() );
        switch ($this->rsPref) {
            // Numerical Index, returns items as $row[0]
            case "0" :
                $this->rsNumArray = mysql_fetch_row($this->rs);
                break;
            // Associative Index, returns items as $row['column']
            case "1" :
                $this->rsAssocArray = mysql_fetch_array($this->rs);
                break;
            // Objects
            case "2" :
                $this->rsObject = mysql_fetch_object($this->rs);
                break;
            default :
                break;
        }
        $this->rsNumRows = mysql_fetch_rows($this->rs);
    }
/*#################################################  ############################
ACCESSOR METHODS
*/
    function getstrQuery() {
        return $this->strQuery;
    }
    function setstrQuery($strQuery) {
        $this->strQuery = $strQuery;
    }
}
?>

So, once I run strSelect(), how would I reference the results?

February 5th, 2004, 03:33 AM

al042077

Join Date: Apr 2007

Location: Omaha, NE

Posts: 23

Time spent in forums: 13 sec
Reputation Power: 0

RE: Building a DB Class

I'm pretty cool... or at least for 30 seconds more, before I hit another jam.
I figured out that deal. In case anyone else wants to use this:
I made the query kick teh results to $this->rs, but kept the mysql_fetch_array() outside of the class.

If anyone knows a way to put that inside the class, please let me know. Thanks!!

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: