My MySQL Class

[per1os]

Alright here is my MySQL class as promised, it is very distinct for my needs, I never use persistent connections or any other fancy stuff. However here it is:

 

<?php
// mySql database Class
class clDB {
// Database constructor
function clDB($config) {
	$this->link_id = mysql_connect($config['dbHost'], $config['dbUser'], $config['dbPass']);
	if (!$this->link_id) {
		die("There seems to be a problem with the database.");
	}

	if (!mysql_select_db($config['dbDatabase'], $this->link_id)) {
		return $this->error("Unable to connect to database. Please try again later.");
	}		

	$config = null;
}

// returns a single array set
function fetchArr($sql) {
	// see if a valid resource was passed in
	if (!strstr($sql, "Resource")) {
		$result = $this->query($sql);
	}else {
		$result = $sql;
	}

        if(mysql_error()) {
		if (DEV) {
			return $this->error("SQL: " . $sql . "<br /><br />Error: " . mysql_error() . "<br /><br />Error No:" . mysql_errno());
		}else {
			$this->mailError($sql, mysql_error(), mysql_errno());
			return $this->error("There was a SQL error and it has been reported. Sorry for the inconvenience.");
		}
        }

	$ar = mysql_fetch_assoc($result);
	mysql_free_result($result);

	return $ar;
}

// returns a multi-dimension array
function fetchMultiArr($sql) {
	// see if a valid resource was passed in
	if (!strstr($sql, "Resource")) {
		$result = $this->query($sql);
	}else {	
		$result = $sql;
	}

	if ($this->numRows($result) <= 1) {
		return array(0 => $this->fetchArr($result));
	}

        if(mysql_error()) {
		if (DEV) {
			return $this->error("SQL: " . $sql . "<br /><br />Error: " . mysql_error() . "<br /><br />Error No:" . mysql_errno());
		}else {
			$this->mailError($sql, mysql_error(), mysql_errno());
			return $this->error("There was a SQL error and it has been reported. Sorry for the inconvience.");
		}
        }

	$i=0;
	while ($row = mysql_fetch_assoc($result)) {
		$return[$i] = $row;
		$i++;
	}

	mysql_free_result($result);

	return $return;
}

// Returns only one column
function fetchOneCol($sql) {
	if (!strstr($sql, "Resource")) {
		$result = $this->query($sql);
	}else {	
		$result = $sql;
	}

	$return = mysql_fetch_array($result);
	$return = $return[0];

	return $return;
}

// clears the resource
function freeResult($result) {
	return mysql_free_result($result);
}

// Sends an email to the admin on an error
function mailError($sql, $error, $errorNo) {
	if (DEV) {
		print $sql . "<br />" . $error . "<br />" . $errorNo;
	}else {
		mail(ADMIN_EMAIL, "SQL Error on your site", $sql . "\n Error Msg: " . $error . "\n Error Number: " . $errorNo, "From: " . ADMIN_EMAIL);
	}
}

// Does a correct add slashes.
function myAddSlashes($string) {
	return (get_magic_quotes_gpc()) ? $string : mysql_real_escape_string($string); 
}

// return number of rows
function numRows($result) {
	return mysql_num_rows($result);
}

//Executes and returns a query
function query($sql) {
	$result = mysql_query($sql, $this->link_id);

        if (mysql_error()) {
	$this->mailError($sql, mysql_error(), mysql_errno());
	return $this->error("There was a SQL error and it has been reported. Sorry for the inconvience.");
        }

        return $result;
}	

// returns the previous insert id
function queryReturnID($sql) {
	$result = mysql_query($sql, $this->link_id);

        if (mysql_error()) {
		if (DEV) {
			return $this->error("SQL: " . $sql . "<br /><br />Error: " . mysql_error() . "<br /><br />Error No:" . mysql_errno());
		}else {
			$this->mailError($sql, mysql_error(), mysql_errno());
			return $this->error("There was a SQL error and it has been reported. Sorry for the inconvience.");
		}
        }

        return mysql_insert_id($this->link_id);
}

// error function
function error($msg) {
	include('error.page.php');
	//print $msg;
	die();
}

// close the database connection.
function close() {
	mysql_close();
}

}
?>

 

Usage is as follows:

<?php
// note define('DEV', true); to avoid e-mailing errors.
// note define('ADMIN_EMAIL', "[email protected]"); for emailing of errors

$config['dbHost'] = "localhost"; // maybe something different than localhost, usually is localhost. I usually store this and include it via a config file
$config['dbUser'] = "yourdbuser";
$config['dbPassword'] = "yourdbpass";
$config['dbDatabase'] = "yourdbname";

include('class.mysqldb.php'); // reference to the above code

$clDB = new clDB($config);
$config = "no data here"; // do this for security reasons to remove all db user/pass.

// Usage:
// This just returns the result for however you want to use it
$result = $clDB->query("SELECT * from tablename WHERE columnname = 'value'");

// returns an array of the above resource and can take either a resource or straight sql
$dataArr = $clDB->fetchArr($result);

// returns the number of rows the query affected
$clDB->numRows($result); 

//returns an array of the sql
$dataArr = $clDB->fetchArr("SELECT * FROM tablename WHERE columnname = 'value'");

// if you are expecting a multi-dimensional array this also takes straight SQL or a resource
$multiDataArr = $clDB->fetchMultiArr("SELECT * FROM tablename");

// runs the query and returns the auto increment id
$clDB->queryReturnID("INSERT INTO tablename colname1, colname2 VALUES('value1', 'value2')"); 

// Just grabs one column, this does not check for multiple columns, so I would not suggest trying it
$column = $clDB->fetchOneCol("SELECT columname FROM tablename WHERE columnname = 'somevalue'");

// Finally the myAddSlashes(); for prevention of SQL Injection
$sql = ""INSERT INTO tablename colname1, colname2 VALUES('value1', '".$clDB->myAddSlashes($_POST['postdataname'])."')";

// The rest of the functions are really internal to the class.

?>

 

Questions, suggestions let me know. This works great for my uses =)

 

[per1os]

Just a note, in the usage portion the last $sql statement has a parse error with an extra " after the $sql = , remove that for it to be "working"

 

Sorry about that.