PHP

MVC con PHP y procedimientos almacenados en MySQL

Adonis G. Sinclair

• 14 min read
MVC con PHP y procedimientos almacenados en MySQL

Cuando se trabaja una aplicación combinando bases de datos MySQL y PHP es normal pensar que todo se debe hacer desde el código. Sin embargo, no está demás separar la lógica de programación de la lógica de los datos.

Es por esta razón que se podrían utilizar procedimientos almacenados para no sobrecargar el código y dejar toda la lógica de datos al SGBD (Sistema Gestor de Base de Datos), siempre teniendo el cuidado de segmentar correctamente el código. Para ello,  se puede utilizar en PHP el patrón MVC (Modelo - Vista - Controlador).

Procedimientos Almacenados

“A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again” (Fuente: www.w3schools.com/sql/sql_stored_procedures.asp).

Tal como lo dice, un procedimiento almacenado permite guardar el código (consultas) SQL en el SGBD, de tal manera que podría ser reutilizado de forma más eficiente.

MVC

MVC (Modelo - Vista - Controlador) es un patrón en el diseño de software comúnmente utilizado para implementar interfaces de usuario, datos y lógica de control. Enfatiza una separación entre la lógica de negocios y su visualización (Fuente: www.developer.mozilla.org/es/docs/Glossary/MVC).

Tecnología que emplearemos

  • Visual Studio Code.
  • MySQL (versión 8.0 de preferencia).
  • MySQL Workbench.
  • WampServer.
  • Bootstrap Framework.

El funcionamiento básico de la aplicación en relación al código se lleva a cabo bajo la estructura del patrón MVC con el lenguaje PHP, el cual tiene comunicación con cada una de sus capas.

La capa de los modelos (M) tendrá comunicación doble vía con los procedimientos almacenados y la base de datos MySQL, de la misma manera que el ruteador (router) servirá de puente entre el controlador y la vista.

Veamos la figura a continuación:

Creación de entorno servidor

Para iniciar el pequeño proyecto, es necesario tener creado un entorno servidor local con la herramienta WampServer debidamente instalada y configurada. Si es necesaria ayuda adicional para configurarlo, ve el siguiente video.

Después de configurar el servidor local, es necesario descargar e instalar la herramienta MySQL Workbench para crear el espacio de base de datos a utilizar en MySQL Server.

Datos para configurar conexión de servidor local en MySQL Workbench:

  • Connection Name: (de preferencia).
  • Hostname: 127.0.0.1.
  • Port: 3306.
  • Username: root.
  • Password: ****** (vacía por defecto).

Creación de base de datos y tablas

A continuación, se debe acceder a la conexión configurada anteriormente y crear la base de datos y tablas necesarias, utilizando scripts con la herramienta MySQL Workbench.

Para crear la base de datos debemos ejecutar el siguiente script:

CREATE SCHEMA notas DEFAULT CHARACTER SET utf8;

USE notas;

DROP TABLE if exists notas;

CREATE TABLE notas(
 idnota INT not null AUTO_INCREMENT,
 autor varchar(20) not null,
 titulo varchar(30) not null,
 descripcion varchar(150) not null,
 fecha_hora TIMESTAMP null DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(idnota),
 UNIQUE INDEX index_titulo_unico (titulo ASC),
 INDEX index_autor (autor ASC)
)
engine = InnoDB
default character set=utf8;


Ejecuta el siguiente script para la creación de los procedimientos almacenados:

USE notas;
drop procedure if exists SP_Nueva_Nota;
drop procedure if exists SP_Obtener_Notas;
drop procedure if exists SP_Obtener_Nota_Por_Id;
drop procedure if exists SP_Modificar_Nota;
drop procedure if exists SP_Eliminar_Nota_Por_Id;

#definimos delimitador
delimiter //

#store procedure para crear nueva nota
create procedure SP_Nueva_Nota(
 in prm_autor varchar(20),
    in prm_titulo varchar(30),
    in prm_descripcion varchar(150)
)
begin
 insert into notas(autor,titulo,descripcion)
    values(prm_autor,prm_titulo,prm_descripcion);
end//

#store procedure para obtener todas las notas
create procedure SP_Obtener_Notas()
begin
 select * from notas;
end//

