building a mysql abstraction layer

[TimTimTimma]

I am looking to make it alittle more functional aswell as fast any ideas?

Criticism welcome.

<?php
/****************************************************************************
//		Title       : TimTimTimma's mysql_abstraction_layer class
//		Version     : 2.0
//		PHP         : X =< 4.4
//		Description : A functional mysql class that executes sql easily
/***************************************************************************/

class mysql_abstraction_layer
{
	var $database_host;
	var $database_name;
	var $database_username;
	var $database_password;
	var $mysql_connection;
	var $mysql_select_database;
	var $process;
	var $delete;
	var $update;
	var $insert;
	var $parse;
	var $affected_rows;
	var $affected_rows_total;
	var $query_count;
	var $query_type;
	var $query_type2;
	var $limit;
	var $order_how;
	var $order_how2;

// CONSTRUCTOR

function mysql_abstract_layer($database_host, $database_name, $database_username, $database_password){
	$this->database_host = $database_host;
	$this->database_name = $database_name;
	$this->database_username = $database_username;
	$this->database_password = $database_password;
}

// ERROR REPORT OBJECT

function sql_error() 
{ 
	echo "<br />There appears to be an error, below the error information is provided.<br /><br />\n"; 
	die("<textarea rows=\"10\" cols=\"100\">".mysql_error()."</textarea>"); 
}

// CONNECTION OBJECT

function connection($process)
{
	$this->process = strtolower($process);
	switch($process){
		case 'open':
			$this->mysql_connection = mysql_connect($this->database_host, $this->database_username, $this->database_password) OR $this->sql_error(); 
			$this->mysql_select_database = mysql_select_db($this->database_name, $this->mysql_connection) OR $this->sql_error(); 
			return $this->mysql_select_database;
		break;

		case 'close':
			return mysql_close();
		break;

		default:
			echo "<strong>You have entered an incorrect mysql connection process, it must be either \"open\" or \"close\".</strong>";
			exit();
		break;
	}
}

// MANUAL MYSQL_QUERY OBJECT

function sql_manual_query($query) 
{ 
	$this->delete = preg_replace('/DELETE/siU', 1, $query);
	$this->update = preg_replace('/UPDATE/siU', 1, $query);
	$this->insert = preg_replace('/INSERT/siU', 1, $query);

	if($this->delete == 1 || $this->update == 1 || $this->insert == 1){
		$this->affected_rows = mysql_affected_rows();
		$this->affected_rows_total += $this->affected_rows;
	} 
	$this->parse = mysql_query($query) OR $this->sql_error(); 
	$this->query_count++; 

	return $this->parse; 
} 

// SIMPLE MYSQL_QUERY OBJECT

function sql_simple_query($query_type, $table, $condition, $limit, $column = NULL) 
{ 
	if(stristr($query_type, 'DELETE') || stristr($query_type, 'UPDATE') || stristr($query_type, 'INSERT')){
		$this->affected_rows = mysql_affected_rows();
		$this->affected_rows_total += $this->affected_rows;
	} 

	$this->query_type = array('SELECT', 'UPDATE', 'DELETE');
	$this->order_how = array('DESC', 'ASC');
	$this->query_type2 = strtoupper($query_type);
	$this->order_how2 = strtoupper($order_how);

	if(in_array($this->query_type2, $this->query_type){
		if(!empty($limit) && $limit !== NULL){
				$this->limit = " LIMIT ".$limit;
		}else{
			$this->limit = "";
		}

		switch($this->query_type2)
		{
			case 'SELECT':
				if(!empty($column) && $column !== NULL){
					$this->parse = mysql_query("SELECT ".$column." FROM ".$table." WHERE ".$condition.$this->limit);
				}else{
					$this->parse = mysql_query("SELECT * FROM ".$table." WHERE ".$condition.$this->limit);
				}
			break;

			case 'UPDATE':
				$this->parse = mysql_query("UPDATE ".$table." SET ".$column." WHERE ".$condition.$this->limit);
			break;

			case 'DELETE':
				$this->parse = mysql_query("DELETE FROM ".$table." WHERE ". $condition.$this->limit);
			break;			
		}
	}else{
		echo "You have supplied an illegal sql syntax! They can be either SELECT, UPDATE, or DELETE.";
		exit();
	}

	$this->parse = mysql_query($this->parse) OR $this->sql_error(); 
	$this->query_count++; 

	return $this->parse; 
} 

// GRABS ROW(S) BASED ON PREVIOUS QUERY AND RETURNS THEN IN ARRAY FORMAT

function sql_array($query) 
{ 
	$this->parse = mysql_query($query) OR $this->sql_error(); 
	$this->parse = mysql_fetch_array($this->parse); 
	$this->query_count++; 
	return $this->parse_query; 
} 

// GRABS ROW(S) BASED ON PREVIOUS QUERY AND RETURNS THEN IN OBJECT FORMAT

function sql_object($query) 
{ 
	$this->parse = mysql_query($query) OR $this->sql_error(); 
	$this->parse = mysql_fetch_object($this->parse); 
	$this->query_count++; 
	return $this->parse_query; 
} 

// COUNTS THE NUMBER OF ROWS BASED ON PREVIOUS QUERY

function sql_rows($query) 
{ 
	$this->parse = mysql_query($query) OR $this->sql_error(); 
	$this->parse = mysql_num_rows($this->parse); 
	$this->query_count++; 
	return $this->parse; 
} 

// RETURNS THE DATA IN THE NAMED FIELD/ROW OF RESULTS

function sql_results($query, $row, $field = NULL) 
{ 
	if($field !== NULL){
		$this->parse = mysql_result($query, $row, $field) OR $this->sql_error(); 
		$this->query_count++; 
		return $this->parse; 
	}else{
		$this->parse = mysql_result($query, $row) OR $this->sql_error(); 
		$this->query_count++; 
		return $this->parse; 
	}
} 

function last_id()
{
	return mysql_insert_id();
}

function count_queries() 
{ 
	return $this->query_count; 
}

function count_affected_rows()
{
	return $this->affected_rows_total;
}

function sql_close()
{
	return mysql_close();
}
}

$mysql = new mysql_abstraction_layer($database_host, $database_name, $database_username, $database_password);
$mysql->connection('open');
?>

[Weedpacket]

Well, there's little point writing a database abstraction layer that only works for one database 🙂. Perhaps you could abstract (😃)) the interface into a class that mysql_abstraction_layer can subclass; then write comparable subclasses for other DBMSs.

So that switching the code from using MySQL to PostgreSQL, say,would be a matter of changing

$mysql = new mysql_abstraction_layer($database_host, $database_name, $database_username, $database_password);

to

$mysql = new postgresql_abstraction_layer($database_host, $database_name, $database_username, $database_password);

[Shrike]

How would I write a query which used foreign keys with this class? This is a pretty basic function of relational databases but your code seems to assume that everything you need is in one table. I think it would be better to assume that the client code knows how to form a query.