Blog

3 correct methods of connecting to MySQL database with php

When we embark on web projects that are made up of dynamic pages, it is inevitable to read and write data, store it and achieve persistence, and this is generally done in a database.

The connection to the database from PHP is simple as well as mechanical, it simply requires understanding the parts of the connection and how to use them. In the following lines I am going to focus on the connection to MySQL, the connection to other databases being similar, even almost identical.

Content

  • Ways to connect to the DBMS.
  • Connecting to MySQL via PDO object
  • Database connection via Mysqli object
  • Database connection with mysql functions

Ways to connect to the DBMS.

There are different ways to connect to the MySQL database (lately MariaDB) and this is mostly due to the widespread use of this DBMS (Database Management System). Its versatility, ease of use, being free and performing well for small and medium databases have made it number one.

A database management system (DBMS) is a set of programs that allow the storage, modification and extraction of information in a database, as well as providing tools to add, delete, modify and analyze the data. Users can access the information using specific interrogation and reporting tools, or through applications for that purpose.

We have several ways to connect to the database and I am going to explain two: by objects and by functions.

Connecting to MySQL via PDO object

For this first connection I am going to explain my favorite, the connection through the PDO object. The PDO object provides an abstraction layer of Data access, which means that no matter what database is being used, the same functions are used to query and retrieve data. In Christian, so that we understand each other, it means that this same object is used to connect to both MySQL and PostgreSQL or other DBMS.

To connect to MySQL and use a database called Test with the root user and root password we will do the following in PHP:

$objetoPDO = new PDO('mysql:host=localhost;dbname=Test', 'root', 'root');

This line will create a PDO class object which will manipulate the connection to the database. Keep in mind that if the connection data is wrong, an error will occur and our code will stop working. To avoid this we will use the catch of exceptions with try and catch:

try{
     $objetoPDO = new PDO('mysql:host=localhost;dbname=Test', 'root', 'root');
}catch (PDOException $e) {
      echo "¡Error!: " . $e->getMessage();
      die();
}

We already have our precious connection to the database, now we can easily send the SQL queries as if we were writing them directly in our database client. For example, for a SELECT from a “user” table:

$query = "SELECT * FROM usuario;";
$sth = $objetoPDO->prepare($query);
$resultado = $sth->execute();

In $result we will have the result of the query in the database and in case it does not return “false” we can retrieve all rows/tuples with:

if($resultado != false){
      $array_filas = $resultado->fetchAll();
}else{
      echo "La consulta ha producido un error, revisala";
}

Now we have the result of the query, that is, the rows returned by the select in $array_rows. To go through the values ​​will be like going through any other array, either through a foreach loop, or another loop. To finish, continuing with the previous example of the User table, a solution to show the information of the columns name, surname, ID, ID be:

foreach( $array_filas as $fila_usuario){
     echo 'Usuario con nombre y apellidos  ' . $fila_usuario['nombre'];
     echo ' ' . $fila_usuario['apellidos'] . ' DNI: ' . $fila_usuario['dni'];
     echo ' y id: ' . $fila_usuario['id'];
}

Database connection via Mysqli object

I like the second option, and also with objects, in this case of the class mysqli. As this class is aimed solely and exclusively at connecting to MySQL (or MariaDB) databases, its use is simple but “limited” regarding PDO, hence my second choice.

Connect as in the previous case to a database Test with user root and password root I would be:

$objeto_mysqli = new mysqli('localhost', 'root', 'root', 'test');

Once the connection has been initialized by creating the object, if we want to make a query we can do it as follows:

$query = "SELECT nombre, apellidos, dni, id FROM usuario";
$resultado = $objeto_mysqli->query($query);

$result in case of success of the query will be an object of the class mysqli_result which will process the data from the query result, otherwise, that is, if there was an error executing the query, the value of $result be false. 

if($resultado != false){
     //hacemos lo que queramos con el resultado de la consulta si procede
}else{
    echo 'La consulta ha producido un error';
}

Once the possible error has been dealt with, we can extract the information from the rows that the object contains. $result. There are a few methods available in the class mysqli_result for row extraction. First we must go through the result rows, for this we can help ourselves using the attribute public num_rows. but I am going to comment on the two that I like the most.

Example with fetch_assoc:

for($i=0;$i < $resultado->num_rows; $i++){
     $fila_usuario = $resultado->fetch_assoc();
     echo 'Usuario con nombre y apellidos  ' . $fila_usuario['nombre'];
     echo ' ' . $fila_usuario['apellidos'] . ' DNI: ' . $fila_usuario['dni'];
     echo ' y id: ' . $fila_usuario['id'];
}

Example with fetch_row:

for($i=0;$i < $resultado->num_rows; $i++){
     $fila_usuario = $resultado->fetch_row();
     echo 'Usuario con nombre y apellidos  ' . $fila_usuario[0]; // 0 => primera columna del select
     echo ' ' . $fila_usuario[1] . ' DNI: ' . $fila_usuario[2];
     echo ' y id: ' . $fila_usuario[3]; // 3 => último columna del select
}

Finally, in the case of this Mysqli class you have to remember always close the connection to the database. Whenever our code reads or writes to the database finishes, we will have to call the method close().

$objeto_mysqli->close();

Database connection with mysql functions

Using functions you can do the same as in the previous cases, the end result is similar, but my predilection for the use of objects and above all the versatility of PDO makes this my last option.

Applying the same previous example (database localuser rootpassword root and database test) for connection we will perform:

$conexion = mysqli_connect('localhost', 'root', 'root');
mysqli_select_db('test', $conexion); //selecciona la base de datos

Once connected we will perform our query on a table user with columns name, surname, ID, ID:

$query = "SELECT nombre, apellidos, dni, id FROM usuario";
$resultado = mysqli_query($query, $conexion);

In this case the result of the query will be a false if the query produced an error or a resource pointing to the result rows. To be able to read this information and also see a possible error in the query:

if($resultado == false){
    echo 'ERROR! CODIGO: ' . mysqli_errno($conexion) . ' mensaje:'  . mysqli_error($conexion);
}else{
    $array_filas = mysql_fetch_assoc($resultado);
    foreach($array_filas as $fila_usuario){
     echo 'Usuario con nombre y apellidos  ' . $fila_usuario['nombre'];
     echo ' ' . $fila_usuario['apellidos'] . ' DNI: ' . $fila_usuario['dni'];
     echo ' y id: ' . $fila_usuario['id'];
   }
}

And are we done? Well almost, we cannot forget to close the connection to the database:

mysqli_close($conexion);

Leave a Reply