IncludeBeer

In part 1 we created a very simple web application with static content. Now let's add some dynamism by integrating a MySQL database into it.

Creation of the database

CodeIgniter offers the possibility to manipulate the database with the Migration and Seeder classes, but to keep this example as simple as possible, we will run the SQL queries directly in MySQL. Create a new database with your favorite software, such as PhpMyAdmin, DBeaver, mysql from the command line or whatever you like. Then, run the following queries to create the tables and insert the sample data into them.

The recipe table contains the recipes: the id, the title of the recipe and its instructions. As a recipe contains several ingredients, we create an ingredient table which contains the list of ingredients of all the recipes: the id, the name of the ingredient, the required quantity and the id of the recipe to which it belongs. We also add a foreign key constraint to guarantee the integrity between the two tables. With this constraint, we are sure that the recipe_id column points to an existing recipe.

Run the following queries in your favorite database software to create the tables:

--
-- Table for the recipes
--
CREATE TABLE `recipe` (
  `id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `instructions` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `recipe`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `recipe`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Table for the ingredients
--
CREATE TABLE `ingredient` (
  `id` int(11) NOT NULL,
  `recipe_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `quantity` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `ingredient`
  ADD PRIMARY KEY (`id`),
  ADD KEY `recipe_fk` (`recipe_id`);

ALTER TABLE `ingredient`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Foreign key
--
ALTER TABLE `ingredient`
  ADD CONSTRAINT `recipe_fk` FOREIGN KEY (`recipe_id`) REFERENCES `recipe` (`id`);

Insert the data into the database

Run the following queries to add the recipes and their ingredients to the tables we just created. Obviously you can add real recipes instead of these silly examples:

--
-- Insert the recipes
-- The recipes id is automatically generated by MySQL
--
INSERT INTO `recipe`
  (`title`,
   `instructions`)
VALUES
  ('Boiling Water',
   'Put the water in a cauldron and boil.'),
  ('Tea',
   'Prepare the recipe for boiling water. Put the water in a cup, add the tea bag and let it steep for a few minutes.'),
  ('Glass of water',
   'Put ice cubes in a tall glass and fill with water. Add a slice of lemon if desired.');

--
-- Insert the ingredients for each recipe
-- The ingredient id is automatically generated by MySQL
-- but we select the id of the corresponding recipe by searching by its title
--
INSERT INTO `ingredient`
  (`recipe_id`,
   `name`,
   `quantity`)
VALUES
  ((SELECT `id` FROM `recipe` WHERE `title` = 'Boiling Water'),
   'Fresh water',
   '250 ml'),
  ((SELECT `id` FROM `recipe` WHERE `title` = 'Tea'),
   'Fresh water',
   '250 ml'),
  ((SELECT `id` FROM `recipe` WHERE `title` = 'Tea'),
   'Tea bag',
   '1'),
  ((SELECT `id` FROM `recipe` WHERE `title` = 'Glass of water'),
   'Fresh water',
   '300 ml'),
  ((SELECT `id` FROM `recipe` WHERE `title` = 'Glass of water'),
   'Ice cubes',
   '2-3'),
  ((SELECT `id` FROM `recipe` WHERE `title` = 'Glass of water'),
   'Lemon (optional)',
   '1 slice');

app/Config/Database.php

Configuration for the connection to the database. Adjust the values according to your configuration:

/**
 * The default database connection.
 *
 * @var array
 */
public $default = [
    'DSN'      => '',

    // Set the hostname. 
    // If MySQL is installed on your computer, localhost should be good.
    'hostname' => 'localhost',

    // Set the username and password. 
    // MySQL default account is root/root
    'username' => 'root',
    'password' => 'root',

    // Set the name for the database. 
    // I named it ci4 but it can be anything.
    'database' => 'ci4',

The models: RecipeModel and IngredientModel

CodeIgniter 4's Model class offers a lot of features. In its simplest form, all you have to do is define the table name, the type of object to return and the list of updatable fields. In this example, we allow to modify all the fields, except the id. As we do not yet have a form to edit a recipe, we could simply leave the field empty and no fields would be updatable.

app/Models/RecipeModel.php

<?php namespace App\Models;

use CodeIgniter\Model;
use App\Entities\Recipe;

class RecipeModel extends Model
{
    // The MySQL table name
    protected $table = 'recipe';

    // The type of object to return
    protected $returnType = Recipe::Class;

    // The updatable fields
    protected $allowedFields = [
        'title',
        'instructions',
    ];
}

app/Models/IngredientModel.php

<?php namespace App\Models;

use CodeIgniter\Model;
use App\Entities\Ingredient;

class IngredientModel extends Model
{
    protected $table = 'ingredient';
    protected $returnType = Ingredient::Class;

    protected $allowedFields = [
        'name',
        'quantity',
    ];
}

The Recipe and Ingredient entities

CodeIgniter 4 introduces a new concept that was not present in version 3, entities. We define one entity per table and instead of returning a generic object, the model will return an instance of this class. Like the Model class, the Entity class offers some interesting features, but for this example we will only add a variable that will contain the list of ingredients for each recipe. We don't need to add variables for id, title, etc. CodeIgniter will automatically add the fields from the MySQL table to it.

app/Entities/Recipe.php

Here is the Recipe entity. CodeIgniter will automatically add the id, title and instructions variables from the recipe table. We add an array $ingredients for the list of ingredients of this recipe:

<?php namespace App\Entities;

use CodeIgniter\Entity;

class Recipe extends Entity
{
    public $ingredients;

    public function __construct (array $data = null)
    {
        parent::__construct($data);

        // Set the ingredient list to an empty array
        $this->ingredients = [];
    }
}

app/Entities/Ingredient.php

Here is the Ingrdient entity in its simplest form. CodeIgniter will automatically add the id, recipe_id, name and quantity variables from the ingredient table. We therefore have nothing else to do. The class is empty and only extends the Entity class:

<?php namespace App\Entities;

use CodeIgniter\Entity;

class Ingredient extends Entity
{
}

The MyRecipes library

To simplify obtaining the recipes and their ingredients, I created a MyRecipes library which takes care of loading the models and obtaining the data.

app/Libraries/MyRecipes.php

<?php namespace App\Libraries;

use App\Models\RecipeModel;
use App\Models\IngredientModel;

class MyRecipes
{
    /**
     * Get all recipes
     * @return array
     */
    public function getAllRecipes ()
    {
        // Create an instance for our two models
        $recipeModel = new RecipeModel();
        $ingredientModel = new IngredientModel();

        // SELECT the recipes, order by id
        $recipes = $recipeModel
            ->orderBy('id')
            ->findAll();

        // For each recipe, SELECT its ingredients
        foreach ($recipes as &$recipe)
        {
            $recipe->ingredients = $ingredientModel
                ->where( ['recipe_id' => $recipe->id] )
                ->orderBy('id')
                ->findAll();
        }
        unset($recipe);

        return $recipes;
    }
}

The controller: RecipesController

In the controller, we load the MyRecipes library and get the data from the MySQL database. The _dummy_data() function defined in part 1 can be deleted.

app/Controllers/RecipesController.php

<?php namespace App\Controllers;

use App\Libraries\MyRecipes;

class RecipesController extends BaseController
{
    public function index()
    {
        // Create an instance of our library
        $myRecipes = new MyRecipes();

        // Collect all the data used by the view in a $data array
        $data = [
            'page_title' => "My Recipes",
            'page_subtitle' => "I present you my favorite recipes...",
            'recipes' => $myRecipes->getAllRecipes(),
        ];

        /* Each of the items in the $data array will be accessible
         * in the view by variables with the same name as the key:
         * $page_title, $page_subtitle and $recipes
         */
        return view('recipe_list', $data);
    }
}

The recipe_list view

In the recipe_list view, we make some adjustments to display the data obtained from the database. The recipe variable is no longer a simple array. It is now an instance of the App\Entities\Recipe class. Just like the variable ingredient is no longer a string. It is now an instance of the App\Entities\Ingredient class.

app/Views/recipe_list.php

<?php
/**
 * @var string $page_title                   The page title (automatically created by CI from the $data array)
 * @var string $page_subtitle                The page subtitle (automatically created by CI from the $data array)
 * @var array  $recipes                      List of recipes (automatically created by CI from the $data array)
 * @var App\Entities\Recipe $recipe          One recipe (created by the foreach instruction)
 * @var App\Entities\Ingredient $ingredient  One ingredient (created by the foreach instruction)
 */
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title><?= esc($page_title) ?></title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet"
      href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css"
      integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk"
      crossorigin="anonymous">

<style type="text/css">
.title
{
    padding: 3rem 1.5rem;
}

article
{
    padding: 1.5rem 1.5rem;
}
</style>

</head>

<body>

<main role="main" class="container">
    <div class="title">
        <h1>
          <?= esc($page_title) ?>
          <small class="text-muted"><?= esc($page_subtitle) ?></small>
      </h1>
    </div>

    <div class="container">

<?php foreach ($recipes as $recipe): ?>
        <article>
            <h3><?= esc($recipe->title) ?></h3>
            <h5>Ingredients</h5>
            <ul>
            <?php foreach ($recipe->ingredients as $ingredient): ?>
                <li><?= esc($ingredient->quantity) ?> <?= esc($ingredient->name) ?></li>
            <?php endforeach; ?>
            </ul>
            <h5>Instructions</h5>
            <p><?= esc($recipe->instructions) ?></p>
        </article>
        <hr>
<?php endforeach; ?>

    </div>

</main>

<footer>
    <p class="text-center">&copy; 2020 My recipe website</p>
</footer>

</body>
</html>

Second part finished!

You can now refresh the page http://ci4.test:8888 which will always display the three recipes. At first glance there is not much difference, but the data is now obtained from MySQL. You can insert new recipes in the recipe and ingredient tables and refresh the page to see the new recipes. If you have a lot of recipes, it doesn't make much sense to have a long list. The recipe list should show only the title and the details of each recipe should be displayed on a new page. This is what we are going to do right now in the third part.