14. Databases

If you don't know SQL or need a refresher, I recommend you go through my SQL course before continuing with this lesson.

In PHP we have different drivers (or connectors) to interact with a database. The most popular and secure one is PDO (PHP Data Objects). It's simple to use and saves us from security problems.

We'll query a table in MySQL.

// Variables
$hostDB = '127.0.0.1';
$nombreDB = 'ejemplo';
$usuarioDB = 'root';
$contrasenyaDB = '123';
// Connect to the database
$hostPDO = "mysql:host=$hostDB;dbname=$nombreDB;";
$miPDO = new PDO($hostPDO, $usuarioDB, $contrasenyaDB);
// Prepare the SELECT
$miConsulta = $miPDO->prepare('SELECT * FROM Escuelas;');
// Run the query
$miConsulta->execute();
// Print
$resultados = $miConsulta->fetchAll();
foreach ($resultados as $posicion => $columna) {
    echo $columna['nombre'];
}

Or using SQLite.

// Variables
$hostDB = 'mibasededatos.sqlite';
// Connect to the database
$hostPDO = "sqlite:$hostDB";
$miPDO = new PDO($hostPDO);

// Prepare the SELECT
$miConsulta = $miPDO->prepare('SELECT * FROM Escuelas;');
// Run
$miConsulta->execute();

// Back to the table
// Print
$resultados = $miConsulta->fetchAll();
foreach ($resultados as $posicion => $columna) {
    echo $columna['nombre'];
}

It will return an array for each row, duplicating the data so we can access it by position or by column name.

Array
(
    [id] => 1
    [0] => 1
    [school] => Oxford
    [1] => Oxford
    [my_population] => 12345
    [2] => 12345
    [created_at] => 2018-07-31 11:04:04
    [3] => 2018-07-31 11:04:04
)
Array
(
    [id] => 2
    [0] => 2
    [school] => London
    [1] => London
    [my_population] => 76543
    [2] => 76543
    [created_at] => 2018-08-31 10:04:04
    [3] => 2018-08-31 10:04:04
)
...

To use SQLite with PHP you'll need the driver installed. In many cases it's already included in development environments like XAMPP or MAMP. Otherwise, as with the official PHP client we're using in this course, you'll have to include it. On Debian and derivatives (like Ubuntu) you should run: sudo apt install php-sqlite3.

Let's see how we would do an INSERT into the alumnos table.

// Variables
$hostDB = '127.0.0.1';
$nombreDB = 'ejemplo';
$usuarioDB = 'root';
$contrasenyaDB = '123';
// Connect to the database
$hostPDO = "mysql:host=$hostDB;dbname=$nombreDB;";
$miPDO = new PDO($hostPDO, $usuarioDB, $contrasenyaDB);
// Prepare the INSERT
$miInsert = $miPDO->prepare('INSERT INTO alumnos (nombre, email, codigo_postal) VALUES (:nombre, :email, :codigo_postal)');
// Run the INSERT with the data
$miInsert->execute(
    array(
        'nombre' => 'beethoven',
        'email' => 'beethoven@cuatroestaciones.com',
        'codigo_postal' => '1234'
    )
);

The data is passed when running the statement (execute), replacing the keys with the elements that have a colon in front. For example, we have :nombre, which will be replaced by beethoven.

Never even think about substituting the data inside the SQL statement, you could suffer an SQL injection (a very common attack). To avoid it, always use the execute array as an intermediary. Never trust the user!

If we wanted to perform other tasks, like UPDATE or DELETE, we would mimic what we did with the INSERT.