Interacting with MySQL

Introduction

WebMVC framework\Model extends PHP \MySQLi class for a native interaction with MySQL. In this page, we show you the basic step for creating, connecting and using a MySQL database and also for retrieving data.

MySQL interaction

We can modify the previous example and retrieve the array of users and roles from a database. Before we do so, it must be taken into account that:

• WebMVC uses PHP \MySqli to interact with MySQL database
• The framework\Model attribute $sql, and the methods updateResultSet(), and getResultSet() are designed for:
  • Model->$sql is a class attribute to store any valid SQL statement, e.g a SELECT query, INSERT, UPDATE and so on.
  • Model->updateResultSet() executes a SQL statement previously stored in the attribute Model->$sql
  • Model->getResultSet() returns the result set of the executed SQL statement by means of Model->updateResultSet()
• You must configure the file config\application.config.php. Specifically, you must modify the constants DBHOST, DBUSER, DBPASSWORD, DBNAME, and DBPORT according to your MySQL setting.
  The code belowe shows the section in config\application.config.php regarding MySQL configuration:

/**
 * Defines the constants for MySQL database connection parameters.
 */
/**
 *  MySQL Host
 */
define("DBHOST","TOUR_DB_HOST");
/**
 *  MySQL User
 */
define("DBUSER","YOUR_USER");
/**
 * MySQL Password
 */
define("DBPASSWORD","YOUR_PASSWORD");
/**
 *  MySQL Database
 */
define("DBNAME","YOUR_DB_NAME");
/**
 *  MySQL Port
 */
define('DBPORT', '3306');

In this example, we modify the models\NestedBlocks for enable it to retrieve data from some database tables. We assume:

• the availability of a relational database schema, named mvc_wiki, containing tables and data for managing users and roles.
• the availability of a table called user containing the same data of the array $users shown in the previous example
• the availability of a table called application_role containing all available application roles that can be assigned to a user
• the availability of a table called users_roles containing roles of each user and also reflecting the same roles assignment we did in the array $usersRoles shown in the example before

The following figure shows the database schema and tables' relationships requirements:

Note that, when we design the database, we use lowercase and under_scores, rather the camelCase or PascalCase, for naming convention of tables and fields. This convention is widely adopted for MySQL design.

To build the required db schema and data you can use the following mvc_wiki_db.sql MySQL script:

-- MySQL Script for mvc_wiki database

-- Temporary disabling DB constraints checking
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';


-- -----------------------------------------------------
-- Create the schema for a simple database named mvc_wiki
-- -----------------------------------------------------
CREATE DATABASE IF NOT EXISTS `mvc_wiki` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `mvc_wiki`;

-- -----------------------------------------------------
-- Create table `user`
-- Stores users information
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `user` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(45) NULL,
  `user_email` VARCHAR(100) NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Create table `application_role`