#store procedure para obtener nota por id
create procedure SP_Obtener_Nota_Por_Id(in prm_idnota int)
begin
 select * from notas
    where idnota=prm_idnota;
end//

#store procedure para procedimiento para modificar notas
create procedure SP_Modificar_Nota(
 in prm_idnota int,
 in prm_autor varchar(20),
    in prm_titulo varchar(30),
    in prm_descripcion varchar(150)
)
begin
 update notas
    set autor=prm_autor, titulo=prm_titulo, descripcion=prm_descripcion
    where idnota=prm_idnota;
end//

#store procedure para eliminar nota por id
create procedure SP_Eliminar_Nota_Por_Id(in prm_idnota int)
begin
 delete from notas
    where idnota=prm_idnota;
end//

Creación de la estructura de carpetas

En la carpeta que se ha creado para el proyecto en el servidor local, emplea Visual Studio Code para crear la siguiente estructura de carpetas:

Crear archivos de configuración path y base de datos

Código de Config.php:

<?php

    //definimos constantes de conexión
    define('LOCAL_PATH','http://localhost:{puerto}/{nombre_carpeta_app}/');
    define('DB_HOST','127.0.0.1');
    define('DB_PORT','3306');
    define('DB_DATABASE','notas');
    define('DB_USER','root');
    define('DB_PASS','');

    define('CHARSET','utf8');

?>

Nota: Reemplazar {puerto} y {nombre_carpeta_app}.

Código de Database.php:

<?php

    require_once __DIR__ .'/../model/Config.php';

    class Database{

        private function conectar(){

            $basedatos = new PDO('mysql:host='.DB_HOST.':'.DB_PORT.';'.'dbname='.DB_DATABASE.';charset='.CHARSET.'',DB_USER,DB_PASS);
            return $basedatos;

        }

        //Función para ejecutar procedimiento almacenado general
        public function EjecutarSPConParams($consulta, $parametros){
          
            try{
                $conexion = $this->conectar();
                $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $sentencia = $conexion->prepare($consulta);
                $sentencia->execute($parametros);

                $respuesta = $sentencia->fetchAll(PDO::FETCH_ASSOC);
                $sentencia->closeCursor();

                //limpiamos
                $conexion=null;
                $sentencia=null;

                return $respuesta;

            }catch(PDOException $exception) {
                return $exception;
            }
        }

        //Función para ejecutar procedimiento sin parámetros
        public function EjecutarSPSinParams($consulta){
          
            try{
                $conexion = $this->conectar();
                $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $sentencia = $conexion->prepare($consulta);
                $sentencia->execute();

                $respuesta = $sentencia->fetchAll(PDO::FETCH_ASSOC);
                $sentencia->closeCursor();

                //limpiamos
                $conexion=null;
                $sentencia=null;

                return $respuesta;

            }catch(PDOException $exception) {
                return $exception;
            }
        }

    }

?>

Crear modelo, controlador y ruteador

Así se crea el modelo NotaModel.php:

<?php

    require_once 'Database.php';

    class NotaModel{

        private $conexion;

        public function __construct(){
            $this->conexion = new Database();
        }

        public function ObtenerNotasModel(){
            $consulta = "CALL SP_Obtener_Notas();";
            $respuesta = $this->conexion->EjecutarSPSinParams($consulta);
            return $respuesta;
        }

        public function ObtenerNotaPorIdModel($idnota){
            $consulta = "CALL SP_Obtener_Nota_Por_Id(:prm_idnota);";
            $parametros = array(
                "prm_idnota"=>$idnota
            );

            $respuesta = $this->conexion->EjecutarSPConParams($consulta, $parametros);
            return $respuesta;

        }

        public function NuevaNotaModel($datos){
            $consulta = "CALL SP_Nueva_Nota(:prm_autor, :prm_titulo, :prm_descripcion);";
            $parametros = array(
                "prm_autor"=>$datos['autor'],
                "prm_titulo"=>$datos['titulo'],
                "prm_descripcion"=>$datos['descripcion']
            );

            $respuesta = $this->conexion->EjecutarSPConParams($consulta, $parametros);
            return $respuesta;

        }

        public function ModificarNotaModel($datos){
            $consulta = "CALL SP_Modificar_Nota(:prm_idnota, :prm_autor, :prm_titulo, :prm_descripcion);";
            $parametros = array(
                "prm_idnota"=>$datos['idnota'],
                "prm_autor"=>$datos['autor'],
                "prm_titulo"=>$datos['titulo'],
                "prm_descripcion"=>$datos['descripcion']
            );

            $respuesta = $this->conexion->EjecutarSPConParams($consulta, $parametros);
            return $respuesta;

        }

        public function EliminarNotaModel($idnota){
            $consulta = "CALL SP_Eliminar_Nota_Por_Id(:prm_idnota);";
            $parametros = array(
                "prm_idnota"=>$idnota
            );

            $respuesta = $this->conexion->EjecutarSPConParams($consulta, $parametros);
            return $respuesta;

        }

    }

