For MySQL PHP provides 3 libraries, mysql (obsolete), mysqli and PDO.
-
MySQLi has been included in PHP since version 5.0 .
-
PDO has been included in PHP since version 5.1 .
As we can see both libraries take a short time and it is not that one system to the other, but that each one is designed for a different purpose .
MySQLi
- Provide procedural and object-oriented interface ( interface
dual ).
- Extensive MySQL engine functionalities support with
version 4.1.3 or higher.
- Support for Prepared Declarations.
- Support for Multiple Declarations.
- Support for Transactions.
- Only databases based on MySQL.
PDO (PHP Data Objects)
- Interface only object oriented.
- Provide a database abstraction layer.
- Support for Prepared Declarations.
- Support for Transactions.
-
Supports multiple database engines ( list ).
ORM
A thirteenth option would be to use a ORM (object-relational mapping), There are several options, for example Doctrine , Propel , Illuminate Database ( Eloquent-Laravel ), among others. These ORMs add a layer of abstacción of both the database engine and the SQL language, facilitating the interaction with databases.
Examples:
MySQLi (or improved MySQL)
Object-oriented style
<?php
// Conectarse a y seleccionar una base de datos de MySQL llamada sakila
$mysqli = new mysqli('127.0.0.1', 'tu_usuario', 'tu_contraseña', 'sakila');
// comprobar si existe algun error
if ($mysqli->connect_errno) {
echo "Error: Fallo al conectarse a MySQL debido a: \n";
echo "Errno: " . $mysqli->connect_errno . "\n";
echo "Error: " . $mysqli->connect_error . "\n";
exit;
}
// Realizar una consulta SQL
$sql = "SELECT * FROM my_table";
// Ejecutar comprobar si existe algun error
if (!$resultado = $mysqli->query($sql)) {
echo "Error: La ejecución de la consulta falló debido a: \n";
echo "Query: " . $sql . "\n";
echo "Errno: " . $mysqli->errno . "\n";
echo "Error: " . $mysqli->error . "\n";
exit;
}
if ($resultado->num_rows > 0){
echo "<table>";
while ($line = $resultado->fetch_assoc()) {
echo "<tr>";
foreach ($line as $col_value) {
echo "<td>$col_value</td>";
}
echo "</tr>";
}
}
else{
echo 'No se encontraron resultados';
exit;
}
// Liberar resultados
$resultado->free();
// Cerrar la conexión
$mysqli->close();
Prepared queries
<?php
// Conectarse a y seleccionar una base de datos de MySQL llamada sakila
$mysqli = new mysqli('127.0.0.1', 'tu_usuario', 'tu_contraseña', 'sakila');
// comprobar si existe algún error
if ($mysqli->connect_errno) {
echo "Error: Fallo al conectarse a MySQL debido a: \n";
echo "Errno: " . $mysqli->connect_errno . "\n";
echo "Error: " . $mysqli->connect_error . "\n";
exit;
}
// Creamos la consulta SQL
$sql = 'SELECT * FROM my_table WHERE id = ?';
// La preparamos
$stmt = $mysqli->prepare($sql);
// bindeamos los datos
$stmt->bind_param('i', 5 );
// Ejecutamos la consulta
$stmt->execute();
// Recuperamos los datos
$resultado = $stmt->get_result();
// comprobar si devolvio registros
if ($resultado->num_rows > 0){
// Imprimir los resultados en HTML
echo "<table>";
while ($line = $resultado->fetch_assoc()) {
echo "<tr>";
foreach ($line as $col_value) {
echo "<td>$col_value</td>";
}
echo "</tr>";
}
}
else{
echo 'No se encontraron resultados';
exit;
}
// Liberar resultados
$resultado->free();
// Cerrar la conexión
$mysqli->close();
Procedural style
<?php
// Conectarse a y seleccionar una base de datos de MySQL llamada sakila
$mysqli = mysqli_connect('127.0.0.1', 'tu_usuario', 'tu_contraseña', 'sakila');
// comprobar si existe algun error
if (mysqli_connect_errno()) {
echo "Error: Fallo al conectarse a MySQL debido a: \n";
echo "Errno: " . mysqli_connect_errno() . "\n";
echo "Error: " . mysqli_connect_error() . "\n";
exit;
}
// Creamos una consulta SQL
$sql = "SELECT * FROM my_table";
// Ejecutar comprobar si existe algun error
if (!$resultado = mysqli_query($mysqli, $sql)) {
echo "Error: La ejecución de la consulta falló debido a: \n";
echo "Query: " . $sql . "\n";
echo "Errno: " . mysqli_connect_errno() . "\n";
echo "Error: " . mysqli_connect_error() . "\n";
exit;
}
if (mysqli_num_rows($resultado) > 0){
echo "<table>";
while ($line = mysqli_fetch_assoc($resultado)) {
echo "<tr>";
foreach ($line as $col_value) {
echo "<td>$col_value</td>";
}
echo "</tr>";
}
}
else{
echo 'No se encontraron resultados';
exit;
}
// Liberar resultados
mysqli_free_result($resultado);
// Cerrar la conexión
mysqli_close($mysqli);
Mix of styles
According to the manual , it is possible to mix the styles without major problem:
It is possible to change between styles at any time. I dont know
recommends mixing the two styles for reasons of clarity and style of
code.
PDO (PHP Data Objects)
<?php
// datos de conexión
$dsn = 'mysql:host=localhost;dbname=testdb';
$nombre_usuario = 'nombre_usuario';
$contrasena = 'contraseña';
$opciones = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
// Conectarse a y seleccionar una base de datos de MySQL
$pdo = new PDO($dsn, $nombre_usuario, $contrasena, $opciones);
$sentencia = $pdo->prepare("SELECT * FROM my_table WHERE id = ?");
// bindear datos y ejecutar
if ( $sentencia->execute(array('mi_id')) ) {
// Imprimir los resultados en HTML
echo "<table>";
while ($fila = $sentencia->fetch(PDO::FETCH_ASSOC)) {
echo "<tr>";
foreach ($line as $col_value) {
echo "<td>$col_value</td>";
}
echo "</tr>";
}
echo "</tr>";
}
// Liberamos resultados
$resultado->free_result();
Note: It is indifferent the type of query that is carried out, it does not matter
contain a JOIN or not, as you indicate in your question. PHP interprets the
query as a string and it does not matter what you have written in said
string, the interpretation of the query is performed by the base engine
of data , in this example MySQL.
SQL injection:
To avoid SQL injection it would be easier to use prepared queries, you can find information on as
prevent Injection here .