Yet another MySQL abstraction layer...

[MikeSnead]

Been working on a new project that I want to make more re-usable and modular. Figured OOP was the natural way to go and that a database class would be a definite plus. I know these have been done thousands of times, but if you all would like to see how mine is currently set up here it is. I'd love to hear what you guys think or suggest I do differently. It is still early in development 🙂

It extends an error class, but you shouldn't need to see that... to get what's going on with it 🙂

<?php

/**
 * CLASS - "db_control"
 *
 * Generic MySQL interface class. Allows for all basic database controls
 * such as connect, query, and close. Complete list of properties and
 * methods are described below.
 *
 */

include_once('./include/class/error.class.php');

class db_control extends error
{
	var $db_connection;		// Tracks Database Connection
	var $db_name;			// Name of Database to be Used
	var $db_user;			// Username of Database User
	var $db_host;			// Database Host Name
	var $db_pass;			// User's Database Password
	var $db_result;			// Storage of Query Result Set
	var $db_row;			// Storage of Current Row from Result Set

function db_control()
{
	// Set Class Properties

	$this->db_host = '*******';
	$this->db_user = '*****';
	$this->db_name = '*****';
	$this->db_pass = '*****';

	// Set Initial Connection

	$this->db_connect();
}

function db_connect()
{
	// Connect to MySQL Database

	if ( !($this->db_connection = @mysql_connect($this->db_host,$this->db_user,$this->db_pass)) )
	{
		$this->error_code(101);
		return false; // Failed Database Connection
	}

	// Select Database

	if ( !@mysql_select_db($this->db_name,$this->db_connection) )
	{
		$this->error_code(102);
		return false; // Failed Database Selection
	}

	return true; // Connection & Selection Success
}

function db_close()
{
	// Close MySQL Database Connection, Clear Result and Connection Vars

	if ( !@mysql_close($this->db_connection) )
	{
		$this->error_code(103);
		return false; // Failed Connection Termination
	}
	else
	{
		unset ($this->db_connection);
		unset ($this->db_result);		
	}

	return true; // Connection Closed Successfully
}

function db_query( $SQL )
{
	// Execute Given MySQL Database Query

	if ( !($this->db_result = @mysql_query($SQL,$this->db_connection)) )
	{
		$this->error_code(104);
		return false; // Failed Query Execution
	}

	return true; // Query Executed Successfully
}

function db_pull_row()
{
	// Return Next Row From Result Set as Assoc. Array

	if ( !($this->db_row = @mysql_fetch_object($this->db_result)) )
	{
		return false; // No More Rows to Pull
	}

	return true;
}

function db_number_of_rows()
{
	// Return the number of rows within the Result property (SELECT only)

	if ( !($number_of_rows = @mysql_num_rows($this->db_result)) )
	{
		return false; // Failed Return Row Count
	}

	return $number_of_rows;
}

function db_affected_rows()
{
	// Return the number of affected rows (INSERT,UPDATE,DELETE only)

	if ( !($affected_rows = @mysql_affected_rows($this->db_result)) )
	{
		return false; // Failed Affected Row Count
	}

	return $affected_rows;
}

function db_last_insert_id()
{
	// Return the ID number of the last INSERT query (auto_increment field)

	if ( !($last_insert_id = @mysql_insert_id($this->db_connection)) )
	{
		$this->error_code(105);
		return false; // Unable to Grab Last Insert ID
	}

	return $last_insert_id;
}

function db_status()
{
	// Return a String with the Current Database Status

	return @mysql_stat($this->db_connection);
}
}

?>

[TimTimTimma]

just alittl suggestion but..

instead of..

function db_control() 
    { 
        // Set Class Properties 

    $this->db_host = '*******'; 
    $this->db_user = '*****'; 
    $this->db_name = '*****'; 
    $this->db_pass = '*****'; 

    // Set Initial Connection 

    $this->db_connect(); 
}

try this...

function db_control($dbhost, $dbuser, $dbname, $dbpass) 
    { 
        // Set Class Properties 

    $this->db_host = $dbhost; 
    $this->db_user = $dbuser; 
    $this->db_name = $dbname; 
    $this->db_pass = $dbpass; 

    // Set Initial Connection 

    $this->db_connect(); 
}

