QuickDB – The MySQL Wrapper

quickdbWhen working on different projects, you have to use time-consuming mysql commands every now and then. Same was the case with me and i had really got sick of all those commands, so i decided to create my own mysql wrapper with ability to perform the similar tasks far more quickly and easily than their mysql counterparts. I have used it in many projects i have developed and it has saved me about 50% of the time as compared to using mysql default functions.

You can download the file whole class with example here.

.
tweet_this
.

So here is the code for the sample file, name it quickdb_example.php

	// Online Class Location:
	// http://www.phpclasses.org/browse/package/5318.html

	/*

	In order to use the example here, you need to create a table,
	just execute the following SQL lines using your MySQL client:
	-------------------------------------------------------------

	################################
	CREATE TABLE `quickdbtest` (
	  `catid` int(11) NOT NULL auto_increment,
	  `catname` varchar(255) default NULL,
	  PRIMARY KEY  (`catid`)
	);
	insert  into `quickdbtest`(`catid`,`catname`) values (1,'Big'),(2,'Small'),(3,'Average'),(4,'Light'),(5,'Huge');
	################################

	*/

	// This file uses most of the functions of QuickDB MySQL wrapper.
	// It is very easy to use these funtions (methods), see below examples.
	// Once you know how to use this, i bet you are gonna use it in future as well :)

	require_once "quickdb.class.php";
	// Argumentd are: host, user, password, database, persistent connection, show errors on screen
	$db = new QuickDB("localhost", "root", "", "test", false, true);

	## execute Method/Function
	// it can run any query whether select, insert, update or delete like mysql_query function

	// returns:
	// 1) resource identifier for "Select query" that can later be used with mysql_fetch_array or mysql_fetch_object
	// 2) number of rows affected for "insert, update or delete" queries

																											// Usage Of:
																											// --------------------------

	$result = $db->execute("select * from quickdbtest order by catname");									// $db->execute
	// Or
	// 		$result = $db->select("quickdbtest");															// $db->select

	while ($row = mysql_fetch_array($result))
	{
		print $row["catname"] . "
";
	}

	print "
Number of rows selected from previous query : " . $db->count_select();						// $db->count_select()

	$affected = $db->execute("insert into quickdbtest set catname = 'New Category'");
	// Or
	//		$affected = $db->insert("quickdbtest", "catname = 'New Category'");								// $db->insert

	print "
Number of rows affected are: $affected";
	// Or
	print "
Number of rows affected using class method: " . $db->count_affected();						// $db->count_affected()
	$db->success_msg("Record was added successfully !!");

	$affected = $db->update("quickdbtest", "catname = 'New Category 2'", "catname = 'New Category'");		// $db->update
	print "
Number of rows affected are: $affected";

	$db->delete("quickdbtest", "catid = " . $db->last_insert_id());											// $db->last_insert_id(), $db->delete
	print "
Number of rows affected using class method: " . $db->count_affected();

	print "
Total records in table are: " . $db->count_all("quickdbtest");								// $db->count_all

	print "
Counting records using 'count_rows' : " . $db->count_rows("select * from quickdbtest");	// $db->count_rows

	// $db->insert_update will update if row exists, or insert data if it doesn't with catid=5
	$affected = $db->insert_update("quickdbtest", "catname='Test Category'", "catid=5");					// $db->insert_update
	print "
Number of rows affected are: $affected
";

	$result = $db->select_limited("quickdbtest", 4, 1);														// $db->select_limited

	while ($row = mysql_fetch_array($result))
	{
		print $row["catname"] . "
";
	}

	if ($db->has_rows("quickdbtest"))																		// $db->has_rows
	{
		print "This table is not empty, it has rows in it !!";
	}
	else
	{
		print "Oops, the table is empty !!!";
	}

	if ($db->row_exists("select * from quickdbtest where catid = 5"))										// $db->row_exists
	{
		print "
Yes, row exists";
	}
	else
	{
		print "
No, row does not exist";
	}

	// fetch a single row from db
	$row = $db->fetch_row("select * from quickdbtest where catid = 1");										// $db->fetch_row
	print "
