Database PHP Class

Posted by on Jun 15, 2010 in PHP | 1 comment

I’m back with another chunk of code. This time, it’s my database class.

I got sick of coming across classes that didn’t have everything I needed, or didn’t do it correctly. One of my biggest problems was an inability to nest query calls within another query result’s output.

Example:

<?php

// main query
// gets a list of companies from the companies table
$a = "SELECT id, company_name FROM companies";
$a = $db->query($a);

while  ($aa = $db->fetch_object($a) ) :

     echo '<div class="company_name">' . $aa->company_name . '</div>';

     // get the contacts for each company
     $b = "SELECT contact_name, phone1 FROM contacts WHERE company_id = $aa->id"; // because we've already used $a, we can't use it again during $a's output
     $b = $db->query($b);

     // output all contacts for the each company
     while ( $bb = $db->fetch_object($b) ) : // the same thing as $a, we can not use $aa again during its output
          echo '<div class="contact_name">' . $bb->contact_name . '</div>';
          echo '<div class="contact_phone">' . $bb->phone1 . '</div>';
     endwhile;

endwhile;

?>

With this class, that is not a problem. The only requirement is that you can’t name your $sql and $results ($row) variables the same (noted in the code above). Other than that, there’s nothing to it. It’s just a way to provide clean, and efficient database interaction.

Database.Core.php Code

  • Updated 06-23-2010:

    • Renamed main database class file to Database.Core.php
    • Removed the $connection settings and placed them in the Database.Connect.Config.php file. Helps keep hands out of the core file.
    • show_mysql_error(): edited so it only receives and passes the original SQL statement, to the error_backtrace() method, upon error.
    • query(): no longer fires the die() function and close_db() method. Send the erroring SQL statement to show_mysql_error() method for handling of error output.
    • multi_query(): no longer fires the die() function and close_db() method. Send the erroring SQL statement to show_mysql_error() method for handling of error output.
    • error_backtrace(): replaced previous method with cleaner code. The error now has formattting for easier readability.

<?php
/* **********************************************************************
*
* 	DB connection variables
*	Must be set in order to establish a connection to the database
*
********************************************************************** */

$connection = (object) array();
require ( 'Database.Connect.Config.php' );

/* **********************************************************************
* END :::
********************************************************************** */

/* **********************************************************************
*	DatabaseFunctions is a class created to manage database functions
*
*	The class manages all database interraction for purpose of allowing certain things to be
*	turned on and off.  An example would be Error Reporting.  Once the application is on the live
*	server, we can set $config->ShowErrors to false and error reporting for database erros will no
*	be shown.
*
*	This is real simply to use
*
*	Examples:
*
**********
*
*		Get number of rows returned in a query (you can also nest this:
*			$sql = "SELECT fieldA, fieldB FROM tableA"; // would return 4 rows
*			$sql = $db->fetch_object($sql);
*			$rows = $db->num_rows();
*			echo $rows; // 4
*
*		Nested num_rows();
*			$a = "SELECT fieldA FROM tableA"; // would return 2 rows
*			$a = $db->fetch_object($a);
*			$a_rows = $db->num_rows();
*			echo $a_rows; // 2
*
*			while ($aa = $db->fetch_object($a) ) :
*				$b = "SELECT fieldA FROM tableA"; // would return 6 rows
*				$b = $db->fetch_object($b);
*				$b_rows = $db->num_rows();
*					echo $b_rows; // 6
*			endwhile;
*
*
**********
*
*
*		Results as Object:
*			$sql = "SELECT fieldA, fieldB FROM tableA";
*			$sql = $db->fetch_object($sql);
*
*			while ($row = $db->fetch_object($sql) ) :
*				echo $row->fieldA . ': ' . $row->fieldB;
*			endwhile;
*
*
**********
*
*
*		Results as Array:
*			$sql = "SELECT fieldA, fieldB FROM tableA";
*			$sql = $db->fetch_object($sql);
*
*			while ($row = $db->fetch_array($sql) ) :
*				echo $row['fieldA'] . ': ' . $row['fieldB'];
*			endwhile;
*
*
**********
*
*
*		Return value of a row or field in a row ( uses the rather inefficient mysql_fetch_row() )
*			$sql = "SELECT fieldA, fieldB FROM tableA";
*			$val = $db->fetch_row(1);        // would return fieldB's value
*				-- OR --
*			$val = $db->fetch_row('fieldA'); // would obviously return fieldA's value
*
*
**********
*
*
*		Return value of a query more efficiently (avoids the inefficient mysql_fetch_row() function)
*			$sql = "SELECT fieldA FROM tableA";
*			$fieldA = $db->fetch_value($sql); // would return fieldA's value
*
*
**********
*
*
*		 Return the query result set as an array:
*			$sql = "SELECT fieldA, fieldB FROM tableA"; // returns 4 rows
*			$results = $db->to_array($sql);
*
*			$results[0]['fieldA'] = 'valueA 1';
*			$results[0]['fieldB'] = 'valueB 1';
*
*			$results[1]['fieldA'] = 'valueA 2';
*			$results[1]['fieldB'] = 'valueB 2';
*
*			$results[2]['fieldA'] = 'valueA 3';
*			$results[2]['fieldB'] = 'valueB 3';
*
*			$results[3]['fieldA'] = 'valueA 4';
*			$results[3]['fieldB'] = 'valueB 4';
*
*
********************************************************************** */