This allows for external file inclusion and makes it alittle more organized, this way if you decide to make this released under GNU/GPL other users wont have to search all through your code to find where they need to edit the info, they can simply open a file, and edit what is neded and not wory about anything else :-)

[MikeSnead]

Indeed! Thanks for the suggestion. I actually just changed that around a couple days ago. The values are now set via a config file that will be controlable through the CMS control panel... well once I get it built... 🙂

Thanks again for giving it a look over!

[mrhappiness]

• db_number_of_rows(), db_affected_rows(), db_last_insert_id()
  why don't you retrieve the values you return in this functions only once, e. g. in db_query()?
  as long as you don't perfrom a new query those values will remain the same, so there's no need to query them over and over again

• i'd like it better if db_pull_row would return either false/NULL (no row) or an array (the row itself)

• if you make your $db_result an array you can handle multiple queries

  function db_query($sql, $result = 'default') {
    $this->db_reesult[$result] = mysql_query($sql);
  }
  
  function db_pull_row($result = 'default') {
    $row = mysql_fetch_assoc($this->db_result[$result]);
    if (!is_array($row))
      return NULL;
    return $row;
  }
  $db->db_query('SELECT * FROM users', 'user');
  $db->db_query('SELECT * FROM hobbies', 'hobbies');
  while ($user = $db->db_pull_row('user')) {
    echo '<p><b>'.$user['name'].'</b><br />';
    while ($hobby = $db->db_pull_row('hobbies'))
      echo $hobby['hobby'].'<br />';
    mysql_data_seek($db->db_result['hobbies', 0);
    echo '</p>';
  }

  it's a somewhat useless quick'n'dirty code but i think you got the point

• you should not use ... extends error if the new class does not add specific functionality to the existing error class itself

  you used ... extends error to save time in typing 😉
  but i think it's better to bass an object of your error class as reference
  this way you would also be able to have one object of your error class containing all the errors that occured in the script like db, user management, file existance and permissions, ...

[Weedpacket]

Originally posted by mrhappiness

• you should not use ... extends error if the new class does not add specific functionality to the existing error class itself
  you used ... extends error to save time in typing 😉
  but i think it's better to bass an object of your error class as reference
  this way you would also be able to have one object of your error class containing all the errors that occured in the script like db, user management, file existance and permissions, ...

[/B]

I agree; this class doesn't really implement any sort of "error"; the extends look more like some kind of namespace hack. As for saving typing, if you do want to use $this->error_code(101) instead of $this->error_handler->error_code(101) (assuming you do want each db_control object to have its own error thingy), then a call() method may be more appropriate:

function __call($method, $a)
{ if(substr($method,0,6)=='error_') $this->error_object->$method($a);
}

(The code is probably wonky there, tbh.)

[MikeSnead]

Thanks guys... I shall definitely work on your suggestions. I especially like the result array concept.

I'm a little confused on why the class shouldn't extend my error class. Is what you're suggesting that I should instead create an error class object within the database class constructor?

[thorpe]

should instead create an error class object within the database class constructor

yes. this class doesnt really extend the error class at all, it merely uses it.

[Weedpacket]

What thorpe said 🙂

Consider what would happen if your class called on functionality provided by several other classes (not just error). Your class can only extend one other, so which should it be?

[MikeSnead]

Alrighty... I think I've got all the suggestions worked through and applied in a useful way... let know what you think now 🙂

Also, this version does not incorporate the configurable hostname, username, password, and dbname. That's still being worked out (original idea ran into some troubles) so I'm using 'hard coded' values for testing purposes.

Thanks in advance! 🙂

<?php

/**
 * CLASS - "db_control"
 *
 * Generic MySQL interface class. Allows for all basic database controls
 * such as connect, query, and close. Complete list of properties and
 * methods are described below.
 *
 */

include_once('./include/class/error.class.php');

class db_control
{
	var $db_connection;		// Tracks Database Connection
	var $db_name;			// Name of Database to be Used
	var $db_user;			// Username of Database User
	var $db_host;			// Database Host Name
	var $db_pass;			// User's Database Password
	var $db_result;			// Storage of Query Result Set (assoc array)
	var $db_last_insert_id;	// Storage of Last Insert ID (assoc array)
	var $db_num_of_rows;	// Number of rows in result set (assoc array)
	var $db_affected_rows;	// Number of rows affected in last query (assoc array)
	var $db_row;			// Storage of Current Row Object (assoc array)
	var $db_error;			// Error handler object

function db_control()
{
	// Set Class Connection Properties

	$this->db_host = '****';
	$this->db_user = '****';
	$this->db_name = '****';
	$this->db_pass = '****';

	// Construct Error Object

	$this->db_error = new error();

	// Set Initial Connection

	$this->db_connect();
}

function db_connect()
{
	// Connect to MySQL Database

	if ( !($this->db_connection = @mysql_connect($this->db_host,$this->db_user,$this->db_pass)) )
	{
		$this->db_error->error_code(101);
		return false; // Failed Database Connection
	}

	// Select Database

	if ( !@mysql_select_db($this->db_name,$this->db_connection) )
	{
		$this->db_error->error_code(102);
		return false; // Failed Database Selection
	}

	return true; // Connection & Selection Success
}

function db_close()
{
	// Close MySQL Database Connection, Clear Result and Connection Vars

	if ( !@mysql_close($this->db_connection) )
	{
		$this->db_error->error_code(103);
		return false; // Failed Connection Termination
	}
	else
	{
		unset ($this->db_connection);
		unset ($this->db_result);		
	}

	return true; // Connection Closed Successfully
}

function db_protect( $var )
{
	// Open Connection to MySQL if not already present

	if ( !isset($this->db_connection) )
	{
		$this->db_connect();
	}

	// Quote Variables for Safe SQL execution - Prevent Injection

	if ( get_magic_quotes_gpc() )
	{
		// Strip Slashes

		$var = stripslashes($var);
	}

	if ( !is_numeric($var) )
	{
		// Quote if not Numeric

		$var = "'" . @mysql_real_escape_string($var,$this->db_connection) . "'";
	}

	return $var;
}

function db_query( $SQL , $result = 'default' )
{
	// Open Connection to MySQL if not already present

	if ( !isset($this->db_connection) )
	{
		$this->db_connect();
	}

	// Execute Given MySQL Database Query

	if ( !($this->db_result[$result] = @mysql_query($SQL,$this->db_connection)) )
	{
		$this->db_error->error_code(104);
		return false; // Failed Query Execution
	}

	// Set Result Properties

	$this->db_num_of_rows[$result] = $this->db_number_of_rows($result);
	$this->db_affected_rows[$result] = $this->db_affected_rows($result);
	$this->db_last_insert_id[$result] = $this->db_last_insert_id();

	return true; // Query Executed Successfully
}

function db_pull_row( $result = 'default' )
{
	// Return Next Row From Result Set as an Object

	if ( !($this->db_row[$result] = @mysql_fetch_object($this->db_result[$result])) )
	{
		return false; // No More Rows to Pull
	}

	return true;
}

function db_number_of_rows( $result = 'default' )
{
	// Return the number of rows within the Result property (SELECT only)

	if ( !($number_of_rows = @mysql_num_rows($this->db_result[$result])) )
	{
		return false; // Failed Return Row Count
	}

	return $number_of_rows;
}

function db_affected_rows( $result = 'default' )
{
	// Return the number of affected rows (INSERT,UPDATE,DELETE only)

	if ( !($affected_rows = @mysql_affected_rows($this->db_result[$result])) )
	{
		return false; // Failed Affected Row Count
	}

	return $affected_rows;
}

function db_last_insert_id()
{
	// Return the ID number of the last INSERT query (auto_increment field)

	if ( !($last_insert_id = @mysql_insert_id($this->db_connection)) )
	{
		return false; // Unable to Grab Last Insert ID
	}

	return $last_insert_id;
}

function db_status()
{
	// Return a String with the Current Database Status

	return @mysql_stat($this->db_connection);
}
}

?>