PHP CRUD usando Procedimiento Almacenado. Su uso es muy recomendado para agilizar procesos y su rendimiento es alto. En este articulo detallaremos el paso a paso para integrar procedimientos almacenado en un CRUD PHP.
PHP CRUD usando Procedimiento Almacenado
Una de las ventajas es prevenir ataques de inyección SQL. Por lo tanto, usar procedimientos almacenados en lugar de concatenación de cadenas para construir consultas dinámicas desde los datos de entrada del usuario para todas las sentencias SQL.
Sin embargo, reduce la posibilidad de ataques de inyección SQL porque todo lo colocado en un parámetro está entre comillas en el proceso.
Estructura de archivos para la operación CRUD
- dbconfig.php: utilizado para la conexión a la base de datos
- tblusers.sql: contiene la estructura de la tabla de la base de datos
- insert.php– utilizado para agregar un registro en la base de datos
- index.php: se usará para leer el registro de la base de datos.
- update.php: se utiliza para actualizar un registro.
Primer paso: crear base de datos
Abra la aplicación MySQL Workbench o use el gestor PHPMyAdmin, cree una base de datos llamada ‘spcruddb’. Después de crear la base de datos, ejecute y/o importe el script SQL. Una vez ejecutada las siguientes líneas de código creara una tabla con todas las columnas necesarias para este ejemplo.
CREATE TABLE IF NOT EXISTS `tblusers` (
`id` int(11) NOT NULL,
`FirstName` varchar(150) NOT NULL,
`LastName` varchar(150) NOT NULL,
`EmailId` varchar(120) NOT NULL,
`ContactNumber` char(11) NOT NULL,
`Address` varchar(255) NOT NULL,
`PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Indexes for table `tblusers`
--
ALTER TABLE `tblusers`
ADD PRIMARY KEY (`id`);
-- AUTO_INCREMENT for dumped tables
-- AUTO_INCREMENT for table `tblusers`
--
ALTER TABLE `tblusers`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;Segundo paso: Archivo de conexión a la base de datos (dbconfig.php)
Para dinamizar la conexión es recomendable crear un archivo que contenga la conexión hacia MySQL y este archivo tendrá de nombre dbconfig.php que contendrá constantes con los valores de MySQL y agregue las siguientes líneas de código:
<?php
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'spcruddb');
$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}Tercer paso: Insertar un registro en la base de datos
Necesitamos insertar registros a MySQL a través de nuestro CRUD y usaremos un archivo de nombre insert.php para insertar un registro en la base de datos. Sin embargo, esta página incluye Librerías y framework, un formulario HTML con un campo de entrada donde podemos completar los datos.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>PHP CURD Operation using Stored Procedure </title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h3>Insert Record | PHP CRUD Operations using Stored Procedure</h3>
<hr />
</div>
</div>
<form name="insertrecord" method="post">
<div class="row">
<div class="col-md-4"><b>First Name</b>
<input type="text" name="firstname" class="form-control" required>
</div>
<div class="col-md-4"><b>Last Name</b>
<input type="text" name="lastname" class="form-control" required>
</div>
</div>
<div class="row">
<div class="col-md-4"><b>Email id</b>
<input type="email" name="emailid" class="form-control" required>
</div>
<div class="col-md-4"><b>Contactno</b>
<input type="text" name="contactno" class="form-control" maxlength="10" required>
</div>
</div>
<div class="row">
<div class="col-md-8"><b>Address</b>
<textarea class="form-control" name="address" required></textarea>
</div>
</div>
<div class="row" style="margin-top:1%">
<div class="col-md-8">
<input type="submit" name="insert" value="Submit">
</div>
</div>
</form>
</div>
</div>
</body>
</html>Importaremos el procedimiento almacenado para la inserción de datos «sp_insert» en la base de datos
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert`(
fname varchar(120),
lname varchar(120),
emailid varchar(150),
cntnumber bigint(12),
address varchar(255)
)
BEGIN
insert into tblusers(FirstName,LastName,EmailId,ContactNumber,Address) value(fname,lname,emailid,cntnumber,address);
END$$
DELIMITER ;Archivo PHP para Inserción de Datos
Aquí está el código completo que hemos escrito para la inserción de datos (insert.php), contiene el fichero de conexión con MySQL, condicionales y método POST para procesar la información. Además, haremos uso del procedimiento almacenado de inserción.
<?php
// include database connection file
require_once'dbconfig.php';
if(isset($_POST['insert']))
{
// Posted Values
$fname=$_POST['firstname'];
$lname=$_POST['lastname'];
$emailid=$_POST['emailid'];
$contactno=$_POST['contactno'];
$address=$_POST['address'];
// Call the store procedure for insertion
$sql=mysqli_query($con,"call sp_insert('$fname','$lname','$emailid','$contactno','$address')");
if($sql)
{
// Message for successfull insertion
echo "<script>alert('Record inserted successfully');</script>";
echo "<script>window.location.href='index.php'</script>";
}
else
{
// Message for unsuccessfull insertion
echo "<script>alert('Something went wrong. Please try again');</script>";
echo "<script>window.location.href='index.php'</script>";
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>PHP CURD Operation using Stored Procedure </title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h3>Insert Record | PHP CRUD Operations using Stored Procedure</h3>
<hr />
</div>
</div>
<form name="insertrecord" method="post">
<div class="row">
<div class="col-md-4"><b>First Name</b>
<input type="text" name="firstname" class="form-control" required>
</div>
<div class="col-md-4"><b>Last Name</b>
<input type="text" name="lastname" class="form-control" required>
</div>
</div>
<div class="row">
<div class="col-md-4"><b>Email id</b>
<input type="email" name="emailid" class="form-control" required>
</div>
<div class="col-md-4"><b>Contactno</b>
<input type="text" name="contactno" class="form-control" maxlength="10" required>
</div>
</div>
<div class="row">
<div class="col-md-8"><b>Address</b>
<textarea class="form-control" name="address" required></textarea>
</div>
</div>
<div class="row" style="margin-top:1%">
<div class="col-md-8">
<input type="submit" name="insert" value="Submit">
</div>
</div>
</form>
</div>
</div>
</body>
</html>Cuarto paso: Mostrar registros de la base de datos
Debemos de importar este procedimiento almacenado (sp_read) para leer datos
BEGIN
select * from tblusers;
ENDAhora, cree un archivo index.php para mostrar todos los registros de la base de datos en una tabla HTML a través de una consulta SQL.
<?php
// include database connection file
require_once'dbconfig.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>PHP CRUD Operations using Stored Procedure </title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
<style type="text/css">
</style>
<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script src="https://getbootstrap.com/dist/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h3>PHP CRUD Operations using Stored Procedure</h3> <hr />
<a href="insert.php"><button class="btn btn-primary"> Insert Record</button></a>
<div class="table-responsive">
<table id="mytable" class="table table-bordred table-striped">
<thead>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Contact</th>
<th>Address</th>
<th>Posting Date</th>
<th>Edit</th>
<th>Delete</th>
</thead>
<tbody>
<?php
$sql =mysqli_query($con, "call sp_read()");
$cnt=1;
$row=mysqli_num_rows($sql);
if($row>0){
while ($result=mysqli_fetch_array($sql)) {
?>
<tr>
<td><?php echo htmlentities($cnt);?></td>
<td><?php echo htmlentities($result['FirstName']);?></td>
<td><?php echo htmlentities($result['LastName']);?></td>
<td><?php echo htmlentities($result['EmailId']);?></td>
<td><?php echo htmlentities($result['ContactNumber']);?></td>
<td><?php echo htmlentities($result['Address']);?></td>
<td><?php echo htmlentities($result['PostingDate']);?></td>
<td><a href="update.php?id=<?php echo htmlentities($result['id']);?>"><button class="btn btn-primary btn-xs"><span class="glyphicon glyphicon-pencil"></span></button></a></td>
<td><a href="index.php?del=<?php echo htmlentities($result['id']);?>"><button class="btn btn-danger btn-xs" onClick="return confirm('Do you really want to delete');"><span class="glyphicon glyphicon-trash"></span></button></a></td>
</tr>
<?php
// for serial number increment
$cnt++;
} } else { ?>
<tr>
<td colspan="9" style="color:red; font-weight:bold;text-align:center;"> No Record found</td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</body>
</html>Quinto paso: Actualizar registro en la base de datos
Importamos el siguiente procedimiento almacenado (sp_sp_readarow) para leer datos de una fila en particular, es decir usando la cláusula WHERE
BEGIN
select * from tblusers where id=rid;
ENDObtener datos en HTML
Crearemos el archivo update.php. Para devolver un registro de MySQL y actualizar un registro, debemos obtener la identificación de la fila de ese registro y almacenarla en $id. Por lo tanto, accedemos a la variable $_GET[‘id’] para hacerlo.
Aquí proporcionare todo el código HTML requerido para esta acción.
<?php
// include database connection file
require_once'dbconfig.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>PHP CURD Operation using Stored Procedure </title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h3>Update Record | PHP CRUD Operations using Stored Procedure</h3>
<hr />
</div>
</div>
<?php
// Get the userid
$userid=intval($_GET['id']);
$sql =mysqli_query($con, "call sp_readarow('$userid')");
while ($result=mysqli_fetch_array($sql)) {
?>
<form name="insertrecord" method="post">
<div class="row">
<div class="col-md-4"><b>First Name</b>
<input type="text" name="firstname" value="<?php echo htmlentities($result['FirstName']);?>" class="form-control" required>
</div>
<div class="col-md-4"><b>Last Name</b>
<input type="text" name="lastname" value="<?php echo htmlentities($result['LastName']);?>" class="form-control" required>
</div>
</div>
<div class="row">
<div class="col-md-4"><b>Email id</b>
<input type="email" name="emailid" value="<?php echo htmlentities($result['EmailId']);?>" class="form-control" required>
</div>
<div class="col-md-4"><b>Contactno</b>
<input type="text" name="contactno" value="<?php echo htmlentities($result['ContactNumber']);?>" class="form-control" maxlength="10" required>
</div>
</div>
<div class="row">
<div class="col-md-8"><b>Address</b>
<textarea class="form-control" name="address" required><?php echo htmlentities($result['Address']);?></textarea>
</div>
</div>
<?php } ?>
<div class="row" style="margin-top:1%">
<div class="col-md-8">
<input type="submit" name="update" value="Update">
</div>
</div>
</form>
</div>
</div>
</body>
</html>Procedimiento almacenado (sp_update) para actualizar un registro
BEGIN
update tblusers set FirstName=fname,LastName=lname,EmailId=emailid,ContactNumber=cntnumber,Address=address where id=rid;
ENDScript completo para actualizar el registro
<?php
// include database connection file
require_once'dbconfig.php';
if(isset($_POST['update']))
{
// Get the row id
$rid=intval($_GET['id']);
// Posted Values
$fname=$_POST['firstname'];
$lname=$_POST['lastname'];
$emailid=$_POST['emailid'];
$contactno=$_POST['contactno'];
$address=$_POST['address'];
// Store Procedure for Updation
$sql=mysqli_query($con,"call sp_update('$fname','$lname','$emailid','$contactno','$address','$rid')");
// Mesage after updation
echo "<script>alert('Record Updated successfully');</script>";
// Code for redirection
echo "<script>window.location.href='index.php'</script>";
}
?>
Aquí está el código completo que hemos escrito para la actualización de datos (update.php)
<?php
// include database connection file
require_once'dbconfig.php';
if(isset($_POST['update']))
{
// Get the row id
$rid=intval($_GET['id']);
// Posted Values
$fname=$_POST['firstname'];
$lname=$_POST['lastname'];
$emailid=$_POST['emailid'];
$contactno=$_POST['contactno'];
$address=$_POST['address'];
// Store Procedure for Updation
$sql=mysqli_query($con,"call sp_update('$fname','$lname','$emailid','$contactno','$address','$rid')");
// Mesage after updation
echo "<script>alert('Record Updated successfully');</script>";
// Code for redirection
echo "<script>window.location.href='index.php'</script>";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>PHP CURD Operation using Stored Procedure </title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h3>Update Record | PHP CRUD Operations using Stored Procedure</h3>
<hr />
</div>
</div>
<?php
// Get the userid
$userid=intval($_GET['id']);
$sql =mysqli_query($con, "call sp_readarow('$userid')");
while ($result=mysqli_fetch_array($sql)) {
?>
<form name="insertrecord" method="post">
<div class="row">
<div class="col-md-4"><b>First Name</b>
<input type="text" name="firstname" value="<?php echo htmlentities($result['FirstName']);?>" class="form-control" required>
</div>
<div class="col-md-4"><b>Last Name</b>
<input type="text" name="lastname" value="<?php echo htmlentities($result['LastName']);?>" class="form-control" required>
</div>
</div>
<div class="row">
<div class="col-md-4"><b>Email id</b>
<input type="email" name="emailid" value="<?php echo htmlentities($result['EmailId']);?>" class="form-control" required>
</div>
<div class="col-md-4"><b>Contactno</b>
<input type="text" name="contactno" value="<?php echo htmlentities($result['ContactNumber']);?>" class="form-control" maxlength="10" required>
</div>
</div>
<div class="row">
<div class="col-md-8"><b>Address</b>
<textarea class="form-control" name="address" required><?php echo htmlentities($result['Address']);?></textarea>
</div>
</div>
<?php } ?>
<div class="row" style="margin-top:1%">
<div class="col-md-8">
<input type="submit" name="update" value="Update">
</div>
</div>
</form>
</div>
</div>
</body>
</html>Sexto paso: Eliminar un registro de la base de datos
Importaremos este procedimiento almacenado (sp_delete) para eliminar un registro en particular a través de la cláusula WHERE, veamos un ejemplo completo.
BEGIN
delete from tblusers where id=rid;
ENDCódigo PHP para la eliminación de datos
Copie y pegue este código en el archivo index.php, si observamos solo se ejecutará cuando el método «del» este activo y ejecutado.
<?php
// include database connection file
require_once'dbconfig.php';
// Code for record deletion
if(isset($_REQUEST['del']))
{
//Get row id
$rid=intval($_GET['del']);
//Qyery for deletion
$sql =mysqli_query($con,"call sp_delete('$rid')");
echo "<script>alert('Record deleted');</script>";
// Code for redirection
echo "<script>window.location.href='index.php'</script>";
}
?>
Conclusión
En este articulo hemos aprendido a usar procedimiento almacenados en los eventos del CRUD PHP, es decir (Leer, Insertar, Actualizar y eliminar).
Los procedimientos almacenados aumentan el rendimiento de las aplicaciones y mejora de la agilidad para que sean son rápidos porque el servidor MySQL aprovecha el almacenamiento en caché.
Espero que esta explicación les ayude a implementar procedimientos en sus proyectos web.