The fetched values are: " . $row->catid . " " . $row->catname ;

	// fetch a single row from db
	$catname = $db->fetch_value("quickdbtest", "catname", "catid = 3");										// $db->fetch_value
	print "
The fetched value is: " . $catname;

	print "
 The date today is: " . $db->get_date();													// $db->get_date()
	print "
 The current time is: " . $db->get_time();													// $db->get_time()

	// $db->last_query() gives the last run query, may be useful for debugging queries
	print "
 The last run query was: <strong>" . $db->last_query()  . "</strong>

";

	// using very useful $db->load_data()
	$db->select("quickdbtest");																				// $db->select
	$data = $db->load_array();																				// $db->load_array()
	print_r($data);
	/////////////////////////

	// using another very useful function that convert db table to a html table
	$db->get_html("select * from quickdbtest order by catname" , true, 'width = 50%, align="center"');		// $db->get_html

	print "
<strong>Database Tables</strong>
";
	$db->list_tables();																						// $db->list_tables()

	print "
<strong>Table Information</strong>
";
	$db->table_info("quickdbtest");																			// $db->table_info

	$db->success_msg("Wow, this is cool class !!");															// $db->success_msg

	// just a wrong table to get that error
	$db->count_rows("select * from NO_TABLE");
	$db->display_errors();																					// $db->display_errros()

	$db->failure_msg("Oops, i must have received the error !!");											// $db->failure_msg

	$db->alert_msg("This is general alert message !!");														// $db->alert_msg

	print "

" . $db->success_msg("<STRONG>REQUEST:</STRONG> IF YOU LIKE THIS THEN PLEASE GO AHEAD AND <a href='http://www.phpclasses.org/browse/package/5318.html'>RATE</a> THIS AT PHPCLASSES.ORG, THANKS :)") . "

";

