Perpetual Theme with PHP and MySQL
Hello! Surely each of those who constantly and a lot write in PHP faced with the question of optimizing and simplifying queries to MySQL databases . Someone has already written to himself convenient classes / procedures, someone has found something in the open spaces of the network.
Since my PHP scripts are increasingly beginning to collapse to a single task - sampling from the database and transferring this data to client-side Java scripts, I relieved myself of the fact that I created a convenient (for me, of course) database class the MySQL .
Immediately make a reservation - the built-in class mqsliquite comfortable and functional, but when faced with the same questions day after day it would be strange not to alleviate one’s fate a little.
Your attention is invited to the class exDBase , it is essentially a wrapper for the class mysqli . Immediately make a reservation - I am a beginner programmer, and am ready to receive a lot of criticism for the written code in comments or personal messages. I don't really own RegExp for example, which would greatly simplify the code, maybe there are other complaints. But nonetheless…
The entire library is contained in a single file - exdbase.php . This file contains the exDBase class description . To get started, you just need to download the file, register this line:
First, let's create an instance of the class, it's very simple:
In the $ DB-> error property there will always be an error of the last operation (if there was an error), or it will be empty.
The format is almost identical to creating an instance of the mysqli class . Naturally, instead of the constants specified in the example, you need to substitute the real values for the host, user name, password, and database name.
Now you can exercise. Suppose we have a ready-made database, and it has a clients table with fields:
ID — unique number, autoincrement
NAME — client name
AGE — client age
AMOUNT — purchase amount
BLOCKED — boolean variable, client blocked or SETTINGS active
- personal parameters, we store them in JSON format
Let's get all the records from the ' clients ' table . To do this, there is a method fetchArray .
And if we want to receive only the first record of the request? For this there is a method fetchFirst .
But, we hardly need to get all the records from the table, there are always selection conditions (the WHERE field in the SELECT command). What should we do? Yes, very simple. This is the second argument to the fetchArray or fetchFirst methods.
Suppose we want to select all clients named John. In our class this can be done in two ways.
The first is to simply set the condition in a string like "NAME = 'John'"
The second is to set the condition with an array:
And if there are more conditions? For example, should age be 30 years old? Easy:
This way you can combine multiple search terms. But equality ... And if we want to find all customers with the name John, who are over 25 years old? Here come the special prefixes to help:
In addition to "> =" you can use: ">", "<", "<=", "! =", "<>", "! =". Thus, you can create queries of different degrees of complexity and always receive the necessary answers.
The third parameter of the database selection methods is the table fields. They can be specified both as a string (for example: "NAME, AGE") and as an array: array ("NAME", "AGE").
The fourth and last parameter of fetchArray and fetchFirst fetch methods is the sort order. It is also specified either as a string (such as: “ID ASC, NAME DESC”) or as an array (“ID” => “ASC”, “NAME” => “DESC”).
Well, ending with the acquisition of data, you ask - and how to limit the sample? For example, need only the first 10 entries?
This is done by the setLimit () method, like this:
The setLimit () method works only for one request, after that the limits are reset.
There is an insert () method for writing new data .
It returns the value of the primary key autoincrement (if such is specified in the table). In our case, it will return the ID of the inserted record.
The data is updated using the update () method .
We updated all the records where the name (the NAME field) is 'Peter'. The second argument to the method is a selection condition, exactly in the same format as WHERE for SELECT. Well, the third argument of the method is the data itself. Now all such records with the name 'Peter' will have an age of 30, and the amount will be 2000.25.
If you already understand the logic of the library, the removal will be very easy. The method is called delete () .
Here is such a first brief excursion into the exDBase library. There are still a number of other, more advanced functions, but more on that another time.
UPD: You can download the file on GitHub
Good code to all!
Since my PHP scripts are increasingly beginning to collapse to a single task - sampling from the database and transferring this data to client-side Java scripts, I relieved myself of the fact that I created a convenient (for me, of course) database class the MySQL .
Immediately make a reservation - the built-in class mqsliquite comfortable and functional, but when faced with the same questions day after day it would be strange not to alleviate one’s fate a little.
Your attention is invited to the class exDBase , it is essentially a wrapper for the class mysqli . Immediately make a reservation - I am a beginner programmer, and am ready to receive a lot of criticism for the written code in comments or personal messages. I don't really own RegExp for example, which would greatly simplify the code, maybe there are other complaints. But nonetheless…
The entire library is contained in a single file - exdbase.php . This file contains the exDBase class description . To get started, you just need to download the file, register this line:
require_once ('exdbase.php');First, let's create an instance of the class, it's very simple:
$DB = new exDBase (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($DB->error)
echo"Ошибка соединения: $DB->error";In the $ DB-> error property there will always be an error of the last operation (if there was an error), or it will be empty.
The format is almost identical to creating an instance of the mysqli class . Naturally, instead of the constants specified in the example, you need to substitute the real values for the host, user name, password, and database name.
Now you can exercise. Suppose we have a ready-made database, and it has a clients table with fields:
ID — unique number, autoincrement
NAME — client name
AGE — client age
AMOUNT — purchase amount
BLOCKED — boolean variable, client blocked or SETTINGS active
- personal parameters, we store them in JSON format
Database request
Let's get all the records from the ' clients ' table . To do this, there is a method fetchArray .
$res = $DB->fetchArray ('clients'); // получаем все записи в виде массива ассоциативных массивовif ($res)
foreach ($res as $client)
echo print_r ($client, true); // выдаем все записи на экранAnd if we want to receive only the first record of the request? For this there is a method fetchFirst .
$res = $DB->fetchFirst ('clients'); // получаем первую запись в виде ассоциативного массиваif ($res)
echo print_r ($client, true); // выдаем первую запись на экранBut, we hardly need to get all the records from the table, there are always selection conditions (the WHERE field in the SELECT command). What should we do? Yes, very simple. This is the second argument to the fetchArray or fetchFirst methods.
Suppose we want to select all clients named John. In our class this can be done in two ways.
The first is to simply set the condition in a string like "NAME = 'John'"
$res = $DB->fetchArray ('clients', "NAME = 'John'");The second is to set the condition with an array:
$res = $DB->fetchArray ('clients', array ("NAME" => "John"));And if there are more conditions? For example, should age be 30 years old? Easy:
$res = $DB->fetchArray ('clients', array ("NAME" = "John", "AGE" => 30));This way you can combine multiple search terms. But equality ... And if we want to find all customers with the name John, who are over 25 years old? Here come the special prefixes to help:
$res = $DB->fetchArray ('clients', array ("NAME" = "John", ">=AGE" => 25));In addition to "> =" you can use: ">", "<", "<=", "! =", "<>", "! =". Thus, you can create queries of different degrees of complexity and always receive the necessary answers.
The third parameter of the database selection methods is the table fields. They can be specified both as a string (for example: "NAME, AGE") and as an array: array ("NAME", "AGE").
$res = $DB->fetchArray ('clients', array ("NAME" = "John", ">=AGE" => 25), array ("NAME", "AGE"));The fourth and last parameter of fetchArray and fetchFirst fetch methods is the sort order. It is also specified either as a string (such as: “ID ASC, NAME DESC”) or as an array (“ID” => “ASC”, “NAME” => “DESC”).
$res = $DB->fetchArray ('clients', array ("NAME" = "John", ">=AGE" => 25), array ("NAME", "AGE"), array ("ID" => "ASC", "NAME" => "DESC"));Well, ending with the acquisition of data, you ask - and how to limit the sample? For example, need only the first 10 entries?
This is done by the setLimit () method, like this:
$DB->setLimit (10);
$res = $DB->fetchArray ('clients', "NAME = 'John'");The setLimit () method works only for one request, after that the limits are reset.
Insert new data
There is an insert () method for writing new data .
$id = $DB->insert ('clients', array ("NAME" => 'Peter', "AGE" => 27, "AMOUNT" => 1000.25));It returns the value of the primary key autoincrement (if such is specified in the table). In our case, it will return the ID of the inserted record.
Data update
The data is updated using the update () method .
$DB->update ('clients', array ("NAME" => 'Peter'), array ("AGE" => 30, "AMOUNT" => 2000.25));We updated all the records where the name (the NAME field) is 'Peter'. The second argument to the method is a selection condition, exactly in the same format as WHERE for SELECT. Well, the third argument of the method is the data itself. Now all such records with the name 'Peter' will have an age of 30, and the amount will be 2000.25.
Data deletion
If you already understand the logic of the library, the removal will be very easy. The method is called delete () .
$DB->delete ('clients', array ("NAME" => 'Peter'); // удалить все записи с именем 'Peter'
$DB->delete ('clients', array (">AGE" => '20'); // удалить все записи с возрастом больше 20.Here is such a first brief excursion into the exDBase library. There are still a number of other, more advanced functions, but more on that another time.
UPD: You can download the file on GitHub
Good code to all!