-- Stores all available application roles 
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `application_role` (
  `role_id` INT NOT NULL AUTO_INCREMENT,
  `role_name` VARCHAR(45) NULL,
  PRIMARY KEY (`role_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Create table `users_roles`
-- Stores users roles 
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users_roles` (
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `role_id`),
  INDEX `fk_user_has_application_role_application_role1_idx` (`role_id` ASC),
  INDEX `fk_user_has_application_role_user_idx` (`user_id` ASC),
  CONSTRAINT `fk_user_has_application_role`
    FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_application_role_assigned_to_user`
    FOREIGN KEY (`role_id`)
    REFERENCES `application_role` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- Restoring DB constraints checking
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Sample data for table `user`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `user` (`user_id`, `user_name`, `user_email`) VALUES (1, 'Mark', '[email protected]');
INSERT INTO `user` (`user_id`, `user_name`, `user_email`) VALUES (2, 'Elen', '[email protected]');
INSERT INTO `user` (`user_id`, `user_name`, `user_email`) VALUES (3, 'John', '[email protected]');
COMMIT;


-- -----------------------------------------------------
-- Sample data data for table `application_role`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `application_role` (`role_id`, `role_name`) VALUES (5, 'admin');
INSERT INTO `application_role` (`role_id`, `role_name`) VALUES (4, 'webmaster');
INSERT INTO `application_role` (`role_id`, `role_name`) VALUES (3, 'moderator');
INSERT INTO `application_role` (`role_id`, `role_name`) VALUES (2, 'editor');
INSERT INTO `application_role` (`role_id`, `role_name`) VALUES (1, 'user');
COMMIT;


-- -----------------------------------------------------
-- Sample data for table `users_roles`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `users_roles` (`user_id`, `role_id`) VALUES (1, 5);
INSERT INTO `users_roles` (`user_id`, `role_id`) VALUES (1, 4);
INSERT INTO `users_roles` (`user_id`, `role_id`) VALUES (1, 3);
INSERT INTO `users_roles` (`user_id`, `role_id`) VALUES (2, 3);
INSERT INTO `users_roles` (`user_id`, `role_id`) VALUES (2, 2);
INSERT INTO `users_roles` (`user_id`, `role_id`) VALUES (2, 1);
INSERT INTO `users_roles` (`user_id`, `role_id`) VALUES (3, 2);
INSERT INTO `users_roles` (`user_id`, `role_id`) VALUES (3, 1);
COMMIT;

To create the schema on your MySQL database open a command line prompt and type (be sure the mysql excutable file is in your PATH):

mysql -uYOUR_USER -pYOUR_PASSWORD < mvc_wiki_db.sql

Finally, we can modify the models\NestedBlocks by update getUsersData() getUsersRoles() with database access PHP instructions for retrieving information. Pay attention that we need to returns data retrieved from the DB by using arrays also having the same formats required by the views\NestedBlocks. For example, the format of $usersRoles array must be the following:

See the code below of the new version models\NestedBlocks:

<?php
namespace models;

use framework\Model;

class NestedBlocks extends Model
{

    /**
     *  Retrieves users from db
     *
     * @return array|false Array of users in the format:
     *                     array( array('Username'=>'','UserEmai'=>'') )
     *                     Returns false when no data found
     */
    public function getUsersData()
    {
        // Notice: 
        // - We use PHP HereDoc to specify the SQL string
        $this->sql = <<<SQL
        SELECT 
            user_name as UserName,
            user_email as UserEmail
        FROM
            user;
SQL;
        $this->updateResultSet();
        // The mysqli result set already has the format:
        // array( array('Username'=>'','UserEmai'=>'') )
        return $this->getResultSet();
    }

    /**
     * Provides users actions.
     *
     * @return array Array of actions in the format:
     *               array( array('ActionName'=>'','ActionCaption'=>'') )
     */
    public function getUserActions()
    {
        $userActions = array(
            array("ActionName" => "email" ,"ActionCaption" => "Send email"),
            array("ActionName" => "edit"  ,"ActionCaption" => "Edit information"),
            array("ActionName" => "erase","ActionCaption" => "Delete user")
        );

        return $userActions;
    }

    /**
     * Retrieves users' roles from db.
     *
     * @return array|false Array of users roles in the format:
     *                     array(array("UserEmail"=>'',"UserRoles"=>array(r1,r2...,rn)))
     *                     Returns false when no data found
     */
    public function getUsersRoles()
    {

        // Notice: 
        // - We use PHP HereDoc to specify the SQL string
        // 
        // - The SQL below joins tables user and application_role for retrieving
        //   the values for email and role name of each id stored into the
        //   users_roles table.
        $this->sql = <<<SQL
        SELECT 
            user.user_id,
            user.user_email,
            application_role.role_name 
        FROM 
            user 
              JOIN users_roles ON ((user.user_id = users_roles.user_id)) 
                JOIN application_role ON ((users_roles.role_id = application_role.role_id))
        ORDER BY 
            application_role.role_id DESC 
SQL;
        $this->updateResultSet();
        $mySqlResults = $this->getResultSet();

        // Initializing an empty array for storing users roles
        $usersRoles= array();

        if ($mySqlResults) {
            while ($row = $mySqlResults->fetch_array()) {
                $userId=$row["user_id"];
                $userEmail=$row["user_email"];
                $userRoleName=$row["role_name"];
                
                // Adds email to $usersRoles array
                $usersRoles[$userId]["UserEmail"]=$userEmail;

                // Adds role to $usersRoles array
                $usersRoles[$userId]["UserRoles"][] = $userRoleName;
            }
        }
        return !empty($usersRoles)? $usersRoles : false;
    }

}

// TO DO

Summary

To run an MVC instance of an application we have seen how to:

• create and run a controller and its methods calling them from the URL
• create a views class linking it to a template file
• substitute a dynamic variable to a placeholder inside a template
• declare a block subject that must be transformed, for example in a list of values, within a template
• created the model class taking the result set from a database of people.

Pay attention to the flow of operations when you define an MVC application in WebMVC:

1. The URL calls a controller method (either with or without parameters)
2. The controller runs the method and retrieves data from the model
3. The data retrieved from the model are sent to the view by the controller
4. The view organizes the data for the presentation.
5. Finally, WebMVC sends the output of the execution to the user.