MySQL DB

In many web projects, it is necessary to have access to a database and therefore having the right functions. Often, the PHP - MySQL couple refers. It is so safe to use a MySQL class grouping all useful functions for usual MySQL databases management tasks.

The documentation for the class which we will explain the use is located at this URL:
http://www.lapinbleu.ch/phpdocumentor/rootpackage/MySQL.html

The functions

The needed functions are:

• MySQL::ConnectDB()
• MySQL::InsertDatas()
• MySQL::SelectRow()
• MySQL::UpdateRow()
• MySQL::DeleteRow()
• MySQL::EmptyTable()
• MySQL::ComplexQuery()
• MySQL::MatchTerms()
• MySQL::ShowColumns()

Which we will add the MySQL::__construct() function, a magical one, which will help us initialize the object during its creation.

All these functions, except the MySQL::__construct() accept the MySQL::debug parameter. This parameter, a boolean, is used, when set to true, to echo the content of the SQL query before executing it.

MySQL::__construct()

With this magical function, it is possible to define the object's variables during its initialization or even directly execute a function of the class.

<?php

$oMySQL = new MySQL(array("database" => "foobase", "table" => "bartable"));

echo $oMySQL->database . "
    " . $oMySQL->table;

// foobase
// bartable

?>

The execution of an action directly at the MySQL object instanciation is done this way:

<?php

$aParams = array(
    "database" => "foodb",
    "table" => "bartable",
    "whereclause" => "testrow = 1"
    );

$sAction = "SelectRow";
$oMySQL = new MySQL($aParams, $sAction);

print_r($oMySQL->result);

/* Result of the query: SELECT * FROM foodb.bartable WHERE testrow = 1; */

?>

MySQL::ConnectDB()

It is never necessary to call this function. Indeed, all functions of this class performs a connection test to database and connects to it if necessary.

This function creates a SQL link to concerned database. Those data are used to make the connection:

• MySQL::dbhost
• MySQL::dbuser
• MySQL::dbpass
• MySQL::database

Those parameters can be defined from two distinct ways:

1. directly in the class
2. through the code instantiating the object, before the function call

This function is returning the SQL link in case of success or an error message on failure.

MySQL::InsertDatas()

This function allows you to insert data in the database with a pair of arrays "field names" / "values".

<?php

$aParams = array(
    "database" => "foodb",
    "table" => "bartable"
    );
$oMySQL = new MySQL($aParams);

$aRows = array(
    "row1", 
    "row2",
    "row3"
    );

$aValues = array(
    "test",
    2,
    "value"
    );


$oMySQL->InsertDatas($aRows, $aValues);

?>

The preceding code is equivalent to the following SQL query:

INSERT INTO foodb.bartable (row1, row2, row3) VALUES ("test", 2, "value");

As can be seen, the use of this function is very simple, again, the inserted values are escaped, which means it is not necessary to take care of special characters.

However, the queries containing mathematical formulas as values are not supported. These will be treated as strings.

MySQL::SelectRow()

This function performs the query "SELECT" and accepts the following parameters:

• MySQL::database : string defining the database on which MySQL::ConnectDB() will attempt to connect. However the query can be executed on a join of different databases.
• MySQL::table : string or array defining respectively the table or the tables list on which the query will be executed.
• MySQL::whereclause : conditional string used for the data filter.
• MySQL::distinct : boolean used to fusion the identical results when set to true.
• MySQL::leftjoin : array used to make LEFT JOIN table ON condition where keys are "table" values and values are the "condition" values.
• MySQL::groupby : array containing the row name on which the results will be grouped.
• MySQL::orderrow : string containing the name of the row on which the results will be sorten.
• MySQL::limitfrom : numerical value corresponding to the shift of the first record to return.
• MySQL::limitqty : numerical value corresponding to the maximum rows to return.
• MySQL::SelectRow( $aRows ) : array corresponding to the names of the rows to return.

So, a simple query looks like this:

<?php

$aParams = array(
    "database" => "foodb",
    "table" => "bartable"
    );
$oMySQL = new MySQL($aParams);
$oMySQL->SelectRow();

?>

Corresponding to the following query:

SELECT * FROM foodb.bartable WHERE 1;

It is then possible, with the help of different parameters to quickly perform complex queries.

<?php

$aParams = array(
    "database" => "foodb",
    "table" => "bartable",
    "whereclause" => "row2=3",
    "limitfrom" => 5,
    "limitqty" => 12,
    "groupby" => "row3",
    "orderrow" => "row1"
    );

$oMySQL = new MySQL($aParams);

$aRows = array(
    "row1",
    "row2",
    "row3"
    );

$oMySQL->SelectRow($aRows);

?>

This code is equivalent to the query:

SELECT row1, row2, row3 FROM foodb.bartable WHERE row2=3 GROUP BY row3 LIMIT 5,12;

This function covers most of the needs of the selection of data in a MySQL database and helps performing fairly complex queries.

MySQL::UpdateRow()

