Revamping My MySQL Connection

[per1os]

Hi All,

 

I figured this forum was a better place to post this since I do not really need help, really just a critique with suggestions.

 

Anyhow I am designing a new MySQL class so to say and I am trying to make it as dynamic as possible with building the queries via passed in parameters. It is still in the phases of being completed I just want to make sure someone does not have a better idea that will make me want to re-work what I currently done. This is all un-tested and probably will not work without some fixing.

 

Anyhow any thoughts/suggestions or ideas would help me out. Thanks!

 

<?php
// usage:
$clDB->query("select", "userid, username, email", "users", "username='".$_GET['username']."' AND x=y AND b = 'x=y'");

$clDB->query("update", "userid, username, email", "users", "username='".$_GET['username']."'", $_POST, "", "1");
//UPDATE table_name SET userid = "test", username = "test", email = "[email protected]" WHERE username = 'frost' LIMIT 1;

// class
class clDB {
         function clDB($config) {
             // do connections etc here.
       }

       function myEscape($data) {
                return get_magic_quotes_gpc() ? $data : mysql_real_escape_string($data);
       }

function query($type='select',$cols, $tbls, $crit="", $order='', $group='', $limit='') {
	if (trim($type) == "") {
		return $this->error(LANG_DB_ERRTYPE);
	}

	switch (strtolower($type)) {
		case 'select':
			$sel = $this->buildSelect($cols);

			$from = $this->buildTables($tbls);

			$crit = $this->buildCrit($crit);

			$sql = "SELECT " . $sel . " FROM " . $from . $crit . (trim($group) != "")?" GROUP BY " . $group:"" . (trim($order) != "")?" ORDER BY " . $order:"" . (trim($limit) != "")?" LIMIT ".$limit:"";
		break;

		case 'update':
			$tbl = $this->buildTables($tbls);				
			$update = $this->buildUpdate($order, $cols);
			$crit = $this->buildSelect($crit);

			$sql = "UPDATE " . $tbl . " SET " . $update . $crit . (trim($limit) != "")?" LIMIT " . $limit:"";
		break;

		case 'delete':

		break;

		case 'insert':

		break;

		case 'sql':
			$sql = $dataArr;
		break;

		default:
			return $this->error(LANG_DB_ERRTYPE);		
	}

	// Setup for referencing this result throughout it's life. ?? Or just return it, one or the other.
	$this->result = mysql_query($sql, $this->link_id) or return $this->error("SQL ERROR: " . mysql_error());
	return $this->result;
}

function buildUpdate($update, $cols) {
	if ($update == "") {
		return error(LANG_DB_ERR_UPDSETDATA);
	}

	if (ereg(",", $cols)) 
		$columns = explode(",", $cols);
	else
		$columns[0] = $cols;

	if (is_array($update)) {
		foreach ($columns as $col) {
			if (isset($update[$col]) && $this->isColumn($col)) {
				$updateSQL .= $col . "='" . $this->myEscape($update[$col]) . "',";
			}
		}

		$updateSQL = substr($updateSQL, 0, -1); // remove ending comma
	}else {
		return error(LANG_DB_ERR_UPDVALS);
	}

	return $updateSQL;
}

function buildCrit($crit) {
	if ($crit == "") {
		return;
	}
	// TODO: figure out a way to be able to mysql_real_escape_string the values inside this query.
	return " WHERE " . $crit;
}

function buildSelect($cols) {
	if ($cols == "*") 
		return $cols;

	foreach ($this->columnList as $col)
		$cols = eregi_replace($col, "`" . $col . "`", $cols);

	return $cols;
}

function buildTables($tbls) {
	foreach ($this->tableList as $table) {
		$tbls = eregi_replace($table, "`" . $table . "`", $tbls);

	return $tbls;
}
}
?>

[mb81]

I have done the same thing, I actually prepare the statement before I send it to the query function to reduce the length of each function.

 

I have the following functions:

 

buildselect( tablename as string, fieldnames AS array, defaults to Array(*),  parameters as Array or String)

 

buildupdate( tablename as string, values AS array (fieldname as key, new value as value), parameters As array or string)

 

builddelete ( tablename as string, parameters AS array or string)

 

For the parameters, if the parameters are an array, I join them with "AND" and assume '=' (ex. Array('category'=>1,'date'=>'2007-03-15') becomes "category='1' AND  date='2007-03-15') (I probably should add support for "OR", but haven't had the need yet), if the parameters are a string, I just take them as is (I use this when I need comparisons other than =)

 

This three functions return a string, which I send to the query function, so a call might look like this:

 

$site->query($site->buildselect("categories",Array('id'=>1));

 

Hope that helps, if you send me a private message, I will be glad to e-mail you the full code.

 

P.S. I have also built into my fetch function the option of sending a string, it sends the string to the query function, then performs the fetch. This saves a line of code when I just need one row of data.