#mysi 1.03
MYSI is a PHP class to management databases in MySQL.
With this PHP class simple, but complete, can be make traditional queries, send to array with the all query rows, getting a single value, recover in every moment the last ID inserted, getting the rows affected of last update or delete and profit additional of functionalities like dates converter, export the DDBB to a text file compressed or uncompressed, save query logs automaticly, ...
This class can automatically handle development and production environments only by changing the values of the following constants.
#Install
Copy all files into repository or directory.
Update / modify the config.php file with your users, passwords and database names for Development and Production.
In addition, you can update / modify the time variables, dat variables, error variables and codification into mysi.php file with your users, passwords and database names for Development and Production.
#Way to use
include "mysi/mysi.php";
$mysi = new MYSI();
$mysi->connect('database_name');
$array = $mysi->query("SELECT id FROM `database_name` WHERE field_name LIKE '%value%'", ARRAY_A);
foreach($array as $key => $value){
echo $key." contains ".$value;
}#Variables Description ##Database Variables #####$selected_rows
Variable that contains number of selected rows for last executed SELECT sentence.
#####$affected_rows
Variable that contains number of affected rows for last executed UPDATE, INSERT or DELETE sentence.
#####$last_insert_id
Variable that contains the last ID inserted.
#####$last_query
Variable that contains the last query executed.
#####$last_error_id
Variable that contains the last ERROR NUMBER that provoked the last query executed.
#####$last_error_msg>
Variable that contains the last ERROR MESSAGE that provoked the last query executed.
##Error Variables #####$_IGNORE_ERRORS
Is a list of Numbers of MySQL errors separated by comma that will be managed in a special way. If you want to know what and how errors manage, you can to see the http://dev.mysql.com/doc/refman/5.0/es/error-handling.html URL.
#####$_WARNING_COLOR
Color to show the WARNING messages.
#####$_ERROR_COLOR
Color to show the ERROR messages.
#####$_SHOW_WARNING_ERROR
If this variable is set to 'true', WARNING messages will be displayed.
#####$_SHOW_IGNORED_ERRORS
If this variable is set to 'false', IGNORED messages aren't displayed. Otherwise, the messages, will be displayed as the others.
#####$_SHOW_CONTROL_MESSAGES
If this variable is set to 'true', ERROR messages will be displayed.
#####$_STOP_WARNING_ERROR
If a warning error is found and this variable is seted to 'true', the application execution is forced to stop execution.
##Date Variables #####$_FORMAT_DATETIME_DB
Datetime format that has set in the database. By default is seted to MySQL format 1970-01-01 1:00:00.
#####$_FORMAT_DATE_DB
Date format that has set in the database. By default is seted to MySQL format 1970-01-01.
#####$_FORMAT_DATETIME_FRMWRK
Datetime format you want to use in the Framework. By default is seted to european format: 31-12-1970 00:00:00.
#####$_FORMAT_DATE_FRMWRK
Date format you want to use in the Framework. By default is seted to european format: 31-12-1970.
##Log Variables #####$_ENABLED_LOG
If this variable is set to 'true', is saved in the database a log entry every time that a page that is accessed. If 'false', it does nothing.
#####$_LOG_TABLE_CREATE_AUTO
Indicates whether to create the table automatically if the database is not created at the time of the call or execution.
#####$_LOG_TABLE_NAME
Is the name of the table that has the configuration of LOG's table. By default is 'dblog'.
#####$_SIZE_LOG_IN_DAYS
LOG size in days. The default is 30 days. If set to zero means you do not want to delete entries in the log.
#####$_SAVE_QUERIES_IN_LOG
If this variable is set to 'true', all queries are saved automatically in MySQL. Otherwise, only events that user requested it will be saved. This is doing directly through insertEntryLog function (...)
#Methods Description ##checkBadWords bool checkBadWords($array)
Check that does not exists bad words in code sent. Bad word is equivalent to prohibited sentences. For example this function is recommended when you want disable CREATE or DROP sentences.
If file has one of array words returns a message error and execution is give by terminated. If return value is 'true' means the code contain bad words.*
#####Parameters:
- array $array - Array of queries that contain the code to check.
#####Example
$lines = file('export.sql');
$badWords = $mysql->checkBadWords($lines);##checkToken bool checkToken( string $string, [string $token = ""])
Function to compare the sent token into $token and the string returned through by $string.
Return a boolean value, 'true' if both tokens are the same, 'false' in another issue.
#####Parameters
string $string - Original string to compare.
string $token - Encrypted string to compare.
#####Example
$mysql->checkToken("Esto es una prueba", "6qblJRamKigUBqqlqWgTQ==");##clean string clean( type $text)
Clear text and prepare to execute.
Returns a string clean of commets and white blanks.
#####Parameters
type $text - Is the array / text with the queries SQL.
#####Example
$mysql->clean("/*!40000 ALTER TABLE `sprint` ENABLE KEYS */; SELECT ...");##compareTokens bool compareTokens( string $token1, [string $token2 = ""])
Function to check two tokens. If $token1 is empty take to compare the returned token by the _ENCODED_TOKEN
Return a boolean value, 'true' if both tokens are the same, 'false' in another issue.
#####Parameters:
string $token1 - First token to compare.
string $token2 - Second token to compare.
#####Example
$mysql->compareTokens("6qblJRamKigUBqqlqWgTQ==");##connect void connect( [string $db = ""])
Function to connect with the database.
This function no return anything
#####Parameters:
Example: // Later of execute the next query, you can recover this values like $id and $name. list($id, $name) = $this->getValues('SELECT id, name FROM customers WHERE id = 1;');string $db - Name of database to connect
#####Example
$mysi->connect();##createTableLog resource createTableLog()
Function to create the LOG data table.
This function hasn't parameters and not return anything.
#####Example
$mysql->createTableLog();##decodeToken string decodeToken( string $token)
Function to decrypted tokens.
Returns a string decrypted.
#####Parameters:
string $token - String to decode
#####Example
$mysql->decodeToken("jHd8dnWBUVtNd4Nvf1CcnKKhlKTlJsgn1GflpKMm6qblJRamKigUBqqlqWgTQ==");##delete void delete( string $table, string $cond)
Function to delete rows from a table
This function no returns anything.
#####Parameters:
string $table - Table name.
string $cond - String with the conditional sentence.
#####Example
$mysql->delete("customers", "id = 1");##disconnect void disconnect()
Close MySQL connection referenced by $this->resource (the MySQL connection identifier).
This function no returns anything.
#####Parameters:
Has no parameters.
#####Example
$mysi->connect();##elapsedTime array elapsedTime( date $dInit, date $dEnd, [string $dInit_format = ""], [string $dEnd_format = ""])
Function to calculate the days difference between two dates like days, hours, minutes and seconds.
Will be returned an array with the format array("d" => 0, "h" => 1, "m" => 33, "s" => 2) with the result.
#####Parameters:
$dInit date $dInit - Initial date.
$dEnd date $dEnd - Final date.
string $dInit_format - Is Optional. * If is empty, by default take the local format defined into class. The possible formats are defined in _FORMAT_DATE_FRMWRK and _FORMAT_DATETIME_FRMWRK depends of if the values is time or date type.
string $dEnd_format - Is Optional. * If is empty, by default take the local format defined into class. The possible formats are defined in _FORMAT_DATE_FRMWRK and _FORMAT_DATETIME_FRMWRK depends of if the values is time or date type.
##encodeToken string encodeToken( string $string)
Function to encrypted tokens. The algorithm is made through to _TOKEN_KEY constant to make the sent string.
Returns String encrypted.
#####Parameters:
- string $string - String to encode
#####Example
$mysql->encodeToken("Complete. SELECT * FROM blog_tags WHERE 1;");##existsToken bool existsToken( [string $token = ""])
Function to check if sent token already is into database.
Returns true if exists, false in another issue.
#####Parameters
string $token - Encrypted string to check.
#####Example
$mysql->existsToken("6qblJRamKigUBqqlqWgTQ==");##export void export( string $exportfilename, [bool $exportdrop = false], [bool|array $exporttables = false], [bool $exportcompresion = false])
Function to export, complete or partially, a database.
This function no returns anything.
#####Parameters:
string $exportfilename - The target filename.
bool $exportdrop - It is optional. Indicates if the table must be dropped before to re-create it.
bool|array $exporttables - It is optional. Its a array that contains the tables of the database that will be stored. You can specify a 'false' value to indicate that all tables of the database are exported. Its default value is false.
bool $exportcompresion - It is optional. If this parameter is seted to 'true', the file will be compressed.
#####Examples
$mysql->export("export.txt");
$mysql->export("export.txt", true);
$mysql->export("export.txt", false, 'enterprises,customers', 'bz2');##fetchArray array fetchArray( [int $type = MYSQL_BOTH])
Retrieves results referenced by $this->resource (the MySQL connection identifier) as an associative array of numbers, names, or both.
This function no returns anything.
#####Parameters
int $type - Is optional. The sent parameter indicates how the partnership will be. Its values can be MYSQL_NUM, MYSQL_ASSOC or MYSQL_BOTH. By default, MYSQL_BOTH is used.
#####Example
$result = $mysql->query("SELECT * FROM names WHERE 1;");
while($row = $mysql->fetchArray()){
echo $row[0]." ".$mysql->utf8($row['name']);
}##fetchObject object|stdClass fetchObject( [string $class = ""])
Retrieves results referenced by $this->resource (the MySQL connection identifier) as an object.
This function no returns anything.
#####Parameters
- string $class - IS the name of the class to instantiate to set the properties and return. In other words, it indicates the object class name resulting where will be transferred.
##field_name string field_name( int $num)
Recover a specific field name of a column. The source to extract this information is the associate to $this->resource.
This function no returns anything.
#####Parameters
int $num - indicates the field number to read the name.*
##free void free()
Free / liberate all memory referenced by $this->resource (the MySQL connection identifier). Only needs to be requested if you are executing queries that required too much memory. The natural behaviour is free / liberate all the memory when the script is ended
#####Parameters
Has no parameters.
#####Example
$mysql->free();##getInfo array getInfo()
Function to recover the client data that browse by the system / web.
Recover, between another data, the name and version browser, IP and operative system. To use this functionality is necessary have loaded previously the getInfo Class. In another words, insert into your code the next line code: include "getInfo.class.php";
Returns an array with all information of user.
#####Parameters
Has no parameters.
#####Example
$mysql->getInfo();##getListValues array getListValues( string $sentence)
Make the request to database and returns a list with the values of the requested fields.
The list is a array which can then be retrieved using the 'list()' PHP instruction, or treat it as a PHP array in every another issue. If no match returns the value set by default _EMPTY_FIELD_BY_DEFAULT.
Returns an array with the result of execute the query.
#####Parameters
string $sentence - Query to execute.
#####Example
// Later of execute the next query, you can recover this values like $id and $name.
list($id, $name) = $this->getValues('SELECT id, name FROM customers WHERE id = 1;');##getNextAutoIncrement string getNextAutoIncrement( string $table, [string $db = ""])
Function to get next auto increment value from a table.
Returns the next value.
#####Parameters:
string $table - Table name.
string $db - Database name. By default the database name used will be the seted by _CURRENT_DB variable.
#####Examples
$mysql->getNextAutoIncrement('customers');
$mysql->getNextAutoIncrement('customers', 'database_name');##getValue string|number getValue( string $sentence, [int $field_number = 0])
Funtion to recover a specific value. Make the request to database sent by first parameter and, if the query is a type of SHOW, EXPLAIN, ... allow choice the column to return setting the second parameter.
If the result is empty, by default the value seted into _EMPTY_FIELD_BY_DEFAULT is returned.
Returns the resulting value.
#####Parameters
string $sentence - Query to execute.
int $field_number - Column to return
Examples
$mysql->getValue("SELECT name FROM customers WHERE id = 1;");
$mysql->getValue("SHOW FULL COLUMNS FROM customers", 1);##insertEntryLog void insertEntryLog( [string $event = ''])
Function to insert a event into LOG table. In addition save, the name and version browser, IP and operative system and page...
This function no returns anuthing
#####Parameters:
string $event - String to save into database.
#####Example
$mysql->insertEntryLog("Access to");##isDate bool isDate( string $value, [string $format = ""])
Check if the sent string is a date type. You must send the format to check.
The parameters than you can to use into $format variable are the PHP same that.
Returns true or false.
#####Parameters
string $value - String to check.
string $format - Format defined.
#####Examples
$mysql->isDate("2012/33", "Y/d"); //is transformed to 02-02-2012, like result will be return 'true'.
$mysql->isDate("oct-01", "m-d"); //is transformed to 01-10-2012, like result will be return 'true'.
$mysql->isDate("oct", "m"); //will be return error, like result will be return 'false'.
$mysql->isDate("31", "d"); //will be return error, like result will be return 'false'.
$mysql->isDate("31/10/2012", ''); //is transformed to 31-10-2012, like result will be return 'true'.##isNumber bool isNumber( number $value)
Check if the sent value is a number
Returns true or false.
Parameters:
number $value - Value to test
##isString bool isString( number $value)
Check if the sent value is a string
Returns true or false.
Parameters:
string $value - Value to test
##mkTimeFormat int mkTimeFormat( string $value, [string $format = ""])
Convert a date format, from format received by $format to UNIX format.
The values with only characters or numbers produce a error. You must to have caution because this function has behaviours defined to help into the development.
If the string sent is "2012/33", will be returned 02-02-2012.
If the string sent is "oct-01", will be returned 01-10-2012.
If the string sent is "2012-oct", will be returned 01-10-2012.
Returns the UNIX timestamp
#####Parameters:
string $value - Is string to convert.
string $format - Is target format.
#####Example
$mysql->mkTimeFormat(date("d-m-Y H:i:s"), "d-m-Y H:i:s");##prepare_queries array prepare_queries( string $queries)
Function to prepare complex instructions or several queries simultaneously.
Returns string with the right format to execute into server.
#####Parameters
string $queries - List of queries or sentence of lines multiple.
#####Example
$query="
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|
DELIMITER ;";
$mysql->prepare_queries($query);##query resource query( string $query, [string $output = ""], [bool $prepare = true])
Function to make requests to database.
If _SHOW_CONTROL_MESSAGES is setet to 'true', will be show the ERROR messages into screen. If _SHOW_WARNING_ERROR is setet to 'true', will be show the WARNING messages into screen. If _STOP_WARNING_ERROR is setet to 'true', the execution is stopped like if had have occurred a fatal error.
Returns a pointer to resulting object.
#####Parameters:
string $query - Sentence to execute.
string $output - Type of data to return. The possible options are: ARRAY_A, ARRAY_N, OBJECT.
bool $prepare - For execute sentences of lines multiple like FUNCTIONS or PROCEDURES
#####Example
$mysi->query("INSERT INTO custumers (`id` , `name`) VALUES (1, 'Islavisual')`");
$mysi->query("SELECT * FROM `customers` WHERE name LIKE '%IS%'");
// Recover users into array
$users = $mysi->query("SELECT * FROM `user` WHERE 1 AND `status` = 1", ARRAY_A);##real_escape string real_escape( string $sentence)
Escapes special characters in a string for use in SQL statements referenced by $this->resource (the MySQL connection identifier).
Return string escaped.
#####Parameters
string $sentence - Query to execute.
#####Example
$mysi->real_escape(sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'");##showError void showError()
Function to update and show the last occurred error.
This function no returns anything.
#####Parameters
No has parameters.
##time2Array array|bool time2Array( $date $date, $format $format)
Function to convert a date of type string to array format.
The parameters than you can to use into $format variable are the PHP same that.
#####Parameters:
string $date - String date type to convert.
string $format - String with the input format.
#####Example
@extract($this->time2Array($value,"Y-m-d"));##toDateFormat toDateFormat( string $value, [string $format_source = ""], [string $format = ""])
Convert a string with date format, from format seted by $format_source variable to $format variable.
The values with only characters or numbers produce a error.
If $format_source is empty, by default take the local format defined into class. The possible formats are defined in _FORMAT_DATE_FRMWRK and _FORMAT_DATETIME_FRMWRK depends of if the values is time or date type.
If $format is empty, by default take the local format defined into class in the _FORMAT_DATETIME_DB variable You must to have caution because this function has behaviours defined to help into the development.
If the string sent is "2012/33", will be returned 02-02-2012. If the string sent is "oct-01", will be returned 01-10-2012. If the string sent is "2012-oct", will be returned 01-10-2012.
Returns a date in string format.
#####Parameters:
string $value - String to transform.
string $format_source - Source format.
string $format - Target format.
#####Example
$mysql->toDateFormat("2012-oct-10","Y-m-d", "d-m-Y");##usedb (Deprecated function) usedb( $db_name $db_name)__
Select a database.
This function no returns anything.
#####Parameters:
string $db_name - Database name
##utf8 string utf8( string $t)
Function to check if a text is encoded like ISO-8859-1. If the response is 'true', the string is coverted to UTF-8, otherwise, the text is decoded.
Returns a string in UTF-8 format.
#####Parameters
string $t - String to check
#####Example
$mysql->utf8($row['name']);##__get
__string get( string $name)
Function allow to recover variables of private use from out of this class.
To allow recover a variable, the variable must be declare into $_ALLOWED_VARS. If the variable name does not into this list, the system will deny the request and will show an error message.
By default, php$_ALLOWED_VARS contains completedIn, total_queries, last_insert_id, affected_rows and selected_rows vars.
Returns the value of variable
#####Parameters
string $name - The variable name to recover
We have detail description in http://www.islavisual.com/articulos/desarrollo_web/clase-en-php-para-mysql-de-islavisual URL, although is writed in spanish.