DB Programming- using SQL from PHP program

Catégorie	Cours
Ordre d'apprentissage	18
Statut	préparé

Query the Marathon DB with PHP

PDO

PHP class (function) used to connect and query to a database on PHP programmation.

💡

Available from PHP 5.1 version

Connect to Database

app_test_DB/
 ├── add_runner.php
 ├── query_inscription.php
 ├── query_runner.php
 └── classes
       ├── query_marathon.php
 └── config
       ├── database_marathon.php

database_marathon.php

PDO is a class

new : create un new instance of the class

setAttribute() is a method of the object PDO that add behavior attributes to the DB connection.

try capture the errors, PDOException is a specific exception for PDO

<?php
// Databse info
$host = 'localhost';
$dbname = 'bddlivre';
$user = 'root';
$pass = 'root';
try {
    // Connect to the databse
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {
    $error_message = "Connection error: " . $e->getMessage();
}

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

To manage errors. try command displays errors.

-> call a method or properties.

Query database

The page where you give the SQL query and display the result of this query

query_runner.php : query the list of runners

<?php
// Call the PDO connexion
require_once __DIR__ . '/config/database_marathon.php';
// Call the query in DB class
require_once __DIR__ . '/classes/query_marathon.php';
?>

<!DOCTYPE html>
<html lang="fr">
<head>
    <meta charset="UTF-8">
    <title>Marathon</title>
</head>
<body>
    <h1>Runners</h1>

    <!-- If error arrives in /config/database_marathon.php -->
    <?php if ($error_message): ?>
        <p style="color: red;"><?= htmlspecialchars($error_message); ?></p>
    <?php endif; ?>
    
    <ul>
        <?php

        // send to query_marathon() the result of the connection to DB
        $query = new query_marathon($pdo);
        
        // SQL query to get the runners
        $sql = "SELECT CO_NOM, CO_PRENOM FROM COUREUR ORDER BY CO_NOM ASC";
        $query_results = $query->fetchAll($sql);

        // Display the result
        foreach ($query_results as $query_result) {
            echo "<li>" . htmlspecialchars($query_result['CO_NOM']) . " " . htmlspecialchars($query_result['CO_PRENOM']) . "</li>";
        }

        ?>
    </ul>
</body>
</html>

htmlspecialchars

htmlspecialchars() is a security function in PHP
It is used to protect the website against XSS (Cross-Site Scripting) attacks.

💥 Problem without htmlspecialchars():

If you use the input or a form, a malicious user could insert code into it.

✅ What the htmlspecialchars() function does:

It converts special HTML characters into HTML entities, so the code is not interpreted but simply displayed as plain text.

➡ Result: the malicious code is not executed — it is displayed as raw text, which protects your site.

📌 When to use it?

Anytime you display text on an HTML page coming from:

a user-submitted form

a database

any external or uncontrolled source

The class that perform the query to the DB

This class is reusable. All the application will use this class to perform queries on DBs

classes/query_marathon.php

<?php

class query_marathon {
    private $pdo;

    // PHP fonction : __construct() objet constructor
    // construct receive the pdo created in /config/query_marathon
    // usefull if you have multiple DB
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    // This founction is usefull for the fetchAll and execute, above, that means prepare and execute the query
    public function query($sql, $params = []) {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    // put all rows in a table
    public function fetchAll($sql, $params = []) {
        return $this->query($sql, $params)->fetchAll(PDO::FETCH_ASSOC);
    }

    // execute for insert, update, delete
    public function execute($sql, $params = []) {
        return $this->query($sql, $params);
    }
}

Another example of select query.

query_inscription.php : query the list of inscription

<?php
// Call the PDO connexion
// require_once : include the file
// __DIR__ it is the path where the curant file is located
require_once __DIR__ . '/config/database_marathon.php';
// Call the query in DB class
require_once __DIR__ . '/classes/query_marathon.php';
?>
<!DOCTYPE html>
<html lang="fr">
<head>
    <meta charset="UTF-8">
    <title>Marathon</title>
</head>
<body>
    <h1>Runners</h1>

    <!-- If error arrives in /config/database_marathon.php -->
    <?php if ($error_message): ?>
        <p style="color: red;"><?= htmlspecialchars($error_message); ?></p>
    <?php endif; ?>
    
    <ul>
        <?php

        // send to query_marathon() the result of the connection to DB
        $query = new query_marathon($pdo);

        // SQL query to get the runners
        $sql = "SELECT IN_COUREUR_FK, IN_DOSSARD FROM INSCRIPTION";
        $query_results = $query->fetchAll($sql);

        // Display the result
        foreach ($query_results as $query_result) {
            echo "<li> Runner ID " . htmlspecialchars($query_result['IN_COUREUR_FK']) . " has Bib " . htmlspecialchars($query_result['IN_DOSSARD']) . "</li>";
        }
        ?>
    </ul>
</body>
</html>

Insert / update / delete

add_runner.php to insert a new row in the Runner table

<?php
// Call the PDO connexion
require_once __DIR__ . '/config/database_marathon.php';
// Call the query in DB class
require_once __DIR__ . '/classes/query_marathon.php';
?>
<!DOCTYPE html>
<html lang="fr">
<head>
    <meta charset="UTF-8">
    <title>Add a runner</title>
</head>
<body>
    <h1>Add a runner</h1>

    <!-- If error arrives in /config/database_marathon.php -->
    <?php if ($error_message): ?>
        <p style="color: red;"><?= htmlspecialchars($error_message); ?></p>
    <?php endif; ?>

		<!-- A form with post method -->
    <form method="post" action="">
        <label for="lastname">Last name :</label>
        <input type="text" name="lastname" id="lastname" required>
        <br><br>

        <label for="firstname">First name :</label>
        <input type="text" name="firstname" id="firstname" required>
        <br><br>

        <label for="birthdate">Birthdate :</label>
        <input type="date" name="birthdate" id="birthdate" required>
        <br><br>

        <label for="gender">Gender :</label>
        <select name="gender" id="gender" required>
            <option value="">-- Select --</option>
            <option value="1">Man</option>
            <option value="2">Woman</option>
        </select>
        <br><br>

        <button type="submit" name="add">Add</button>
    </form>

    <?php
    // Form check
    if ($_SERVER["REQUEST_METHOD"] === "POST" && isset($_POST['add'])) {
        $lastname = trim($_POST['lastname']); // $_POST contains all the data that are send in the post
        $firstname = trim($_POST['firstname']); // trim() is used to remove unnecessary spaces at the beginning and end of a string
        $birthdate = trim($_POST['birthdate']);
        $gender = trim($_POST['gender']);

        if (!empty($lastname) && !empty($firstname)) {
            // Prepared SQL statement to prevent SQL injection attacks
            // send to query_marathon() the result of the connection to DB
            $query = new query_marathon($pdo);
            $sql = "INSERT INTO COUREUR (CO_NOM, CO_PRENOM, CO_NAISSANCE, CO_SEXE) VALUES (:lastname, :firstname, :birthdate, :gender)";
            $params = [
                ':lastname' => $lastname,
                ':firstname' => $firstname,
                ':birthdate' => $birthdate,
                ':gender' => $gender
            ];
            try {
                $query_results = $query->execute($sql, $params);

                echo "<p style='color: green;'>✅ Runner added with success !</p>";
                echo "Number of row inserted : {$query_results->rowCount()}";
            } catch (PDOException $e) {
                echo "<p style='color: red;'>❌ Database error: " . htmlspecialchars($e->getMessage()) . "</p>";
            }
        } else {
            echo "<p style='color: red;'>❌ Please complete all required fields.</p>";
        }
    }
    ?>
</body>
</html>

Prepared SQL statement

A secure method for executing SQL queries with dynamic parameters, while protecting your database from SQL injection.

It allows you to separate the structure of the SQL query from the data provided by the user.

It prevents a malicious user from injecting SQL code into a form (a well-known attack: SQL injection).

📌 Structure of a prepared statement in PDO:

1️⃣ Prepare the query with placeholders (:param):

$sql = "INSERT INTO COUREUR (CO_NOM, CO_PRENOM) VALUES (:nom, :prenom)";

2️⃣ Bind(link) and execute the values securely:

$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':nom' => $nom,
    ':prenom' => $prenom
]);

✅ The SQL engine knows that :nom and :prenom are values, not SQL code, so even if someone tries to insert “DROP TABLE COUREUR”, it won’t work.

The PDO engine first sends the structure of the SQL query to the server, without any data inside it yet.

Then the data (:nom, etc.) is sent separately, as raw values, and not interpreted as SQL code.

This prevents someone from inserting code into a form input field.

PHP Logs

✅ Macos : /Applications/MAMP

✅ Windows : C:\xampp\php\logs\php_error_log

Exercices

Create a new page with a query to list all the clubs.

Create a new form on a separate page to add a new adhesion
1. Select a runner → provide the option to add a new runner if they are not in the list.
1. Select a club → provide the option to add a new club if it is not in the list.
1. Add the input fields for the adhesion (year, license number, fee).
1. Insert the new runner (if needed), the new club (if needed) and finally the adhesion.