?>

Creación del controlador NotaController.php:

<?php

    require_once __DIR__ . '/../model/NotaModel.php';

    class NotaController{

        private $notaModel;
        private $respuesta;

        public function __construct(){
            $this->notaModel = new NotaModel();
        }

        public function ObtenerNotasController()
        {
            try {
                $resultados = $this->notaModel->ObtenerNotasModel();
                $this->respuesta = array(
                    "state" => true,
                    "notas" => $resultados
                );
            } catch (PDOException $pdoEx) {
                $this->respuesta = array(
                    "state" => false,
                    "mensaje" => $pdoEx->getMessage()
                );
            }

            return $this->respuesta;
        }

        public function NuevaNotaController($datos)
        {
            try {
                $resultados = $this->notaModel->NuevaNotaModel($datos);
                $this->respuesta = array(
                    "state" => true,
                    "resultado" => $resultados
                );
            } catch (PDOException $pdoEx) {
                $this->respuesta = array(
                    "state" => false,
                    "mensaje" => $pdoEx->getMessage()
                );
            }

            return $this->respuesta;
        }


    }

?>

Así se crea el archivo ruteador NotasRouter.php:

<?php

    require_once __DIR__ . '/../controller/NotaController.php';

    $accion = $_POST['accion'];

    switch ($accion) {
        case 'nuevo':
            echo NuevaNota();       
            break;
        default:
            break;
    }


    function NuevaNota()
    {
        $notaController = new NotaController();

        if(isset($_POST['autor'])
            && isset($_POST['titulo'])
            && isset($_POST['descripcion'])){

            //creamos arreglo de datos
            $datos = array(
                "autor" => $_POST['autor'],
                "titulo" => $_POST['titulo'],
                "descripcion" => $_POST['descripcion']
            );

            $respuesta = $notaController->NuevaNotaController($datos);
          
            if($respuesta['state']==true){
                header("Location: ../index.php");
            }         

        }
    }

?>

Creación de Vistas

Así se crea NotaView.php:

<?php
    require_once 'controller/NotaController.php';
    $notaController = new NotaController();

    $notas = $notaController->ObtenerNotasController();
?>

<div class="row">
    <div class="col-md-3 p-3 m-1 bg-info">
        <h4>Edición de Notas</h4>
        <form method="POST" action="router/NotasRouter.php">
            <div class="mb-3">
                <input type="text" name="accion" value="nuevo" hidden>
                <label for="in-autor" class="form-label">Autor</label>
                <input type="text" class="form-control" id="in-autor" name="autor" placeholder="Nombre de autor..." required>
            </div>
            <div class="mb-3">
                <label for="in-titulo" class="form-label">Título</label>
                <input type="text" class="form-control" id="in-titulo" name="titulo" placeholder="Título de nota..." required>
            </div>
            <div class="mb-3">
                <label for="tarea-descripcion">Descripción</label>
                <textarea class="form-control" placeholder="Descripción de nota..." id="tarea-descripcion" name="descripcion"></textarea>
            </div>
            <button type="submit" class="btn btn-primary">Guardar</button>
        </form>
    </div>

    <div class="col-md-8 p-3 m-1">
        <h4>Notas Guardadas</h4>
        <table class="table table-hover">
            <thead>
                <tr>
                    <th scope="col">ID</th>
                    <th scope="col">AUTOR</th>
                    <th scope="col">TITULO</th>
                    <th scope="col">DESCRIPCION</th>
                    <th scope="col">FECHA</th>
                </tr>
            </thead>
            <tbody>
                <?php if($notas['state']==1){
                    foreach ($notas['notas'] as $nota) {?>
                        <tr>
                            <th scope="row"><?php echo $nota['idnota'] ?></th>
                            <td><?php echo $nota['autor'] ?></td>
                            <td><?php echo $nota['titulo'] ?></td>
                            <td><?php echo $nota['descripcion'] ?></td>
                            <td><?php echo $nota['fecha_hora'] ?></td>
                        </tr>
                <?php } } ?>
            </tbody>
        </table>
    </div>

