scriptNode / MySQL Database Class

MySQL Database Class

Intermediate

Published August 24th, 2008 by Matt Hackett

I've been spending loads of time recently with a new project that's supposed to be comprised mostly of what I love doing the most: hardcore JavaScript. Unfortunately, most front-ends aren't much without a back-end to fall on, so I've had to hit the PHP pretty hard too.

When modifying the framework I had built, I noticed that the old database class I was using was a little out of date. PHP5 is the new standard, shouldn't we all be using PDO? So I added migrating to PDO to my to-do list. But really, the class is a pretty good one. It's certainly been useful to me for, well, almost a few years now, give or take a few dozen tweaks.

So given the longevity of the script, I thought I'd offer it up for this week's Script Sunday:

/**
 * Database constructor
 */
class Database {

protected
		$connection,
		$debug,
		$debug_func;

/**
	 * Constructor method. Connects to the database
	 * @public
	 */
    function __construct() {

$this->debug = Array();
		$this->debug_func = 'debugNone';

}

/**
	 * Gets the number of affected rows from the last query
	 * @return integer Number of affected rows
	 * @public
	 */
    function affectedRows() {
        return mysql_affected_rows($this->connection);
    }

/**
	 * Checks a table to see if a key/value pair exists
	 * @param string table The table to check
	 * @param string field The field in the table
	 * @param string value The value of the field to check
	 * @return bool Returns true if it found a matching row
	 * @public
	 */
    function checkField($table, $field, $value, $and = '') {

$qry = sprintf('
			SELECT
				%s
			FROM
				%s
			WHERE
				%s = "%s"
				%s
			',
			$field,
			$table,
			$field,
			$value,
			$and
		);

$res = $this->query($qry);
		$row = $this->fetchRow($res);

return ($row ? true : false);

}

/**
	 * Cleans a value for the DB
	 * @param str $value The value to clean
	 * @return str The cleaned string
	 */
	function clean($value) {

$value = stripslashes($value);
		$value = trim($value);
		$value = str_replace("'", "\'", $value);

return $value;

}

/**
	 * Connect to the database
	 * @param string db_server The database server to connect to (often localhost)
	 * @param string db_user The user to send to the server
	 * @param string db_password The user's password
	 * @param string db_name The name of the database to use
	 * @public
	 */
    function connect($db_server, $db_user, $db_password, $db_name) {

$this->connection = mysql_connect($db_server, $db_user, $db_password);
        $this->select($db_name, $this->connection);

return ($this->connection ? true : false);
        
    }

function debugNone() {}

/**
	 * Adds a debug message to $debug
	 * @param str The debug message
	 * @public
	 */
	function debugPut($debug) {
		$this->debug[] = $debug;
	}

/**
	 * Deletes rows from the database
	 * @param string table The table to query
	 * @param string where There WHERE clause in the query
	 * @param integer limit How many rows to limit (default is 1)
	 * @return integer Number of affected rows
	 * @public
	 */
    function deleteRows($table, $where, $limit = 1) {

$limit = ($limit ? 'LIMIT ' . $limit : '');

$qry = 'DELETE FROM ' . $table . '
				WHERE ' . $where . '
				' . $limit;

$this->query($qry);

return $this->affectedRows();

}
     
