Login   Register  
PHP Classes
elePHPant
Icontem

File: quickdb.class.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Sarfraz Ahmed  >  QuickDB  >  quickdb.class.php  >  Download  
File: quickdb.class.php
Role: Class source
Content type: text/plain
Description: main class file
Class: QuickDB
Query MySQL using values passed pragmatically
Author: By
Last change: made some necessary changes to execute method.
Date: 5 years ago
Size: 20,595 bytes
 

Contents

Class file image Download
<?php

	#########################################################
	#			QuickDB->MySQL Wrapper Class				#
	#-------------------------------------------------------#
	#	Author: SARFRAZ AHMED CHANDIO						#
	#			Web Developer								#
	#			The Brains									#
	#			http://www.brainstech.com					#
	#			http://sarfraz-ahmed.blogspot.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 '	<br /><br /><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 . '<br />
						<span style="color:#ff0000;">MySQL Error Message</span> : ' . $msg . '</div><br />';
			}
		}
		
	
		#################################################
		#				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 table from the specified table
		public function get_html($command, $display_field_headers = true, $table_attribs = 'border="0" cellpadding="3" cellspacing="2" 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 '<br /><br /><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;'><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><br /><br />";
			}
		}
		
		
		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
		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 . "<br />";
				}
			}
		}


		// 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<br /><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 '	<br /><br /><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 . '<br />
							<span style="color:#ff0000;">MySQL Error Message</span> : ' . $this->emsg . '</div>';
				}
				else
				{
					print '	<br /><br /><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 '	<br /><br /><div align="center" style="background:#EEFDD7; padding:5px; font-size:13px; font-family:verdana; border:1px solid #8DD607;">
					' . $msg . '
					</div><br />';
		}
	
		// to display failure message
		public function failure_msg($msg)
		{
			print '	<br /><br /><div align="center" style="background:#FFF2F2; padding:5px; font-size:13px; font-family:verdana; border:1px solid #FF8080;">
					' . $msg . '
					</div><br />';
		}

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

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

?>