</div>

Crear index.php y archivo .htaccess

Así se crea index.php:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Notas</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css">
<body>
    <div class="container-fluid">
        <div class="row">
            <nav class="navbar bg-dark p-2" data-bs-theme="dark">
                <h1>Notas (MVC-PHP-MYSQL)</h1>
            </nav>
        </div>

        <?php include 'view/NotaView.php' ?>

    </div>

    <!-- Bootstrap Popper-->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.11.6/dist/umd/popper.min.js" integrity="sha384-oBqDVmMz9ATKxIep9tiCxS/Z9fNfEXiDAYTujMAeBAsjFuCZSmKbSSUnQlmh/jp3" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.min.js" integrity="sha384-mQ93GR66B00ZXjt0YO5KlohRA5SY2XofN4zfuZxLkoj1gXtW8ANNCe9d5Y3eG5eD" crossorigin="anonymous"></script>
</body>
</html>

Crear .thaccess

Para darle algo de seguridad a la aplicación se puede utilizar el archivo .htaccess y escribir las siguientes líneas de código:

#página de inicio
DirectoryIndex index.php

#acceso restrictivo a directorio principal
Options All -Indexes


Para probar la aplicación, accede desde el navegador con la URL configurada anteriormente en el archivo Config.php.

Conclusión

Quizás una de las razones más significativas  por las que se utiliza MVC es la fácil organización de código (adaptable a diferentes frameworks de la actualidad), su escalabilidad potencial y la facilidad de poder trabajar en equipo con otros desarrolladores.

En cuanto a los procedimientos almacenados en MySQL, se mejora el rendimiento con rápidas y eficientes respuestas en la aplicación, flexible en cuanto que los datos pueden ser accesibles por diferentes aplicaciones y lenguajes, no dejando por fuera la modularización de código SQL.

Espero que este material haya sido de utilidad. Hasta pronto.

Referencias

Documentation. (n.d.). PHP. Retrieved January 27, 2023, from https://www.php.net/docs.php

Get started with Bootstrap · Bootstrap v5.3. (n.d.). Bootstrap. Retrieved January 27, 2023, from https://getbootstrap.com/docs/5.3/

Los procedimientos almacenados en MySQL y sus ventajas. (2017, November 23). VIU. Retrieved January 27, 2023, from https://www.universidadviu.com/es/actualidad/nuestros-expertos/los-procedimientos-almacenados-en-mysql-y-sus-ventajas

MVC - Glosario de MDN Web Docs: Definiciones de términos relacionados con la Web | MDN. (2022, December 5). MDN Web Docs. Retrieved January 27, 2023, from http://www.developer.mozilla.org/es/docs/Glossary/MVC

MySQL :: MySQL 8.0 Reference Manual. (n.d.). MySQL :: MySQL 8.0 Reference Manual. Retrieved January 27, 2023, from https://dev.mysql.com/doc/refman/8.0/en/

Rodríguez, F. (2022, July 13). Qué es la arquitectura MVC. KeepCoding. Retrieved January 27, 2023, from https://keepcoding.io/blog/que-es-la-arquitectura-mvc/

SQL Stored Procedures for SQL Server. (n.d.). W3Schools. Retrieved January 27, 2023, from http://www.w3schools.com/sql/sql_stored_procedures.asp

Stored Procedure Advantages | SQL Server Stored Procedure Basics. (2002, February 8). InformIT. Retrieved January 27, 2023, from https://www.informit.com/articles/article.aspx?p=25288&seqNum=3

💡
Las opiniones y comentarios emitidos en este artículo son propiedad única de su autor y no necesariamente representan el punto de vista de Revelo.

Revelo Content Network da la bienvenida a todas las razas, etnias, nacionalidades, credos, géneros, orientaciones, puntos de vista e ideologías, siempre y cuando promuevan la diversidad, la equidad, la inclusión y el crecimiento profesional de los profesionales en tecnología.