PHP supports MySQL databases. Built-in functions specifically designed to work with MySQL, allowing you to effectively write and read data, make the necessary queries, fix bugs. A database connection script that makes a query and outputs the result will consist of a few lines. To start the database (MySQL) does not require additional installation and configuration, everything is already encapsulated and available in php itself.
But to work with the full functionality still need to use the php extension mysqli (MySQL Improved) or PDO (PHP Data Objects), which complement the language full support database (database). They support all the features of current versions of MySQL.
For example, we use the extension mysqli.
The process of working with the database
The process of working with the database in the php script consist of several steps:
- Establish a connection to the database server by transferring the required parameters: address, login and password.
- Check successful connection: access to the database, correct password and login, etc.
- Make a valid SQL query (such as reading or writing data to a table).
- Make sure the query succeeds.
- Extract the result from the database in the form of an array of records.
- Apply the obtained data to your script.
Let’s analyze them one by one.
Creating a connection to MySQL
To work with records in MySQL, you must first create a connection to the server through php. This is done with the function mysqli_connect(), which gives the result as a connection resource. This resource will be subsequently used in all subsequent operations in the database (MySQL).
To make a connection to the server, you must have three basic parameters: the address of the database server itself, a login and a password.
With a standard installation or using OpenServer: the server address is localhost, login – root. But the password for the standard installation is set in the wizard itself (at a certain step), with OpenServer password is an empty string.
Type of function in php code line:
<!--php-->
mysqli_connect(<database server address>, <login>, <password>, <database name>);
Checking Database Connection
After connecting to the database it is necessary to check its success. The necessity of checking is to exclude a connection error to the database. Incorrect settings with high load or incorrect connection parameters will not allow MySQL to establish a new connection. This in turn will cause connection failure. Therefore, webmaster should check the connection to the server to be able to work with the database in the future.
A MySQL connection is established only once in a script, and then it is applied to each database query.
The mysqli_connect() function will print the value – resource. If the connection to MySQL fails, function mysqli_connect() will return a logical value of false. Whenever a new database is created, it is a good idea to check if this function is executed and compared to false.
An example of code to connect and check for errors with MySQL in the php environment:
<!--php-->
<?php
$link = mysqli_connect("localhost", "root", "password");
if ($link == false){
echo "Error: connection to MySQL not established" . mysqli_connect_error();
}
else {
echo "Connection successful";
}
The mysqli_connect_error() function has a description of the last MySQL error.
Setting the correct encoding
After the connection is established, you must first specify the encoding to be used to exchange data with MySQL. Otherwise, instead of the records (in Cyrillic) you will get a response from: ‘?????????’.
To set the encoding, call the function mysqli_set_charset($con, "utf8");
SQL queries
With the connection and encoding set up, you can already execute the necessary SQL queries. They can be made from the visual (console) interface of the MySQL client, and from a php script. Certain built-in language functions can handle it.
SQL queries can be divided into two main groups:
- changing information (INSERT, UPDATE, DELETE);
- reading (SELECT).
When queries to read information is executed from the environment of php, then the special resource returns the result (if successful). It, depending on the need, can be converted into a two-dimensional (two or more records) or associative (one record) array. And at modification (change) it returns the result of execution – error or success.
Adding a record to the table
To add new records to the table INSERT INTO operator is used.
You need to compose a valid SQL query for inserting the record and execute it by passing the function mysqli_query(), which will add the data to the table.
<!--php-->
<?php
$link = mysqli_connect("localhost", "root", "password");
$sql = 'INSERT INTO yourtable1 SET name = "Value 1"';
$result = mysqli_query($link, $sql);
if ($result == false) {
echo "Query execution error";
}
As you can see from the example, first the mysqli_query() function specifies a connection resource (mysqli_connect()) and then the query string ($sql) is passed.
When data change requests come in, the result will be a logical value (false or true). If the result is false, the query is not executed. Outputs an error with description is handled by function mysqli_error($link).
Record ID
Using records from one table in another allows you to create arrays of data with as many records as you need.
If you have part of the records stored in one table (yourtable2) and another part stored in a second table (yourtable1). In order to refer between the records, you need to specify identifiers in them. The function to determine the id is mysqli_insert_id(). It works with the argument – the connection resource, returning the identifier of the last added records.
To add a record consisting of two separate records in different tables, you need to write the following php code:
<!--php-->
<?php
$link = mysqli_connect("localhost", "root", "password");
if ($link == false){
echo "Error: no connection to MySQL established" . mysqli_connect_error();
}
else {
$sql = 'INSERT INTO yourtable1 SET name = "Time"';
$result = mysqli_query($link, $sql);
if ($result == false) {
echo "Query error";
}
else {
$yourtable1_id = mysqli_insert_id($link);
$sql = 'INSERT INTO yourtable2 SET yourtable1_id = ' . $yt1value_id . ', day = "2021-10-25", rise = 7:37, set = 17:46';
$result = mysqli_query($link, $sql);
if ($result == false) {
echo "Query error";
}
}
}
Reading MySQL records
After writing to the database, there is another, no less important operation – reading these records from tables. An SQL query to get data from a table that uses SELECT is also performed using the mysqli_query() function.
An example of the output of all existing records in yourtable1:
<!--php-->
<?php
$sql = 'SELECT id, name FROM yourtable1';
$result = mysqli_query($link, $sql);
while ($row = mysqli_fetch_array($result)) {
echo("Day:" . $row['name'] . "; ID: ." . $row['id'] . "<br>");
}
The result of running mysqli_query() from the example is stored in the $result variable. Please note that this variable stores a link to the query result, not the data.
The function mysqli_fetch_array() is responsible for retrieving the data (records) itself. Once specified, calling this function will return the subsequent record as an associative array.
Using the while loop is necessary to “comb through” all the records in the array. Now, to find out the value of a field for each individual record, you can use the key of the associative array.
Getting the records in two-dimensional array
In some tasks, when you call mysqli_fetch_array function, it is more convenient not to display the following records in order, but all at once. In php, to do this, use function mysqli_fetch_all($res, MYSQLI_ASSOC), which returns a two-dimensional array of the query results with all records.
A code view showing all dates present using this function:
<!--php-->
<?php
$sql = 'SELECT id, name FROM yourtable1';
$result = mysqli_query($link, $sql);
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC)
foreach ($rows as $row) {
echo"Day:" . $row['name'] . "; ID: ." . $row['id'] . "<br>";
}
Find out the total number of records
An important piece of information may be the number of all records that the executed SQL query will produce. It can be used, for example, when building page navigation, or in general, to understand the amount of data. The function that displays the number of records is mysqli_num_rows(). It needs reference of query result.
Conclusion
Adding entries to MySQL tables is a skill that can be used by both novice programmers and developers in training. This article has covered the basic ways of working with records via php using MySQLi, with examples.