Development and production

Course Beginner Free

MySQL — The basics

Store the recipes in a relational database: tables, queries, joins and data integrity.

  • 4 guides
  • 41 concepts
Start reading

Guides

  1. 01 Guide MySQL glossary Explain the concepts in your own words and recognise them in an example. 14 concepts
  2. 02 Guide MySQL essentials Choose and use the essential elements to complete this step of the blog. 13 concepts
  3. 03 Guide MySQL workshop · À table ! Reproduce a working result, test it, then succeed at an exercise without copying. 8 concepts
  4. 04 Guide Resources and review Find reliable information and explain what you have built. 6 concepts

Suggested path

  1. 01. HTML: structure three recipes in a semantic page.
  2. 02. CSS: make the blog readable on mobile and on desktop.
  3. 03. JavaScript: filter the recipes without reloading the page.
  4. 04. PHP: produce the pages from arrays and process a form.
  5. 05. MySQL: keep the recipes in a relational database.
  6. 06. PDO: read and add recipes from PHP with prepared queries.

Sources and references

Beginner workshop, step 5/6. The complete files are materials to reproduce in a separate local project. Read the prerequisites, the expected results and the limits stated in the workshop.

Example files

database/schema.sql
-- À exécuter dans un schéma d’exercice neuf. Ce script ne supprime rien.
CREATE DATABASE blog_recettes CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE blog_recettes;

CREATE TABLE categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE recettes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  titre VARCHAR(120) NOT NULL,
  duree_minutes SMALLINT UNSIGNED NOT NULL,
  portions TINYINT UNSIGNED NOT NULL,
  ingredients TEXT NOT NULL,
  etapes TEXT NOT NULL,
  categorie_id INT UNSIGNED NOT NULL,
  cree_le TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT ck_duree CHECK (duree_minutes BETWEEN 1 AND 1440),
  CONSTRAINT ck_portions CHECK (portions BETWEEN 1 AND 20),
  CONSTRAINT fk_categorie FOREIGN KEY (categorie_id)
    REFERENCES categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

INSERT INTO categories (nom) VALUES ('Entrée'), ('Plat'), ('Dessert');

-- Les identifiants ci-dessous correspondent à une base neuve : 1, 2, 3.
INSERT INTO recettes (titre, duree_minutes, portions, ingredients, etapes, categorie_id) VALUES
('Soupe de carottes', 20, 2,
 CONCAT('4 carottes', CHAR(10), '1 oignon', CHAR(10), '500 ml d’eau'),
 CONCAT('Éplucher et couper les légumes.', CHAR(10), 'Cuire dans l’eau 15 minutes puis mixer.'), 2),
('Salade de tomates', 10, 2,
 CONCAT('3 tomates', CHAR(10), '1 cuillère d’huile d’olive', CHAR(10), 'Quelques feuilles de basilic'),
 CONCAT('Laver et couper les tomates.', CHAR(10), 'Ajouter l’huile et le basilic, mélanger.'), 1),
('Tarte aux pommes', 45, 4,
 CONCAT('1 pâte brisée', CHAR(10), '3 pommes', CHAR(10), '2 cuillères de compote'),
 CONCAT('Préchauffer le four à 180 °C et étaler la pâte.', CHAR(10), 'Garnir de compote et de pommes puis cuire 30 minutes.'), 3);

SELECT r.id, r.titre, r.duree_minutes, c.nom AS categorie
FROM recettes AS r
JOIN categories AS c ON c.id = r.categorie_id
ORDER BY r.id;

In the same series Step 5 of 6

  1. 1 Course HTML — The basics
  2. 2 Course CSS — The basics
  3. 3 Course JavaScript — The basics
  4. 4 Course PHP — The basics
  5. 5 Course MySQL — The basics You are here
  6. 6 Course PDO — The basics

Share this article

In the same category

Course

Development and production · Beginner

HTML — The basics

Build a semantic, accessible web page step by step with a running project: a recipe mini-blog.

4 guides · 38 concepts In the same series

Course

Development and production · Intermediate

Node.js — The fundamentals

Understand the Node.js runtime: modules, asynchronous code, npm and building a small server.

4 guides · 44 concepts In the same series

Course

Development and production · Beginner

CSS — The basics

Style the recipe mini-blog and make it responsive: selectors, box model, flexbox, grid and mobile-first.

4 guides · 39 concepts In the same series