Location: PHPKode > scripts > mysqlix > mysqlix/mysqlix.class.php
<?
	/**
	 * This class extends the very useful predefined class mysqli in a way that adding some useful function.
	 * To get more useful function of class mysqli, read details on the class mysqli documentation.
	 * @link http://www.php.net/manual/en/ref.mysqli.php
	 * @author Anton Rifco Susilo <hide@address.com>
	 * @version 1.0
	 * @access public
	 * @copyright never
	 * @todo In the future release, I will add function to create stored mySQL function/procedure, index, create table, create view, and selection from multiple table(s). Send bug reports, patches, feature requests, complaints & hate mail (no spam thanks) to my email 
	 * 
	 */
	class mysqlix extends mysqli
	{
		/**
		 * Contains current query statement
		 * @since version 1.0
		 * @access public
		 * @var string
		 */		
		public $statement;
		public $latestdb;
		
		/**
		 * Constructor of class mysqlix.
		 * Connect database using mysqli function
		 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @param string (OPTIONAL)$hostname mySQL server hostname that want to be connected. Use localhost if not defined
		 * @param string (OPTIONAL)$username Username of mySQL server. Use root if not defined
		 * @param string (OPTIONAL)$password Password of defined username of mySQL server. Use '' if not defined
		 * @param string (OPTIONAL)$dbname The name of database that want to be connected.
		 * @param string (OPTIONAL)$port Define Port to connect. Will use default if not given
		 * @param string (OPTIONAL)$socket Define socket to connect. Will use default if not given
		 */
		public function mysqlix( $hostname = 'localhost', $username = 'root', $password = '', $dbname = null, $port = null, $socket = null )
		{
			$this->mysqli($hostname, $username, $password, $dbname, $port, $socket);
		}
		
		/**
		 * mysqli overloading function select_db()
		 * Update variable $latestdb on calling this function
		 * @param string $dbname Name of database used
		 * @return boolean
		 * @since version 1.0
		 */
		public function select_db( $dbname )
		{
			if( parent::select_db($dbname) )
			{
				$this->latestdb = $dbname;
				return true;
			}
			else
				return false;
		}
		
		/**
		 * mysqli Overloading function query.
		 * Catch query 'use', and then update variabel $latestdb to latest db connected
		 * @since version 1.0
		 * @param string $query The query specified by user
		 * @param boolean $checkdb (OPTIONAL)Should variable $latestdb updated
		 * @return mixed 
		 */
		public function query( $query, $checkdb = true )
		{
			if( $checkdb && stripos( $query, 'use ' ) !== false )
			{
				if( parent::query( $query ) )
				{
					$this->latestdb = substr( trim($query), 4 );
				}
				else
					parent::query( $query );
			}
			else
				return parent::query( $query );
		}
		
		/**
		 * destructor of class mysqli.
		 * Close current connection
		 */
		public function __destruct()
		{
			$this->close();
		}
		
		/**
		 * Simple insert to a table in defined database.
		 * Define name of table in $tablename, and
		 * Specify each name and column that want to be inserted, in a array(key => value) $arrcolumn
		 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @param string $tablename Contains the name of table that you want to be inserted
		 * @param mixed $arrcolum Contains array of column with its value that want to be inserted, the name of each column shoud be exist in the table $tablename
		 * @return boolean Whether the insert process succeed or not
		 */
		public function insert( $tablename, $arrcolumn )
		{
			$this->statement = "INSERT INTO `$tablename` (";
			foreach( $arrcolumn as $name => $value )
			{
				$this->statement .= $name;
				if( next($arrcolumn) != null )
					$this->statement .= ", ";
				else
					$this->statement .= ") ";
			}
			$this->statement .= "VALUES (";
			foreach( $arrcolumn as $name => $value )
			{
				if( $name == 'password' )
					$this->statement .= "PASSWORD('$value') ";
				else if( stripos( $value,'SELECT ' ) !== false )
					$this->statement .= $value; //this will handle value that retrieved using query
				else
					$this->statement .= "'$value' ";
				if( next($arrcolumn) != null )
					$this->statement .= ", ";
				else
					$this->statement .= ") ";
			}
			return $this->query( $this->statement );
		}
		
		/**
		 * Simple update to a table in defined database.
		 * This function support update with where clause. Use variable $tablename to define name of table, 
		 * array of string $arrcolumn will contain column and its value that want to be updated,
		 * and finally array of string $arrwhere will contain column name and its value that you want them to be put in where clause 
		 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @param string $tablename Specify the name of table that want to be updated
		 * @param mixed $arrcolumn Contains column(s) with its value that need to be updated
		 * @param mixed $arrwhere (OPTIONAL)Contains column(s) with its desired value that will be put in where clause (as constraints) in the query
		 * @return boolean Whether the update process suceed or not
		 */
		public function update( $tablename, $arrcolumn, $arrwhere = array())
		{
			$this->statement = "UPDATE `$tablename` SET ";
			foreach( $arrcolumn as $name => $value )
			{
				$this->statement .= "`$name` = '$value' ";
				if( next($arrcolumn) != null )
					$this->statement .= ", ";
			}
			if( count($arrwhere) > 0 )
				$this->statement .= "WHERE ";
			foreach( $arrwhere as $name => $value )
			{
				if( $name == 'password' )
					$this->statement .= "`$name` = PASSWORD('$value') ";
				else if( stripos( $value,'SELECT ' ) !== false )
					$this->statement .= "`$name` = " . $value; //this will handle value that retrieved using query
				$this->statement .= "`$name` = '$value' ";
				if( next($arrwhere) != null )
					$this->statement .= "AND ";				
			}
			return $this->query( $this->statement );
		}
		
		/**
		 * Select some / all column (s) of a table.
		 * Define table name in $tablename, <br>
		 * Define column(s) in $tablename that you want to retrieve in each array $arrcolumn 's name
		 * If you want to add constraint(s) in your select query, define it in one or more element(s) array $arrcolumn 's value. 
		 * If you dont want to put constraint (no where clause), leave blank each element of array $arrcolumn 's value
		 * $compare variable specify how the variable in database want to be compared, by '=', 'LIKE', or 'REGEXP'
		 * To add order attribute on your select query, you can specify which column it should be ordered using variable $orderby (a column of your table), and its singly on variable $singly (could be 'ASC' or 'DESC')
		 * To limit its selection, you can combine variable $limit and $n, where $limit will limit the selected query to a specified value of integer, and variable $n command the sql to return the ($n+1) st/nd/th $limit of returned query, 
		 * for example $limit = 15, and $n = 0, the result will return the first 15 whatever the query returned	
		 * note : if you want to make a customized search like username = '%anton%', you should specify it on the value of each $arrcolumn's elements
		 * e.g : select( 'user', array('username' => 'anton','score' => '4'), 'REGEXP', 'username', 'ASC', '30', 0 )
		 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @param string $tablename Specify the name of table you want to select
		 * @param mixed $arrcolumn (OPTIONAL)Handle which column you want to retrieve, and where clause. Leave all value blank if you dont need constraint (where clause)
		 * @param string $compare (OPTIONAL)How where clause want to be compared, '=', 'LIKE', or 'REGEXP'
		 * @param string $orderby (OPTIONAL)Specify which column the tupple retrieved should ordered by
		 * @param string $singly (OPTIONAL)Specify how the order behave. Ascending (ASC) or Descending (DESC)
		 * @param int $limit (OPTIONAL)Specify how many the data will be retrieved
		 * @param int $n (OPTIONAL)Relate to $limit. Which $n th $limit should be retrieved
		 * @return mixed Retrieved tupple(s) of query
		 */
		public function select( $tablename, $arrcolumn = null, $compare = '=', $orderby = null, $singly = 'ASC', $limit = null, $n = 0 )
		{
			$temp_num = 0;
			$this->statement = "SELECT ";
			if($arrcolumn == null)
			{
				$this->statement .= "* ";
				$this->statement .= "FROM `$tablename` ";
			}
			else
			{
				foreach( $arrcolumn as $name => $value)
				{
					if( $value != '' )
						$temp_num++;
					$this->statement .= "`$name`, ";
				}
				$this->statement = substr($this->statement, 0, strlen($this->statement) - 2);
				$this->statement .= " FROM `$tablename` ";
				if( $temp_num > 0 )
					$this->statement .= "WHERE ";
				foreach( $arrcolumn as $name => $value)
				{
					if( $value != '' )
					{
						$this->statement .= "`$name` $compare '$value' ";
						if( next($arrcolumn) != null )
							$this->statement .= "AND ";
					}
				}
			}
			if( $orderby != null )
				$this->statement .= "ORDER BY $orderby $singly ";
			if( $limit > 0 )
			{
				$n1 = $n * $limit;
				$this->statement .= "LIMIT $n1, $limit ";
			}
			return $this->query( $this->statement );
		}
		
		/**
		 * This function can secure your multi query in a way that it will make a checkpoint before processing first query<br>
		 * And, if there's at least one query error, they are rolled back
		 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @param mixed $arrquery Contains array of queries
		 * @return boolean Whether multi query succeed or not
		 */
		public function secure_multi_query($arrquery)
		{
			$success = true;
			$this->autocommit(FALSE); //set off the php mysql autocommit setting
			$this->commit(); //commit here to make a checkpoint
			$this->statement = "";
			foreach( $arrquery as $value )
			{
				$this->statement .= $value . "; ";
				$success = $success && $this->query( $value ); //state each query
				if( !$success )
					break;
			}
			if( !$success )
				$this->rollback();//if at least one of the given queries got error, we will rollback to last commit state
			$this->autocommit(TRUE);
				
			return $success;
		}
		
		/**
		 * Create a trigger in your mySQL database server.
		 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @param string $triggername Specify the name of trigger 
		 * @param string $moment The Moment that you want your trigger activated, should be 'BEFORE', or 'AFTER'
		 * @param string $event The event of database activities that trigger will be activated. Relates to @tablename
		 * @param string $tablename The name of table that the trigger relates to
		 * @param mixed $arraction The action(s) that will be triggered
		 * @return boolean Whether the creation of trigger succeed or not
		 * e.g : create_trigger('minusIt', 'loan', array("UPDATE debt SET money = 'money - 10' WHERE user = 'anton'"), 'AFTER', 'UPDATE');
		 */
		public function create_trigger( $triggername, $tablename, $arraction, $moment = 'BEFORE', $event = 'INSERT' )
		{
			$this->statement = 
			"CREATE TRIGGER $triggername $moment $event ON $tablename 
			FOR EACH ROW
			BEGIN								
			";
			foreach($arraction as $value)
			{
				$this->statement .= $value . "; ";			
			}
			$this->statement .= 
			"
			END;";
			if( $this->multi_query($this->statement) === false )
			{
				$this->query("DROP TRIGGER $triggername");
				if( $this->multi_query($this->statement) === false )
					return false;
				else
					return true;
			}
			else
				return true;
		}
		
		/**
		 * Drop one or more trigger(s)<br>
		 * It accepts a string that contain the name of trigger or array of trigger's name
		 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @param string|mixed $triggernames String or array of string that contains trigger(s) name
		 * @return boolean Wheter the drop process succeed or not
		 */
		public function drop_trigger( $triggernames )
		{
			$this->autocommit(FALSE); //set off the php mysql autocommit setting
			$this->commit(); //commit here to make a checkpoint
			
			if( is_array($triggernames) )
			{
				$this->statement = "";
				foreach( $triggernames as $value )
					$this->statement .= "DROP TRIGGER $value;";
			}
			else
				$this->statement = "DROP TRIGGER $triggernames;";
				
			if( $this->multi_query( $this->statement ) === false )
			{
				$this->rollback();
				$this->autocommit(TRUE);
				return false;
			}
			else
			{
				$this->autocommit(TRUE);
				return true;
			}
		}
		
		/**
		 * Creating a database with specified parameter(s)
		 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @param string $dbname Specify the name of database
		 * @param boolean $ifnoexist Should query behave 'IF NO EXIST'
		 * @param string $charset (OPTIONAL)Specify character set of database. If not given, will use default
		 * @param string $collate (OPTIONAL)Specify collate of database. If not given, will use default
		 * @return boolean Wheter the creation succeed or not
		 */
		public function create_db( $dbname, $ifnotexist = true, $charset = null, $collate = null )
		{
			$this->statement = "CREATE DATABASE ";
			if( $ifnotexist )
				$this->statement .= "IF NOT EXISTS ";
			$this->statement .= "`$dbname` ";
			if( $charset != null )
				$this->statement .= "DEFAULT CHARACTER SET $charset ";
			if( $collate != null )
				$this->statement .= "DEFAULT COLLATE $collate ";
			return $this->query( $this->statement );
		}
		
		/**
	 	 * Drop one or more database(s)
		 * It accept string as $dbnames if you want to drop just one, or array of string to drop more than one databases
	 	 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @param string|mixed $dbnames String related to the name of db to be dropped, or a set of array of String that its elements contain string related to the name of db to be dropped 
	 	 * @return boolean Wheter the drop process succeed or not
		 */
		public function drop_db( $dbnames )
		{
			$this->autocommit(FALSE); //set off the php mysql autocommit setting
			$this->commit(); //commit here to make a checkpoint
			if( is_array($dbnames) )
			{
				$this->statement = "";
				foreach( $dbnames as $value )
					$this->statement .= "DROP DATABASE $value;";
			}
			else
				$this->statement = "DROP DATABASE $dbnames;";
			
			if( $this->multi_query( $this->statement ) === false )
			{
				$this->rollback();
				$this->autocommit(TRUE);
				return false;
			}
			else
			{
				$this->autocommit(TRUE);
				return true;
			}
		}
		
		/**
		 * Function to manipulate array used in create_table as $arrcolumn
		 * @since version 1.0
		 * @see function create_table
		 * @return mixed 
		 */
		public function add_column( $arrcolumn, $name, $type, $length = null, $null = false, $autoinc = false, $primary = false, $default = null, $comment = null, $charset = null, $collate = null )
		{
			$temp = array();
			$temp2 = $arrcolumn;
			$temp['name'] = $name;
			$temp['type'] = $type;
			$temp['length'] = $length;
			$temp['null'] = $null;
			$temp['autoinc'] = $autoinc;
			$temp['primary'] = $primary;
			$temp['default'] = $default;
			$temp['comment'] = $comment;
			$temp['charset'] = $charset;
			$temp['collate'] = $collate;
			array_push( $temp2, $temp );
			return $temp2;
		}
		
		/**
		 * Create table in a defined database
		 * @since version 1.0
		 * @see function add_column to help in manipulating array $arrcolumn 
		 * @return mixed
		 */
		public function create_tables( $tablename, $arrcolumn, $comment = "", $arrindex = null, $charset = null, $collate = null, $type = "MYISAM" )
		{
			$this->statement = "CREATE TABLE `$tablename` ( ";
			foreach ( $arrcolumn as $value )
			{
				$colname = $value['name']; //mandatory
				$coltype = $value['type']; //mandatory
				$collength = $value['length']; //optional, for some type is mandatory
				$colcharset = $value['charset']; //optional
				$colcollate = $value['collate']; //optional
				$colnull = $value['null']; ////mandatory
				$coldefault = $value['default']; //optional
				$colautoinc = $value['autoinc']; //optional
				$colprimary = $value['primary']; //optional
				$colcomment = $value['comment']; //optional
				$this->statement .= "`$colname` $coltype ";
				if( $collength != null )
					$this->statement .= "($collength) ";
				if( $colcharset != null )
					$this->statement .= "CHARACTER SET $colcharset ";
				if( $colcollate != null )
					$this->statement .= "COLLATE $collate ";
				if( $colcharset != null )
					$this->statement .= "CHARACTER SET $colcharset ";
				if( !$colnull )
					$this->statement .= "NOT ";
				$this->statement .= "NULL ";
				if( $colautoinc )
					$this->statement .= "AUTO_INCREMENT PRIMARY KEY ";
				else if( $colprimary )
					$this->statement .= "PRIMARY KEY ";
				if( $coldefault != null )
					$this->statement .= "DEFAULT '$coldefault' ";
				if( $colcomment != null )
					$this->statement .= "COMMENT '$colcomment' ";
				$this->statement .= ", ";							
			}
			
			if( $arrindex != null )
			{
				$this->statement .= "INDEX( ";
				foreach ( $arrindex as $value )
				{
					$this->statement .= "`$value`";
					if( next($arrindex) != null )
						$this->statement .= ", ";
				}
				$this->statement .= ")";
			}
			else
				$this->statement = substr($this->statement, 0, strlen($this->statement) - 2);
			$this->statement .= ") TYPE = $type COMMENT = '$comment'";
			if( $charset != null )
				$this->statement .= "CHARACTER SET $charset ";
			if( $collate != null )
				$this->statement .= "COLLATE $collate ";
			return $this->query( $this->statement );
		}
		
		/**
		 * Drop one or more table(s)
		 * It accept string as $tablenames if you want to drop just one, or array of string to drop more than one tables
	 	 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @todo Actually, I have function to create table, but hesitate it will be useful. Perhaps, next release will contain that
	 	 * @param string|mixed $tablenames String related to the name of table to be dropped, or a set of array of String that its elements contain string related to the name of table to be dropped 
		 * @return boolean Wheter the drop process succeed or not
	 	 */
		public function drop_table( $tablenames )
		{
			$this->statement = "";
			if( is_array($tablenames) )
			{
				foreach( $tablenames as $value )
					$this->statement .= "DROP TABLE `$value`; ";
			}	
			else
				$this->statement = "DROP TABLE IF EXISTS `$tablenames`;";
			
			if( $this->multi_query( $this->statement ) === false )
				return false;
			else
				return true;
		}
	
		/**
		 * Return comment on a table (if you specify it in creation)
		 * This function only work if your mySQL server contains database information_schema that contains all information about your database server, probably made by web server package, like XAMPP
		 * @since version 1.0
		 * @see example.php To get information how to use this function
		 * @param string $tablename Contains the name of table that you want the function to returned its comment
		 * @param string $dbname (OPTIONAL)you can specify the database name where you put your $tablename table. This variable will handle ambiguity when there's 2 table with the same name but in the different database
		 * @return string Comment of specified table
		 */
		public function table_comment( $tablename, $dbname = null )
		{
			$this->query("USE information_schema", false);		
			
			$this->statement = "SELECT `TABLE_COMMENT` FROM `TABLES` WHERE `TABLE_NAME` = '$tablename'";
			if( $dbname != null )
				$this->statement .= " AND `TABLE_SCHEMA` = '$dbname'";
			$result = $this->query( $this->statement );
			$row = $result->fetch_row();
			$result->close();
			$this->select_db($this->latestdb);
			return $row[0];
		}
		
		/**
		 * @see function table_comment( $tablename, $dbname = null )
		 * Return comment of table(s) that exist on a specified database
		 * This function works as table_comment( $tablename, $dbname = null ) function, <br>
		 * except that it will search all comment(s) for table(s) that exist in a database
		 * @todo Actually, I want to add function to retrieve table/database 's statistic. But never realized :) .Perhaps it will exist in future release
		 * @see example.php To get information how to use this function
		 * @since version 1.0
		 * @param string $dbname Specify database name
		 * @return mixed Array of array<name, comment> where name is tablename, and comment is comment of the table
		 */
		public function table_comments( $dbname )
		{
			$arrcomments = array();
			
			$this->query("USE information_schema", false);	
			
			$this->statement = "SELECT `TABLE_NAME`,`TABLE_COMMENT` FROM `TABLES` WHERE `TABLE_SCHEMA` = '$dbname'";
			$result = $this->query( $this->statement );
			while( $row = $result->fetch_array() )
			{
				$temp = array("name" => $row[0], "comment" => $row[1]);
				array_push( $arrcomments, $temp );
			}
			$result->close();
			$this->select_db($this->latestdb);
			return $arrcomments;
		}
	}
?>
Return current item: mysqlix