Working with MySQL in PHP
connection with mysql using php, php mysql extension , php and mysql notes
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:
- Establish a connection to the DBMS server by passing the necessary parameters: address, login, password.
- Make sure that the connection was successful: the DBMS server is available, the login and password are correct, and so on.
- Form a correct SQL query (for example, to read data from a table).
- Verify that the request was completed successfully.
- Get the result from the DBMS as an array of records.
- 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;
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(<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 -
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:
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:
- Reading information (SELECT).
- 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.
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
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:
This example shows how to display all existing cities from the cities table:
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.
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: