Working with MySQL in PHP

PHP supports MySQL database.
Special built-in functions for working with MySQL make it easy and efficient to work with this DBMS: execute any queries, read and write data, handle errors.
The script that connects to the database, executes the query and shows the result will consist of only a few lines. You don't need to install or configure anything to work with MySQL; everything you need is already available along with the standard PHP distribution.

What is mysqli?

mysqli (MySQL Improved) is a PHP extension that adds full support for MySQL databases to the language. This extension supports many of the features of modern MySQL versions.

What does working with a database look like?

A typical process for working with a DBMS in a PHP script consists of several steps:

1. Establish a connection to the DBMS server by passing the necessary parameters: address, login, password.
2. Make sure that the connection was successful: the DBMS server is available, the login and password are correct, and so on.
3. Form a correct SQL query (for example, to read data from a table).
4. Verify that the request was completed successfully.
5. Get the result from the DBMS as an array of records.
6. Use the received records in your script (for example, show them in the form of a table).

Mysqli connect function: connect to MySQL

Before you start working with data inside MySQL, you need to open a connection to the DBMS server.
PHP does this using a standard function mysqli_connect(). The function returns the result - the connection resource. This resource is used for all of the following MySQL operations.

But in order to connect to the server, you need to know at least three parameters:

• DBMS server address;
• Login;
• Password.

If you followed the standard MySQL installation procedure or are using OpenServer, the server address will be localhost, the login is root. When using OpenServer, the password for connecting is an empty string '', and when installing MySQL yourself, you specified the password in one of the steps of the installation wizard.

Basic function syntax mysqli_connect():

mysqli_connect(<host>, <username>, <password>, <database>);

Checking the connection

The first thing to do after connecting to the DBMS is to verify that it was successful.
This check is needed to exclude an error when connecting to the database. Invalid connection parameters, misconfiguration or high load will cause MySQL to reject new connections. All of these situations will result in a connection failure, so the programmer should verify that the connection to the server is successful before performing the following steps.

The MySQL connection is established once in a script and then used for all database queries.
The result of executing the function mysqli_connect()will be a value of a special type - a resource.
If the connection to MySQL fails, the function mysqli_connect()will return a boolean value of the "false" type instead of a resource - false.
It is good practice to always check the value of the result of this function and compare it to false.

Connecting to MySQL and checking for errors:

Call this function right after successful connection:mysqli_set_charset($con, "utf8");

Executing queries

With the connection established and the encoding determined, we are ready to execute our first SQL queries. You already know how to compose correct SQL commands and execute them through the console or visual interface of the MySQL client.
The same requests can be sent unchanged from a PHP script. Several built-in language functions will help with this.

Two kinds of requests

All SQL queries should be divided into two groups:

1. Reading information (SELECT).
2. Modification (UPDATE, INSERT, DELETE).

When executing queries from the PHP environment, queries from the second group return only the result of their execution: success or error.
Queries in the first group, if successful, return a special result resource. It, in turn, can be converted to an associative array (if a single record is needed) or to a two-dimensional array (if a list of records is required).

Adding an entry

Let's go back to our project - a diary of observations of the weather. Let's start our practical work by filling the tables with data. First, let's add at least one city to the cities table.

The expression is INSERT INTOused to add new records to the database table.

Let's compose a correct SQL query to insert a record with the city name, and then execute it by passing this query to a function mysqli_query()to add new data to the table.

For requests to change data (not SELECT), the execution result will be a Boolean value - true or false.
falsewill mean that the request failed. There is a function to get a string describing the error mysqli_error($link).

Insert id function: how to get the id of the added record

The next step is to add a weather record for the new city.
Weather records are stored in the weather_log table, but to reference a city, you need to know the record ID from the cities table.
This is where the function comes in handy mysqli_insert_id().
It takes a single argument, the connection resource, and returns the ID of the last added entry.

We now have everything we need to add a weather record.
Here's what a complex example would look like, connecting to MySQL and adding two new entries:

Let's write a SQL query that will use the SELECTexpression. We will then execute this query using a function mysqli_query()to get the data from the table.

This example shows how to display all existing cities from the cities table:

It is important to understand that this variable does not contain data from the table, but a special data type - the so-called reference to the query results.

To get valid data, that is, records from the table, you should use another function mysqli_fetch_array()- and pass it this same link as the only parameter.
Now, each function call mysqli_fetch_array()will return the next record from the entire resulting recordset as an associative array.

The loop whileis used here to " loop " through all the records from the resulting recordset.
The value of the field of each record can be found simply by referring to the key of this associative array.

How to get all records at once as a two-dimensional array

Sometimes it is convenient, after a read request, not to call in a loop mysqli_fetch_arrayto retrieve the next record in order, but to get them all at once with one call. PHP can do that too. The function mysqli_fetch_all($res, MYSQLI_ASSOC)will return a two-dimensional array with all the records from the result of the last request.
Let's rewrite an example showing existing cities using it:

This can be helpful when organizing page navigation, or simply as information.
The function mysqli_num_rows()that should be passed a link to the query result will help to find out the number of records .