class System_Database {

	# set whether errors should be displayed or not
	# probably want to turn off once site is live
	var $displayErrors = false;

	/* ****************************************************************
	*
	* Establish connection to the database
	* Be sure to set the connection info above
	*
	**************************************************************** */
	function connect() {
		global $connection;
		$connection->mysqlLink = mysql_connect($connection->host, $connection->user, $connection->password);

		# The connection failed for some reason
		if ( !isset($connection->mysqlLink) ) :
			die("Error connecting to the server");

		# The connection is good so lets get the Application Config Settings
		# and create the $config object
		elseif ( isset($connection->mysqlLink) ) :
			$db_selected = mysql_select_db($connection->db);

		endif;
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Run the query
	*
	**************************************************************** */
	function sqlId() {
		$id = rand();
		$uniqueId[] = 'sql' . $id;
		$uniqueId[] = 'row' . $id;
		return $uniqueId;
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* If $showErrors is set to true or $config->ShowErrors
	* is set and is true display the error that has occurred
	*
	**************************************************************** */
	function show_mysql_error( $sql ) {
		$this->error_backtrace( $sql );
		die();
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Run the query
	*
	**************************************************************** */
	function query( $sql ) {
		$this->result=mysql_query( $sql );
		if ( !$this->result ) :

			$this->show_mysql_error( $sql );

		else :

			return $this->result;

		endif;
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Connect to the server using the mysqli extension
	* This is used automatically during multi_query()
	*
	**************************************************************** */
	function mySqliConnect() {
		global $connection;
		$connection->mySqli = new mysqli($connection->host, $connection->user,  $connection->password, $connection->db);
		return $connection->mySqli;
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Close the mysqli connection once we have finished
	* using the multi_query() method
	*
	**************************************************************** */
	function mySqliClose() {
		global $connection;
		mysqli_close($connection->mySqli);
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Execture the multi-query
	*
	**************************************************************** */
	function multi_query( $sql ) {

		$mysqli = $this->mySqliConnect();

		$this->result=mysqli_multi_query( $mysqli, $sql );

		if ( !$this->result ) :
			$this->show_mysql_error( $sql );
		else :
			$this->mySqliClose();
			return $this->result;
		endif;
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Returns the results of the query as an object
	*
	* Example:
	*	$sql = "SELECT fieldA, fieldB FROM tableA";
	*	$sql = $db->fetch_object($sql);
	*
	*	while ($row = $db->fetch_object($sql) ) :
	*		echo $row->fieldA . ': ' . $row->fieldB;
	*	endwhile;
	*
	**************************************************************** */
	function fetch_object( $sql ) {
		$uniqueIdentifier=rand();
		$$uniqueIdentifier=mysql_fetch_object( $sql );
		return $$uniqueIdentifier;
		unset( $$uniqueIdentifier );
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Returns the results of the query as an array
	*
	* Example:
	*	$sql = "SELECT fieldA, fieldB FROM tableA";
	*	$sql = $db->fetch_object($sql);
	*
	*	while ($row = $db->fetch_object($sql) ) :
	*		echo $row['fieldA'] . ': ' . $row['fieldB'];
	*	endwhile;
	*
	**************************************************************** */
	function fetch_array( $sql ) {
		$uniqueIdentifier=rand();
		$$uniqueIdentifier=mysql_fetch_array( $sql );
		return $$uniqueIdentifier;
		unset( $$uniqueIdentifier );
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Returns the number of rows returned in a query
	*
	**************************************************************** */
	function num_rows($result='') {
		$uniqueIdentifier=rand();
		if (empty($result)) $result = $this->result;
		$$uniqueIdentifier=mysql_num_rows( $result );
		return $$uniqueIdentifier;
		unset( $$uniqueIdentifier );
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Returns the number of rows affected by a query
	*
	**************************************************************** */
	function rows_affected($result='') {
		$uniqueIdentifier=rand();
		if (empty($result)) $result = $this->result;
		$$uniqueIdentifier = mysql_rows_affected( $result );
		return $$uniqueIdentifier;
		unset( $$uniqueIdentifier );
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Runs the mysql_result() function, but a little differently
	*
	* if $this->fetch_row(1), it will return the second field
	* of the first row
	*
	* if $this->fetch_row('field_name'), it will return the value
	* for 'field_name' in the first row
	*
	**************************************************************** */
	function fetch_row ( $fieldName ) {
		if (is_numeric( $fieldName )) {
			$fieldValue=mysql_result( $this->result, $fieldName );
		} else {
			$fieldValue=mysql_result( $this->result, 0, $fieldName );
		}
		return $fieldValue;
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Returns the value of the specified field, in the specified
	* row.
	*
	* If no field or row is specified, it will default to first
	* row, first field
	*
	**************************************************************** */
	function fetch_value ( $sql='', $fieldName='', $row='' ) {
		$i=0;
		$fieldValue = false;

		if (empty($fieldName)) { $fieldName = 0; }
		if (empty($row)) { $row = 0; }
		$ret = array();
		$sql = $this->query($sql);
		if ($this->num_rows() > 0) {
			while ($rec = mysql_fetch_array( $sql )) {
				for ($a=0; $a < count($rec); $a++) {
					$ret[$i] = $rec;
				}
				$i++;
			}

			$fieldValue=$ret[$row][$fieldName];
		}

		return ($fieldValue);
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Returns the results of the query as an array
	*
	* Example:
	*	$sql = "SELECT fieldA, fieldB FROM tableA"; // returns 4 rows
	*	$results = $db->to_array($sql);
	*
	*	$results[0]['fieldA'] = 'valueA 1';
	*	$results[0]['fieldB'] = 'valueB 1';
	*
	*	$results[1]['fieldA'] = 'valueA 2';
	*	$results[1]['fieldB'] = 'valueB 2';
	*
	*	$results[2]['fieldA'] = 'valueA 3';
	*	$results[2]['fieldB'] = 'valueB 3';
	*
	*	$results[3]['fieldA'] = 'valueA 4';
	*	$results[3]['fieldB'] = 'valueB 4';
	*
	**************************************************************** */
	function to_array ( $sql='' ) {
		$i=0;
		$ret = array();
		$sql = $this->query($sql);
		while ($row = mysql_fetch_assoc( $sql )) {
			for ($a=0; $a < count($row); $a++) {
				$ret[$i] = $row;
			}
			$i++;
		}
		return ($ret);
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* closes the database connection
	*
	* PHP nowadays does a really good job of trashing these connection
	* automatically. However, just to be safe, I typically call this
	* method in an include that is loaded after everything else
	*
	**************************************************************** */
	function close_db() {
		global $connection;
		if ($connection->mysqlLink) :
			mysql_close( $connection->mysqlLink );
		endif;
	}
	/* ****************************************************************
	* end :::
	**************************************************************** */

	/* ****************************************************************
	*
	* Will format system errors and display them
	*
	**************************************************************** */
	function error_backtrace( $sql ) {

		$errorsThrown = debug_backtrace();
		$errorThrown = $errorsThrown[2];

		echo "
		<style>
			#error-container { width: 100%; color: #333!important; position: relative; background-color: #f3f3f3; border-bottom: 1px dotted #333; }
			#error-wrapper { font: 110%/0.8em arial!important; letter-spacing: 0.02em; width: 850px!important; background-color: #f3f3f3!important; padding: 10px!important; }
			#error-wrapper a { color: #cc6600; }
			#primary-error td { color: #333!important; padding: 4px!important; border: 1px solid #f3f3f3!important; }
			#primary-error td.left {
				width: 120px;
				text-align: right;
				padding: 8px 4px 8px 8px!important;
				margin: 0 8px 1px 0!important;
				background-color: #d3d3d3!important;
			}
			#error-wrapper p { text-align: left; padding: 18px; clear: both!important; }

			#previous-errors th { color: #333!important; padding: 6px; }
			#previous-errors td { color: #333!important; padding: 4px; border-top: 1px solid #666; border-right: 1px solid #666; }
		</style>
		";

		echo "<div id=\"error-container\">"
			. "<div id=\"error-wrapper\">"
			. "<table id=\"primary-error\">\n"
			. "<tr><td class=\"left\"><strong>Error: </strong></td><td>Database error</td></tr>\n"
			. "<tr><td class=\"left\"><strong>SQL: </strong></td><td>" . $sql . "</td></tr>\n"
			. "<tr><td class=\"left\"><strong>MySQL Error: </strong></td><td>" . mysql_error() . "</td></tr>\n"
			. "<tr><td class=\"left\"><strong>Calling Function: </strong></td><td>" . $errorThrown['function'] . "</td></tr>\n"
			. "<tr><td class=\"left\"><strong>File Name: </strong></td><td>".$errorThrown['file']."</td></tr>\n"
			. "<tr><td class=\"left\"><strong>On line: </strong></td><td>".$errorThrown['line']."</td></tr>\n</table>\n"
			. '<p>&nbsp;</p>'
			. '</div></div>';
	}
	/* ****************************************************************
	* END :::
	**************************************************************** */

}
/* *********************************************************************************
* END :::
********************************************************************************* */

$db = new System_Database;
?>

Database.Connect.Config.php Code

used for establishing the connection information for your database. this file is automatically included in the Database.Core.php file. just drop it in the same folder and you’re good to go.


<?php
	$connection->host           = 'localhost';
	$connection->user           = 'user_name';
	$connection->password    = 'your_password';
	$connection->db             = 'your_database';
?>

1 Comment

  1. party supplies
    August 12, 2010

    Great blog! I definitely love how it’s easy on my eyes and the info are well written. I am wondering how I can be notified whenever a new post has been made. I have subscribed to your rss feed which ought to do the trick! Have a nice day!

    reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>