MySQL database management system and the server-side programming language PHP are used everywhere, providing convenient tools for processing data. Outwardly, significant differences in the logic of processing and storing information actually complement each other's capabilities. It makes sense to consider options for connecting PHP to MySQL and choose the most suitable for solving a specific problem.
General logic for working with MySQL
MySQL has its own dialect of the query language, which is available in PHP through a set of functions. Connecting to MySQL via PHP can be performed with several simple functions, if there is no need to build a complex query system or to use the special features of a database management system.
To get started, you must have the following parameters:
- host
- Database;
- Username;
- user password
Correctly specifying the text values of these four parameters is enough to connect and successfully work with the database. MySQL is simple, easy to use and always works flawlessly. If you encounter any problems, you need to check the configuration files and * .ini files of MySQL, PHP and Apache (or another server used). There may be other reasons, but this will be an exception to the rule.
Work begins with establishing a connection. If successful, you can perform operations with the database. Usually a procedural method is used by means of mysqli_query () and related functions, but the use of an object-oriented style, work through PDO or other options is allowed.
Matching Versions: Reliable Performance
The versions used by MySQL and PHP are essential. It is believed that the mysql_ * function interface is deprecated and only mysqli_ * should be used. This is a general rule. In some situations, you may encounter outdated installations that cannot be removed.
In any case, before you start planning to work with a specific web resource, you need to clarify the hosting data. Much in how to organize the connection of the MySQL database in PHP will depend on the conditions provided on the hosting.
Using the phpinfo () function, you can determine all of the PHP installation data and determine which options are provided for making connections.
MySQL connection example
The ideal option is to work with the database through its own interface. It is necessary to use the entire assortment of mysqli_ * functions, primarily mysqli_query (), when it is not possible to formalize the quickly needed database functionality or it is not advisable to do this.
In the proposed example, it is enough to be able to read, write, modify and delete. Therefore, the use of mysqli_query () and related functions is made in a separate object. Nothing extra. It turns out reliable, simple and affordable for quick changes.
Actually, the connection is performed in the very first line (1) by the mysqli_connect () function, which receives four parameters:
- host name
- Username;
- user password
- database name.
Usually this data is determined by the hoster, but in any case, it is fixed and valid for the entire resource. Several databases or several different users with different scope of rights can be provided - for reading, writing or other operations.
Point 2 is rarely required, but you should not forget about the encoding, especially if something went wrong during the connection. The first thing that is questioned is the encoding of the base and the encoding of the page from which the connection is established.
Point 3 is rarely used, but in vain. Having a test option in your code is always a good rule. In this case, the test code inserts that control the output of debugging information to the protocol file are placed across all the functions of the interface.
Item 4 can also be used. In this case, during development, you can enable / disable a function that cleans the database tables to verify the correct operation of the created functionality - from scratch.
Creating or using a database
Point 5 is the creation of a database. This web resource uses the concept of a self-developing site. Even if there is no database, he creates and uses it. Not all tasks require such a solution.
The main use of PHP in connecting to MySQL is working with data. In fact, the base exists, and it is quite voluminous or located on a shared resource, and specific functionality needs to be added.
Point 5 is the beginning of using the database as required by the task at hand.
This example creates a universal object for working with a database, which has only four functions:
- iLineSel ($ cWho, $ cFrom, $ cWhere)
- iLineIns ($ cTo, $ cNames, $ cValues)
- iLineDel ($ cFrom, $ cWhere)
- iLineUpd ($ cTo, $ cSet, $ cWhere)
These are fetching data, inserting rows, deleting rows, and changing rows accordingly. Function parameters correspond to the syntax of SQL statements: select, insert, delete and update.
Using the database through these functions is greatly simplified, since they provide an interface to a specific task and do not require writing its own codes in the course of its solution using mysqli_query and related PHP functions.
Essentially, MySQL connecting to PHP is an area of base-language relations, and a set of functions is a relationship of language and specific code (application).
Connection example via interface
The development of the interface, rather than the use of the PHP / MySQL functionality, directly compares the generated code from conventional coding. For example, one of the four functions indicated above, iLineSel can be executed in this way:
- The selection list, table name, and selection condition are passed to the function.
- Inside the function, an SQL query is generated and a direct connection to the MySQL database is performed.
- PHP provides the ability to describe a set of native functions or create an independent object.
- Outside of these functions (this object) is code that receives the necessary connections in a convenient way.
Encoding and Versions
Of course, PHP5 and PHP7 connections to MySQL do not differ in logic, but may differ in syntax. The concept of performance is also determined by specific performance, not specific versions. Seven is better than five, as expected, but the code is more important.
When connecting to a MySQL database in PHP, the most important thing is to pay attention to the quality of the produced code and the encoding used. It is better to write in PHP 7.2.4 and use MySQL 5.7.21 - they work fine in pairs, but it is preferable to check the available versions from the host and write high-quality code.