<?
/**
* 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;
}
}
?>