	/**
	 * Retrieves a row from the database
	 * @param string result The result set returned from the query
	 * @return array Associatve index of the retrieved row
	 */
    function fetchRow($res) {
        return @mysql_fetch_assoc($res);
    }

/**
	 * Get a single row from the database
	 * @param qry The query to send
	 * @return array Associative array of the row
	 */
	function getRow($qry) {

$res = $this->query($qry);
		$row = $this->fetchRow($res);

return $row;

}

/**
	 * Retreive rows from the database
	 * @param qry The query to get the rows
	 * @param array Numeric array of the rows
	 */
	function getRows($qry) {

$rows = Array();
		$res  = $this->query($qry);

while ($row = $this->fetchRow($res)) {
			$rows[] = $row;
		}

return $rows;

}

/**
	 * Format a query for insertion into the database
	 * @param string table The table to insert into
	 * @param array pairs Key value pairs of the fields and values to insert
	 * @return string The prepared query, ready to insert
	 */
    function formatInsert($table, $pairs) {

foreach ($pairs as $field => $value) {

$fields .= $field . ',';

if (!is_numeric($value)) {
				$value = $this->clean($value);
			}

$values .= "'$value',";

}

$fields = substr($fields, 0, -1);
		$values = substr($values, 0, -1);

$qry = sprintf("
			INSERT INTO
				$table
			($fields)
			VALUES
				($values)
		");

return $qry;

}

/**
	 * Formats an update query based on a key/value array
	 * @param string table The name of the table
	 * @param array pairs The key/value pair of the fields/values
	 * @param string where The WHERE clause of the array (default: 1)
	 * @param integer limit The limit on tbe query
	 * @return string The prepared query
	*/
    function formatUpdate($table, $pairs, $where = '1', $limit = false) {

foreach ($pairs as $key => $value) {

if (!is_numeric($value)) {
				$value = $this->clean($value);
			}

$set .= "$key = '$value', ";

}

$set   = substr($set, 0, -2);
		$limit = ($limit ? ' LIMIT ' . $limit : '');

$qry = sprintf("
			UPDATE
				$table
			SET
				$set
			WHERE
				$where
			$limit
		");

return $qry;

}

/**
	 * Get the id of the last inserted row
	 * @return integer They id (if it exists) of the last inserted row
	*/
    function getID() {
        return mysql_insert_id($this->connection);
    }

/**
	 * Inserts rows into the database
	 * @param string table The table to send the query to
	 * @param array pairs The key/value pairs to insert into the database
	 * @return integer Number of affected rows
	*/
	function insertRows($table, $pairs) {
		return $this->query($this->formatInsert($table, $pairs));
	}

/**
	 * Gets the number of rows from a result set
	 * @return integer Number of rows
	*/
    function numRows($res) {
        return mysql_num_rows($res);
    }

/**
	 * Send a query to the database
	 * @param string qry The query to send
	 * @return string A reference to the result set
	*/
    function query($qry) {

$func = $this->debug_func;
		$this->$func($qry);

return mysql_query($qry, $this->connection);

}

/**
	 * Select a database on the server
	 * @param string db_name The name of the database
	 * @return bool Returns true on success and false on failure
	*/
    function select($db_name, $connection = null) {

if ($connection) {
			$this->connection = &$connection;
		}

return mysql_select_db($db_name, $this->connection);

}

/**
	 * Turns on or off the debug option
	 * @param bool debug The new value of the debug option
	*/
	function setDebug($debug) {
		if ($debug) {
			$this->debug_func = 'debugPut';
		} else {
			$this->debug_func = 'debugNone';
		}
	}

/**
	 * Updates rows in the database
	 * @param string table The table to send the query to
	 * @param array pairs The key/value pairs to insert into the database
	 * @param string where The WHERE clause of the array (default: 1)
	 * @param integer limit The limit on tbe query
	 * @return integer Number of affected rows
	*/
	function updateRows($table, $pairs, $where = '1', $limit = false) {
		return $this->query($this->formatUpdate($table, $pairs, $where, $limit));
	}
        
}

And here's a quick example of how to use it:

[Download database.php.txt] (6,848 bytes)
[Download db-class.zip] (1,966 bytes)

Read other articles tagged: mysql, php, script-sunday

Comments (2)

August 24th, 2008 Homar said…

Just what I was looking for! Thanks!
October 22nd, 2008 Peter Goodman said…

It concerns me that your clean() method doesn’t use mysql_real_escape_string().

scriptNode

MySQL Database Class

Comments (2)

Thoughts?

Social Web

Tags

Links