and here is the code for the class itself, quickdb.class.php

	#########################################################
	#			QuickDB->MySQL Wrapper Class				#
	#-------------------------------------------------------#
	#	Author: SARFRAZ AHMED CHANDIO						#
	#			Web Developer								#
	#			Brains Technology							#
	#			http://www.brainstech.com					#
	#			http://sarfraznawaz.wordpress.com			#
	#														#
	#	Date Created: 12 April 2009							#
	#########################################################

	#-------------------------------------------------------#
	# 	+++ Future Additions +++							#
	#-------------------------------------------------------#
	#	Paging												#
	#	Multi-Language Support								#
	#-------------------------------------------------------#

	class QuickDB
	{
		private $con 			= null;		// for db connection
		private $result 		= null;		// for mysql result resource id
		private $row 			= null;		// for fetched row
		private $rows 			= null;		// for number of rows fetched
		private $affected 		= null;		// for number of rows affected
		private $insert_id 		= null;		// for last inserted id
		private $query 			= null;		// for the last run query
		private $show_errors 	= null;		// for knowing whether to display errors
		private $emsg 			= null;		// for mysql error description
		private $eno 			= null;		// for mysql error number

		// Intialize the class with connection to db
		public function __construct($host, $user, $password, $db, $persistent = false, $show_errors = false)
		{
			if ($show_errors == true)
			{
				$this->show_errors = true;
			}

			if ($persistent == true)
			{
				$this->con = @mysql_pconnect($host, $user, $password);
			}
			else
			{
				$this->con = @mysql_connect($host, $user, $password);
			}

			if ($this->con)
			{
				$result = mysql_select_db($db, $this->con) or die("Could Not Select The Database !!");
				return $result;
			}
			else
			{
				die("Could Not Establish The Connection !!");
			}
		}

		// Close the connection to database
		public function __destruct()
		{
			$this->close();
		}

		// Close the connection to database
		public function close()
		{
			$result = @mysql_close($this->con);
			return $result;
		}

		// stores mysql errors
		private function setError($msg, $no)
		{
			$this->emsg = $msg;
			$this->eno = $no;

			if ($this->show_errors == true)
			{
				print '
<div style="background:#f6f6f6; padding:5px; font-size:13px; font-family:verdana; border:1px solid #cccccc;">
						<span style="color:#ff0000;">MySQL Error Number</span> : ' . $no . '
						<span style="color:#ff0000;">MySQL Error Message</span> : ' . $msg . '</div>
';
			}
		}

		#################################################
		#				General Functions				#
		#################################################

		// Runs the SQL query (general execute query function)
		public function execute($command)
		{
			# Params:
			# 		$command = query command

			if (!$command)
			{
				exit("No Query Command Specified !!");
			}

			$this->query = $command;

			// For Operational query
			if 	(
				(stripos($command, "insert ") !== false) ||
				(stripos($command, "update ") !== false) ||
				(stripos($command, "delete ") !== false) ||
				(stripos($command, "replace ") !== false)
				)
			{
				$this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno());

				if (stripos($command, "insert ") !== false)
				{
					if ($this->result)
					{
						$this->insert_id = intval(mysql_insert_id());
					}
				}

				if ($this->result)
				{
					$this->affected = intval(mysql_affected_rows());
					// return the number of rows affected
					return $this->affected;
				}
			}
			else
			{
				// For Selection query
				$this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno());
				if ($this->result)
				{
					$this->rows = intval(mysql_num_rows($this->result));
					// return the query resource for later processing
					return $this->result;
				}
			}
		}

		// Gets records from table
		public function select($table, $rows = "*", $condition = null, $order = null)
		{
			# Params:
			# 		$table = the name of the table
			#		$rows = rows to be selected
			# 		$condition = example: where id = 99
			#		$order = ordering field name

			if (!$table)
			{
				exit("No Table Specified !!");
			}

			$sql = "select $rows from $table";

			if($condition)
			{
				$sql .= ' where ' . $condition;
			}
			else if($order)
			{
				$sql .= ' order by ' . $order;
			}

			$this->query = $sql;
			$this->result = mysql_query($sql) or $this->setError(mysql_error(), mysql_errno());

			if ($this->result)
			{
				$this->rows = intval(mysql_num_rows($this->result));
				// return the query resource for later processing
				return $this->result;
			}
		}

		// Inserts records
		public function insert($table, $data)
		{
			# Params:
			# 		$table = the name of the table
			# 		$data = field/value pairs to be inserted

			if ($table)
			{
				if ($data)
				{
					$this->result = mysql_query("insert into $table set $data") or $this->setError(mysql_error(), mysql_errno());
					$this->query = "insert into $table set $data";

					if ($this->result)
					{
						$this->affected = intval(mysql_affected_rows());
						$this->insert_id = intval(mysql_insert_id());
						// return the number of rows affected
						return $this->affected;
					}
				}
				else
				{
					print "No Data Specified !!";
				}
			}
			else
			{
				print "No Table Specified !!";
			}
		}

		// Updates records
		public function update($table, $data, $condition)
		{
			# Params:
			# 		$table = the name of the table
			# 		$data = field/value pairs to be updated
			# 		$condition = example: where id = 99

			if ($table)
			{
				if ($data)
				{
					if ($condition)
					{
						$this->result = mysql_query("update $table set $data where $condition") or $this->setError(mysql_error(), mysql_errno());
						$this->query = "update $table set $data where $condition";

						if ($this->result)
						{
							$this->affected = intval(mysql_affected_rows());
							// return the number of rows affected
							return $this->affected;
						}
					}
					else
					{
						print "No Condition Specified !!";
					}
				}
				else
				{
					print "No Data Specified !!";
				}
			}
			else
			{
				print "No Table Specified !!";
			}
		}

		// Deletes records
		public function delete($table, $condition)
		{
			# Params:
			# 		$table = the name of the table
			# 		$condition = example: where id = 99

			if ($table)
			{
				if ($condition)
				{
					$this->result = mysql_query("delete from $table where $condition") or $this->setError(mysql_error(), mysql_errno());
					$this->query = "delete from $table where $condition";

					if ($this->result)
					{
						$this->affected = intval(mysql_affected_rows());
						// return the number of rows affected
						return $this->affected;
					}
				}
				else
				{
					print "No Condition Specified !!";
				}
			}
			else
			{
				print "No Table Specified !!";
			}
		}

		// returns table data in array
		public function load_array()
		{
			$arr = array();

			while ($row = mysql_fetch_object($this->result))
			{
				$arr[] = $row;
			}

			return $arr;
		}

		// print a complete html table from the specified db table
		public function get_html($command, $display_field_headers = true, $table_attribs = 'border="0" cellpadding="5" cellspacing="0" style="padding-bottom:5px; border:1px solid #cccccc; font-size:13px; font-family:verdana;"')
		{
			if (!$command)
			{
				exit("No Query Command Specified !!");
			}

			$this->query = $command;
			$this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno());

			if ($this->result)
			{
				$this->rows = intval(mysql_num_rows($this->result));

				$num_fields = mysql_num_fields($this->result);

				print '
<div>
<table ' . $table_attribs . '>'
						. "\n" . '
<tr>';

				if ($display_field_headers == true)
				{
					// printing table headers
					for($i = 0; $i < $num_fields; $i++)
					{
						$field = mysql_fetch_field($this->result);
						print "
<td bgcolor='#f6f6f6' style=' border:1px solid #cccccc; padding:5px;'><strong style='color:#666666;'>" . ucwords($field->name) . "</strong></td>
\n";
					}
					print "</tr>
\n";
				}

				// printing table rows
				while($row = mysql_fetch_row($this->result))
				{
					print "
<tr>";

					foreach($row as $td)
					{
						print "
<td bgcolor='#f6f6f6'>$td</td>
\n";
					}

					print "</tr>
\n";
				}
				print "</table>
</div>
";
			}
		}

		public function last_insert_id()
		{
			if ($this->insert_id)
			{
				return $this->insert_id;
			}
		}

		// Counts all records from a table
		public function count_all($table)
		{
			if (!$table)
			{
				exit("No Table Specified !!");
			}

			$this->result = mysql_query("select count(*) as total from $table") or $this->setError(mysql_error(), mysql_errno());
			$this->query = "select count(*) as total from $table";

			if ($this->result)
			{
				$this->row = mysql_fetch_array($this->result);
				return intval($this->row["total"]);
			}
		}

		// Counts records based on specified criteria
		public function count_rows($command)
		{
			# Params:
			# 		$command = query command

			if (!$command)
			{
				exit("No Query Command Specified !!");
			}

			$this->query = $command;
			$this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno());

			if ($this->result)
			{
				return intval(mysql_num_rows($this->result));
			}
		}

		// Updates a row if it exists or adds if it doesn't already exist.
		public function insert_update($table, $data, $condition)
		{
			# Params:
			# 		$table = the name of the table
			# 		$data = field/value pairs to be added/updated
			# 		$condition = example: where id = 99

			if ($table)
			{
				if ($data)
				{
					if ($condition)
					{
						if ($this->row_exists("select * from $table where $condition"))
						{
							$this->result = mysql_query("update $table set $data where $condition") or $this->setError(mysql_error(), mysql_errno());
							$this->query = "update $table set $data where $condition";

							if ($this->result)
							{
								$this->affected = intval(mysql_affected_rows());
								// return the number of rows affected
								return $this->affected;
							}
						}
						else
						{
							$this->result = mysql_query("insert into $table set $data") or $this->setError(mysql_error(), mysql_errno());
							$this->query = "insert into $table set $data";

							if ($this->result)
							{
								$this->insert_id = intval(mysql_insert_id());
								$this->affected = intval(mysql_affected_rows());
								// return the number of rows affected
								return $this->affected;
							}
						}
					}
					else
					{
						print "No Condition Specified !!";
					}
				}
				else
				{
					print "No Data Specified !!";
				}
			}
			else
			{
				print "No Table Specified !!";
			}
		}

		// Runs the sql query with claus "limit x, x"
		public function select_limited($table, $start, $return_count, $condition = null, $order = null)
		{
			# Params:
			# 		$start = starting row for limit clause
			# 		$return_count = number of records to fetch
			# 		$condition = example: where id = 99
			# 		$order = ordering field name

			if ($table && $start >= 0 && $return_count)
			{
				if ($condition)
				{
					if ($order)
					{
						$this->result = mysql_query("select * from $table where $condition order by $order limit $start, $return_count") or $this->setError(mysql_error(), mysql_errno());
						$this->query = "select * from $table where $condition order by $order limit $start, $return_count";
					}
					else
					{
						$this->result = mysql_query("select * from $table where $condition limit $start, $return_count") or $this->setError(mysql_error(), mysql_errno());
						$this->query = "select * from $table where $condition limit $start, $return_count";
					}
				}
				else
				{
					if ($order)
					{
						$this->result = mysql_query("select * from $table order by $order limit $start, $return_count") or $this->setError(mysql_error(), mysql_errno());
						$this->query = "select * from $table order by $order limit $start, $return_count";
					}
					else
					{
						$this->result = mysql_query("select * from $table limit $start, $return_count") or $this->setError(mysql_error(), mysql_errno());
						$this->query = "select * from $table limit $start, $return_count";
					}
				}

				if ($this->result)
				{
					$this->rows = intval(mysql_num_rows($this->result));
					// return the query resource for later processing
					return $this->result;
				}
			}
			else
			{
				print "Parameter Missing !!";
			}
		}

		#################################################
		#				Utility Functions				#
		#################################################

		// Counts rows from last Select query
		public function count_select()
		{
			if ($this->rows)
			{
				return $this->rows;
			}
		}

		// Gets the number of affected rows after Operational query has executed
		public function count_affected()
		{
			if ($this->affected)
			{
				return $this->affected;
			}
		}

		// Checks whether a table has records
		public function has_rows($table)
		{
			$rows = $this->count_all($table);

			if ($rows)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		// Checks whether or not a row exists with specified criteria
		public function row_exists($command)
		{
			# Params:
			# 		$command = query command

			if (!$command)
			{
				exit("No Query Command Specified !!");
			}

			$this->query = $command;
			$this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno());

			if ($this->result)
			{
				if (mysql_num_rows($this->result))
				{
					return true;
				}
				else
				{
					return false;
				}
			}
		}

		// Returns single fetched row
		public function fetch_row($command)
		{

			if (!$command)
			{
				exit("No Query Command Specified !!");
			}

			$this->query = $command;
			$this->result = mysql_query($command) or $this->setError(mysql_error(), mysql_errno());

			if ($this->result)
			{
				$this->rows = intval(mysql_num_rows($this->result));
				$this->row = mysql_fetch_object($this->result);
				return $this->row;
			}
		}

		// Returns single field value
		public function fetch_value($table, $field, $condition = null)
		{

			if (!$table || !$field)
			{
				exit("Arguments Missing !!");
			}

			$query = "select $field from $table";

			if ($condition != null)
			{
				$query = "select $field from $table where $condition";
			}

			$this->query = $query;
			$this->result = mysql_query($query) or $this->setError(mysql_error(), mysql_errno());

			if ($this->result)
			{
				$this->rows = intval(mysql_num_rows($this->result));
				$this->row = mysql_fetch_object($this->result);
				return $this->row->$field;
			}
		}

		// Returns the last run query, useful for debugging queries
		public function last_query()
		{
			if ($this->query)
			{
				return $this->alert_msg($this->query);
			}
		}

		// Gets today's date
		public function get_date($format = null)
		{
			# Params:
			#		$format = date format like Y-m-d

			if ($format)
			{
				$today = date($format);
			}
			else
			{
				$today = date("Y-m-d");
			}

			return $today;
		}

		// Gets currents time
		public function get_time($format = null)
		{
			# Params:
			#		$format = date format like H:m:s

			if ($format)
			{
				$time = date($format);
			}
			else
			{
				$time = date("H:m:s");
			}

			return $time;
		}

		// Adds slash to the string irrespective of the setting of getmagicquotesgpc
		public function smartslashes($value)
		{
			if (get_magic_quotes_gpc())
			{
				$value = stripslashes($value);
			}

			if (!is_numeric($value))
			{
				$value = mysql_real_escape_string($value);
			}

			return $value;
		}

		// This function can be used to discard any characters that can be used to manipulate the SQL queries or SQL injection

		/* EXAMPLE USE:

			if (is_valid($_REQUEST["username"]) === true && is_valid($_REQUEST["pass"]) === true)
			{
				//login now
			}
		*/

		public function is_valid($input)
		{
			$input = strtolower($input);

			if (str_word_count($input) > 1)
			{
				$loop = "true";
				$input = explode(" ",$input);
			}

			$bad_strings = array("'","--","select","union","insert","update","like","delete","1=1","or");

			if ($loop)
			{
				foreach($input as $value)
				{
					if (in_array($value, $bad_strings))
					{
					  return false;
					}
					else
					{
					  return true;
					}
				}
			}
			else
			{
				if (in_array($input, $bad_strings))
				{
				  return false;
				}
				else
				{
				  return true;
				}
			}
		}

		// lists tables of database
		public function list_tables()
		{
			$this->result = mysql_query("show tables");
			$this->query = "show tables";

			if ($this->result)
			{
				$tables = array();
				while($row = mysql_fetch_array($this->result))
				{
					$tables[] = $row[0];
				}

				foreach ($tables as $table)
				{
					print $table . "
";
				}
			}
		}

		// provides info about given table
		public function table_info($table)
		{
			if ($table)
			{
				$this->result = mysql_query("select * from $table");
				$this->query = "select * from $table";

				$fields = mysql_num_fields($this->result);
				$rows   = mysql_num_rows($this->result);
				$table = mysql_field_table($this->result, 0);

				print "	The '<strong>" . $table . "</strong>' table has <strong>" . $fields . "</strong> fields and <strong>" . $rows . "</strong>
						record(s) with following fields.\n
<ul>";

				for ($i=0; $i < $fields; $i++)
				{
					$type  = mysql_field_type($this->result, $i);
					$name  = mysql_field_name($this->result, $i);
					$len   = mysql_field_len($this->result, $i);
					$flags = mysql_field_flags($this->result, $i);

					print "<strong>
	<li>" . $type . " " . $name . " " . $len . " " . $flags . "</strong></li>
\n";
				}
				print "</ul>
";

			}
			else
			{
				print "The table not specified !!";
			}
		}

		// displays any mysql errors generated
		public function display_errors()
		{
			if ($this->show_errors == false)
			{
				if ($this->emsg)
				{
					print '
<div style="background:#f6f6f6; padding:5px; font-size:13px; font-family:verdana; border:1px solid #cccccc;">
							<span style="color:#ff0000;">MySQL Error Number</span> : ' . $this->eno . '
							<span style="color:#ff0000;">MySQL Error Message</span> : ' . $this->emsg . '</div>
';
				}
				else
				{
					print '
<div style="background:#f6f6f6; padding:5px; font-size:13px; font-family:verdana; border:1px solid #cccccc;">
							<strong>No Erros Found !!</strong></div>
';
				}
			}
		}

		// to display success message
		public function success_msg($msg)
		{
			print '
<div align="center" style="background:#EEFDD7; padding:5px; font-size:13px; font-family:tahoma, verdana; border:1px solid #8DD607; margin:5px 0px 5px 0px;">
					<strong>' . $msg . '
					</strong></div>
';
		}

		// to display failure message
		public function failure_msg($msg)
		{
			print '
<div align="center" style="background:#FFF2F2; padding:5px; font-size:13px; font-family:tahoma, verdana; border:1px solid #FF8080; margin:5px 0px 5px 0px;">
					<strong>' . $msg . '
					</strong></div>
';
		}

		// to display general alert message
		public function alert_msg($msg)
		{
			print '
<div align="center" style="background:#FFFFCC; padding:5px; font-size:13px; font-family:tahoma, verdana; border:1px solid #CCCC33; margin:5px 0px 5px 0px;">
					<strong>' . $msg . '
					</strong></div>
';
		}

	////////////////////////////////////////////////////////
	}

Enjoy and reply with your feedbacks :)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s