Perpetual Theme with PHP and MySQL

Hello! Surely every one 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 himself comfortable classes / procedures, someone found something in the open spaces of the network.

Since my PHP scripts are increasingly beginning to collapse to one 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 MySQL

Immediately make a reservation - the built-in class mqsli is quite convenient 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 - a unique number, auto increment
NAME - customer name
AGE - age of the client
AMOUNT - the amount of purchases
BLOCKED - boolean, client blocked or active
SETTINGS - 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

All good code!