This function is used to update data in different columns of rows filtered by a condition. This function is used the same way as the MySQL::InsertDatas() function unlike a value can be set to the MySQL::whereclause parameter. PAY ATTENTION: if no value is passed to MySQL::whereclause, it will be considered as "WHERE 1" and all the table will be modified. 

<?php

$aParams = array(
    "database" => "foodb",
    "table" => "bartable",
    "whereclause" => "ID=3"
    );

$oMySQL = new MySQL($aParams);

$aRows = array(
    "row1",
    "row2",
    "row3"
    );

$aValues = array(
    5,
    "foo",
    "bar"
    );

$oMySQL->UpdateRow($aRows, $aValues);

?>

This code is equivalent to the following query:

UPDATE foodb.bartable SET row1=2, row2="foo", row3="bar" WHERE ID=3;

MySQL::DeleteRow()

This function is used to remove some rows from the database using a condition.

The following parameters are supported:

• MySQL::database : string defining the database on which MySQL::ConnectDB() will attempt to connect. However the query can be executed on a join of different databases.
• MySQL::table : string or array defining respectively the table or the tables list on which the query will be executed.
• MySQL::whereclause : conditional string used for the data filter.
• MySQL::distinct : boolean used to fusion the identical results when set to true.
• MySQL::leftjoin : array used to make LEFT JOIN table ON condition where keys are "table" values and values are the "condition" values.
• MySQL::groupby : array containing the row name on which the results will be grouped.
• MySQL::orderrow : string containing the name of the row on which the results will be sorten.
• MySQL::limitfrom : numerical value corresponding to the shift of the first record to return.
• MySQL::limitqty : numerical value corresponding to the maximum rows to return.

<?php

$aParams = array(
    "database" => "foodb",
    "table" => "bartable",
    "whereclause" => "ID=3"
    );

$oMySQL = new MySQL($aParams);
$oMySQL->DeleteRow();

?>

This code is equivalent to the following query:

DELETE FROM foodb.bartable WHERE ID=3;

MySQL::EmptyTable()

This function is used to empty the specified table with an optimized way. The MySQL::table parameter can either contain a string or an array respectively corresponding toa table or a table list which will be emptied.

<?php

$aParams = array(
    "database" => "foodb",
    "table" => "bartable"
    );

$oMySQL = new MySQL($aParams);
$oMySQL->EmptyTable();

?>

This code is equivalent to the following query:

TRUNCATE TABLE foodb.bartable;

MySQL::ComplexQuery()

This function allows you to directly execute SQL complex queries. The MySQL::complex_query parameter must be provided.

<?php

$complexSQL = "SELECT * FROM bartable WHERE 1;";

$aParams = array(
    "database" => "foodb",
    "table" => "bartable",
    "complex_query" => $complexSQL
    );

$oMySQL = new MySQL($aParams);
$oMySQL->ComplexQuery();

?>

This code is equivalent to the following query:

SELECT FROM bartable WHERE 1;

MySQL::MatchTerms()

This function allows you to search terms in FULLTEXT rows.

The used parameters are the following:

• MySQL::database : string defining the database on which MySQL::ConnectDB() will attempt to connect.
• MySQL::table : string defining the table on which the query will be executed.
• MySQL::order : string containing the name of the row on which the results will be sorten.
• MySQL::limitfrom : numerical value corresponding to the shift of the first record to return.
• MySQL::limitqty : numerical value corresponding to the maximum rows to return.
• MySQL::matchvalue : numerical value corresponding to the minimal searched occurrences.
• MySQL::MatchTerms( $rows2show, ... , ... ) : string or array respectively corresponding to the row or the list of rows to return in the result.
• MySQL::MatchTerms( ... , $rows2search, ... ) : string or array respectively corresponding to the row or the list of rows to search in.
• MySQL::MatchTerms( ... , ... , $searchterm ) : string corresponding to the searched term.

<?php

$aParams = array(
    "database" => "foodb",
    "table" => "bartable",
    "limitfrom" => 0,
    "limitqty" => 10
    );

$oMySQL = new MySQL($aParams);

$aCols2Show = array("col1", "col2");
$aCols2Search = array("col2", "col4");
$searchterm = "football";

$oMySQL->MatchTerms($aCols2Show, $aCols2Search, $searchterm);

?>

This code is equivalent to the following query:

SELECT col1, col2, MATCH(col2, col4) AGAINST("football") AS mtch FROM bartable HAVING mtch > 1 ORDER BY mtch LIMIT 0,10;

MySQL::ShowColumns()

This function is used to return the list of columns of a specified table. This function is very useful to know the structure of a table. It is possible to give MySQL::ShowColumns( $aRows ) as a parameter to only list some rows.

<?php

$aParams = array(
    "database" => "foodb",
    "table" => "bartable"
    );

$oMySQL = new MySQL($aParams);
$oMySQL->ShowColumns();

?>

This code is equivalent to the following query:

SHOW COLUMNS FROM bartable;