Last Updated:

PHP MySQL Basics

PHP MySQL Basics

PHP supports working with the MySQL database. Built-in functions for working with the database allow you to work effectively and simply with this DBMS:

  • read and write data;
  • Execute different queries.
  • Handle errors.

A script that connects to a database involves multiple rows. To work with the database, you do not have to install anything additional. Everything you need already comes with a standard PHP setting.

MySQLI – what is it?

MySQLImproved is a special PHP extension that adds full support for the MySQL database to the language. This extension supports many features of modern versions of MySQL databases.

Work with the database. Step

The classic process of working with MySQL in PHP consists of the following steps:

  1. Establish a connection to the MySQL server by passing the following parameters: address, login and password.
  2. Checking the successful connection: the dbMS server is available, the correct login and password are entered, and so on.
  3. Formation of a correct SQL query (for example, to read data from a table).
  4. Verify that the request was successful.
  5. The next step is to get the result from the database in the form of an array of records.
  6. Apply the received records in your scenario (for example, show them as a table).

Connection to MySQL. mysqliconnect function

Before you start working with data inside the database, you need to connect to the DBMS server. The mysqli_connect() function is responsible for this process. It returns the result of the connection resource. This resource is used for all subsequent database operations.

However, to make a connection to the server, you need to know three main parameters: the address of the server to which the connection will be, login and password.

If you followed the standard instructions for installing the MySQL database or use OpenServer, then the login will be root, the address is localhost. When installing MySQL yourself, you previously created a password at one of the installation steps, and when using OpenServer, the password will be an empty string.'

Here is the basic syntax of the mysqli_connect():

mysqli_connect(<server address>, <login>, <password>, <database name>);

Test the connection

After connecting to the DBMS, you need to check whether it was successful. Do not skip this step to eliminate the possibility of an error when connecting to the database. Incorrect connection settings, high load or incorrect configuration will cause the database to reject new connections. As a result, the connection will not be possible, so you need to make sure that the connection to the server is successful before performing subsequent actions.

It is convenient that the connection to the database is established only once, after which it is constantly used in queries to MySQL. The result of the execution of the mysqli_connect() function will be the value of a special type - resource.
In the event that the connection fails, mysqli_connect() will return a Boolean value of type "false" - false.

Attention! Ideally, always check the value of the result of a given function and compare it to a lie.

Connecting to the database and checking for errors:

<?php
$link = mysqli_connect("localhost", "root", "");

if ($link == false){
print("Error: Unable to connect to MySQL" . mysqli_connect_error());
}
else {
print("Connection established successfully");
}

The mysqli_connect_error() function returns a text description of the last database error.

Set the encoding

After you are sure that the connection is successful, you need to specify the encoding that will be used during data exchange with MySQL. If you ignore this step, then instead of entries with values in Cyrillic, you run the risk of getting a sequence of question marks:

‘?????????????????’.

Immediately after a successful connection, you need to call the following function:

mysqli_set_charset($con, 'utf8');

Executing Queries

After the first two steps, you can start executing the first SQL queries. They can be executed through the visual or console interface of the MySQL client.

The same requests can be sent from the PHP script without modification. A few built-in PHP functions will help with this.

Types of SQL Queries

 

It is worth allocating 2 groups of SQL-queries:

  1. (SELECT) – Read information.
  2. (UPDATE, INSERT, DELETE) – Модификация.

When executing requests from the PHP environment, queries from the second group return only the result of their execution: error or success.

Queries in the first group return a special result resource when successful. In turn, it can be converted: if one record is required, then into an associative array, and if you need a list of records, then into a two-dimensional one.

Add a record

Consider the example of a diary of weather observations. Let's start filling the tables with data. First of all, you need to add at least one city to the cities table.

To add new records to the database table, use the INSERT INTO statement.

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

<?php
$link = mysqli_connect("localhost", "root", "");

$sql = 'INSERT INTO cities SET name = "St. Petersburg"';
$result = mysqli_query($link, $sql);

if ($result == false) {
print(An error occurred while executing the query));
}

Importantly! The first parameter for mysqli_query() passes the connection resource from mysqli_connect(), and the second parameter is a string with an SQL query.

For data change requests, the result of the execution will be a Boolean value of fals or true.
To get a string with an error, the mysqli_error($link) function responds.

Get the ID of the added record. Insert id function

The next step is to add a weather record for another city. Weather records are stored in the weather_log table. However, to refer to a city, you need to know the ID of the record from the cities table. The mysqli_insert_id() function comes to the rescue, which takes a single argument, the connection resource, while returning the ID of the record that was added last.

You can now add a weather record. Notice what the example will look like with connecting to Bdi by adding 2 new entries:

<?php
$link = mysqli_connect("localhost", "root", "");

if ($link == false){
print("Error: Unable to connect to MySQL" . mysqli_connect_error());
}
else {
$sql = 'INSERT INTO cities SET name = "St. Petersburg"';
$result = mysqli_query($link, $sql);

if ($result == false) {
print("An error occurred while executing the request");
}
else {
$city_id = mysqli_insert_id($link);

$sql = 'INSERT INTO weather_log SET city_id = ' .$city_id .', day = "2017-09-03", temperature = 10, cloud = 1';

$result = mysqli_query($link, $sql);

if ($result == false) {
print(An error occurred while executing the query));
}
}
}

Reading records

Another common operation in working with a database in PHP is to retrieve records from tables, namely QUERIES of the SELECT type.

First of all, you need to create a SQL query that would use the SELECT expression. Next, we will execute this request using the mysqli_query() function. This will get the data from the table.

An example of how to display all existing cities from the table:

<?php

$sql = 'SELECT id, name FROM cities';

$result = mysqli_query($link, $sql);

while ($row = mysqli_fetch_array($result)) {
print("City: " . $row['name'] ."; ID: . " . $row['id'] . "<br>");
}

In the example above, the output of the mysqli_query() function is stored in a variable $result.

Attention! This variable does not contain data taken from the table, but a special data type – a reference to the query result.

To obtain valid data (records from the table), you need to resort to another function mysqli_fetch_array(). You need to pass this link to her with a single parameter.

Now each subsequent call to mysqli_fetch_array() will return the next record from the entire set as an associative array.

The while loop is used to "traverse" all records from the full set. The field values of each record can be found by accessing the key of this associative array.

Method of retrieving all records as a two-dimensional array

For this purpose, you need to use the mysqli_fetch_all function ($res, MYSQLI_ASSOC), which will return a two-dimensional array with all the records from the last request.

Example:

<?php

$sql = 'SELECT id, name FROM cities';
$result = mysqli_query($link, $sql);

$rows = mysqli_fetch_all($result, MYSQLI_ASSOC)

foreach ($rowsas $row) {
print("City: " . $row['name'] ."; ID: . " . $row['id'] . "<br>");
}

How to find out the number of records

You may want to count how many records the SQL query will return. This will help when organizing page-by-page navigation or as information.

The number of records will help you find out the mysqli_num_rows() function, which should be passed a link to the query result.