Aprovechando que para unas cosas de la escuela tuve que instalar SQL Server y SQL Server Management Studio (ya que por voluntad propia no lo haría jamás) decidí conectar PHP con SQL Server y hacer un CRUD, es decir, create, read, update y delete de una base de datos de SQL Server.
De esta manera si algún día alguien quiere consumir una base de datos de SQL Server con PHP puede tomar este post como referencia.
Al final tendremos una aplicación web como la siguiente, basada en una plantilla de Bootstrap 4.
Voy a mostrarte cómo hacer un select, update, insert y delete. Además de prevenir inyecciones SQL.
Recuerda que al final de todo lo que vamos a usar es PDO, una envoltura de las bases de datos que permite intercambiar de un motor a otro cambiando la cadena de conexión.
Nota: todo el código fuente aquí mostrado está actualizado y completo en mi GitHub. Siéntete libre de clonarlo, editarlo, descargarlo o mejorarlo.
Preparar entorno
Recuerda instalar SQL Server y SSMS para que puedas probar la conexión. También te sugiero crear un nuevo usuario y darle permiso de acceso a las bases de datos.
Un paso obligatorio es instalar la extensión de SQL Server en PHP.
Por cierto, como lo dije, PDO es un genérico para todos los motores de bases de datos, así que puedes ver otros ejemplos que aplican igualmente para SQL Server:
Esquema de la tabla
Para el CRUD que vamos a hacer se necesita crear una tabla con la siguiente estructura. En el archivo también incluyo algunos datos:
/*
CRUD con SQL Server y PHP
@author parzibyte [https://parzibyte.me/blog/]
@date 2019-06-03
================================
Este archivo define la tabla e inserta algunos datos
para trabajar
================================
*/
CREATE TABLE mascotas(
id bigint identity(1,1) primary key,
nombre varchar(50) NOT NULL,
edad smallint NOT NULL
);
insert into mascotas
(nombre, edad)
values
('Maggie', 3),
('Guayaba', 2),
('Capuchina', 2),
('Snowball', 1),
('Panqué', 1);
La tabla incluye un id autoincrementable.
DSN de PDO
Cuando ya estés preparado, la creación del objeto PDO con la cadena de conexión, así como el usuario, contraseña y bases de datos queda así:
<?php
/*
CRUD con SQL Server y PHP
@author parzibyte [https://parzibyte.me/blog/]
@date 2019-06-03
================================
Este archivo se encarga de conectar a la base de datos
y traer un objeto PDO
Recuerda cambiar tus credenciales, y tal vez ponerlas en
un archivo env: [https://parzibyte.me/blog/posts/leer-archivo-configuracion-ini-php/](https://parzibyte.me/blog/posts/leer-archivo-configuracion-ini-php/)
================================
*/
$contraseña = "hunter2";
$usuario = "usuario";
$nombreBaseDeDatos = "pruebas_parzibyte";
# Puede ser 127.0.0.1 o el nombre de tu equipo; o la IP de un servidor remoto
$rutaServidor = "127.0.0.1";
try {
$base_de_datos = new PDO("sqlsrv:server=$rutaServidor;database=$nombreBaseDeDatos", $usuario, $contraseña);
$base_de_datos->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {
echo "Ocurrió un error con la base de datos: " . $e->getMessage();
}
El servidor, si es local, es 127.0.0.1; si no, investiga la IP del servidor al que te vas a conectar.
Cambia el nombre de la base de datos, el usuario y la contraseña como sea requerido.
Antes de intentar conectar, intenta iniciar sesión con esas credenciales en SQL Server.
La línea de setAttribute es para indicar que si hay un error en la base de datos el mismo se refleje y lance como excepción en PHP.
Leer datos
Para leer datos existen dos maneras: trayéndolos como un arreglo o leyéndolos a través de un cursor.
Si no hay parámetros en la consulta, se llama al método query del objeto PDO. El método query devuelve un statement el cual tiene métodos como fetchAll, que trae un arreglo con los resultados de la base de datos.
La constante PDO::FETCH_OBJ es para traer los datos como objetos y acceder, por ejemplo, a $mascota->nombre.
Con arreglo
<?php
/*
CRUD con SQL Server y PHP
@author parzibyte [https://parzibyte.me/blog/]
@date 2019-06-03
================================
Este archivo lista todos los
datos de la tabla, obteniendo a
los mismos como un arreglo
================================
*/
?>
<?php
include_once "base_de_datos.php";
$sentencia = $base_de_datos->query("select id, nombre, edad from mascotas");
$mascotas = $sentencia->fetchAll(PDO::FETCH_OBJ);
?>
La ventaja de usar un arreglo es que podemos codificarlo con JSON cuando programamos una API.
Con cursor
Para utilizar un cursor en lugar de un arreglo debemos preparar una consulta con el modo de cursor, llamar a execute e iterar hasta que el cursor ya no pueda ir más allá.
<?php
/*
CRUD con SQL Server y PHP
@author parzibyte [https://parzibyte.me/blog/]
@date 2019-06-03
================================
Este archivo lista todos los
datos de la tabla, pero en un
ciclo usando un cursor, no
a través de un arreglo
(se supone que es más eficiente)
================================
*/
?>
<?php
include_once "base_de_datos.php";
$consulta = "select id, nombre, edad from mascotas";
# Preparar sentencia e indicar que vamos a usar un cursor
$sentencia = $base_de_datos->prepare($consulta, [
PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL,
]);
# Ejecutar sin parámetros
$sentencia->execute();
# Forma de iterar
while ($mascota = $sentencia->fetchObject()){
# Aquí hacer algo con $mascota
}
?>
En ambos casos queda a nuestro criterio cómo se dibujan los elementos. Al final pondré ejemplos igualmente.
Insertar datos
Al insertar datos hay que prevenir inyecciones SQL así que tenemos que preparar la consulta y después llamar a execute.
En la consulta, en lugar de poner los datos, ponemos signos de interrogación como placeholders.
execute recibe un arreglo de los valores (en forma corta), los cuales van a sustituir, en el mismo orden, a los signos de interrogación.
<?php
include_once "base_de_datos.php";
$nombre = "Ejemplo";
$edad = 2;
/*
Al incluir el archivo "base_de_datos.php", todas sus variables están
a nuestra disposición. Por lo que podemos acceder a ellas tal como si hubiéramos
copiado y pegado el código
*/
$sentencia = $base_de_datos->prepare("INSERT INTO mascotas(nombre, edad) VALUES (?, ?);");
$resultado = $sentencia->execute([$nombre, $edad]); # Pasar en el mismo orden de los ?
El método execute devuelve true o false dependiendo del éxito de la ejecución.
Actualizar datos
Esta consulta es muy parecida al de insertar datos, porque igualmente se llama a execute y se pasan los parámetros.
Obviamente los valores pueden venir de cualquier lugar, aquí los pongo hardcodeados para hacer más fácil el ejemplo.
<?php
include_once "base_de_datos.php";
$id = 1; // El id con el que se hace el where
$nombre = "ASD"; // El nuevo nombre
$edad = 2; // La nueva edad
$sentencia = $base_de_datos->prepare("UPDATE mascotas SET nombre = ?, edad = ? WHERE id = ?;");
$resultado = $sentencia->execute([$nombre, $edad, $id]); # Pasar en el mismo orden de los ?
if ($resultado === true) {
# OK
} else {
echo "Algo salió mal. Por favor verifica que la tabla exista, así como el ID del usuario";
}
Obtener un dato
Si queremos acceder únicamente al primer dato llamamos a fetchObject. En este caso, al editar se tiene que recuperar el objeto completo utilizando el ID.
El código se ve así:
<?php
$id = 1;
include_once "base_de_datos.php";
$sentencia = $base_de_datos->prepare("SELECT id, nombre, edad FROM mascotas WHERE id = ?;");
$sentencia->execute([$id]);
$mascota = $sentencia->fetchObject();
if (!$mascota) {
#No existe
echo "¡No existe alguna mascota con ese ID!";
exit();
}
#Si la mascota existe, se ejecuta esta parte del código
# Aquí hacer algo con $mascota...
?>
Eliminar datos
Lo mismo para eliminar, se llama a una consulta con delete. Recuerda que siempre se deben preparar las consultas, no llamarlas concatenando, para evitar inyecciones SQL.
<?php
$id = 1;
include_once "base_de_datos.php";
$sentencia = $base_de_datos->prepare("DELETE FROM mascotas WHERE id = ?;");
$resultado = $sentencia->execute([$id]);
if ($resultado === true) {
#OK
} else {
echo "Algo salió mal";
}
Hasta aquí ya tenemos el CRUD. Es momento de poner todo junto y agregarle interacción del usuario.
Poniendo todo junto
Listar
Para que el usuario interactúe hay que darle formularios y enlaces. El que lista los datos queda así:
<?php
/*
CRUD con SQL Server y PHP
@author parzibyte [https://parzibyte.me/blog/]
@date 2019-06-03
================================
Este archivo lista todos los
datos de la tabla, obteniendo a
los mismos como un arreglo
================================
*/
?>
<?php
include_once "base_de_datos.php";
$sentencia = $base_de_datos->query("select id, nombre, edad from mascotas");
$mascotas = $sentencia->fetchAll(PDO::FETCH_OBJ);
?>
<!--Recordemos que podemos intercambiar HTML y PHP como queramos-->
<?php include_once "encabezado.php" ?>
<div class="row">
<!-- Aquí pon las col-x necesarias, comienza tu contenido, etcétera -->
<div class="col-12">
<h1>Listar con arreglo</h1>
<a href="https://parzibyte.me/blog/" target="_blank">By Parzibyte</a>
<br>
<div class="table-responsive">
<table class="table table-bordered">
<thead class="thead-dark">
<tr>
<th>ID</th>
<th>Nombre</th>
<th>Edad</th>
<th>Editar</th>
<th>Eliminar</th>
</tr>
</thead>
<tbody>
<!--
Atención aquí, sólo esto cambiará
Pd: no ignores las llaves de inicio y cierre {}
-->
<?php foreach($mascotas as $mascota){ ?>
<tr>
<td><?php echo $mascota->id ?></td>
<td><?php echo $mascota->nombre ?></td>
<td><?php echo $mascota->edad ?></td>
<td><a class="btn btn-warning" href="<?php echo "editar.php?id=" . $mascota->id?>">Editar 📝</a></td>
<td><a class="btn btn-danger" href="<?php echo "eliminar.php?id=" . $mascota->id?>">Eliminar 🗑️</a></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
<?php include_once "pie.php" ?>
Con la siguiente vista:
Cada botón es en realidad un enlace que lleva a eliminar o editar. El enlace lleva el id de la mascota.
Insertar
Hay un formulario que envía los datos a insertar.php. El código del formulario queda así:
<?php
/*
CRUD con SQL Server y PHP
@author parzibyte [https://parzibyte.me/blog/]
@date 2019-06-03
================================
Este archivo muestra un formulario que
se envía a insertar.php, el cual guardará
los datos
================================
*/
?>
<?php include_once "encabezado.php" ?>
<div class="row">
<div class="col-12">
<h1>Agregar</h1>
<form action="insertar.php" method="POST">
<div class="form-group">
<label for="nombre">Nombre</label>
<input required name="nombre" type="text" id="nombre" placeholder="Nombre de mascota" class="form-control">
</div>
<div class="form-group">
<label for="edad">Edad</label>
<input required name="edad" type="number" id="edad" placeholder="Edad de mascota" class="form-control">
</div>
<button type="submit" class="btn btn-success">Guardar</button>
<a href="./listar.php" class="btn btn-warning">Ver todas</a>
</form>
</div>
</div>
<?php include_once "pie.php" ?>
No te confundas con las clases, estoy usando Bootstrap pero con un simple formulario sin estilos se puede lograr el mismo objetivo.
Presta atención al action del formulario, así como el method. Y también al atributo name de cada input.
La vista se ve así:
Editar
Para editar se toma el id de la URL, se obtiene una mascota y se rellena el formulario que es una copia del que inserta.
Para tener el id a la mano (ese no lo introduce el usuario) se crea un input de tipo hidden y se pone el id; de esta manera al guardar los datos el id estará presente.
<?php
/*
CRUD con SQL Server y PHP
@author parzibyte [https://parzibyte.me/blog/]
@date 2019-06-03
================================
Este archivo muestra un formulario llenado automáticamente
(a partir del ID pasado por la URL) para editar
================================
*/
if (!isset($_GET["id"])) {
exit();
}
$id = $_GET["id"];
include_once "base_de_datos.php";
$sentencia = $base_de_datos->prepare("SELECT id, nombre, edad FROM mascotas WHERE id = ?;");
$sentencia->execute([$id]);
$mascota = $sentencia->fetchObject();
if (!$mascota) {
#No existe
echo "¡No existe alguna mascota con ese ID!";
exit();
}
#Si la mascota existe, se ejecuta esta parte del código
?>
<?php include_once "encabezado.php"?>
<div class="row">
<div class="col-12">
<h1>Editar</h1>
<form action="guardarDatosEditados.php" method="POST">
<input type="hidden" name="id" value="<?php echo $mascota->id; ?>">
<div class="form-group">
<label for="nombre">Nombre</label>
<input value="<?php echo $mascota->nombre; ?>" required name="nombre" type="text" id="nombre" placeholder="Nombre de mascota" class="form-control">
</div>
<div class="form-group">
<label for="edad">Edad</label>
<input value="<?php echo $mascota->edad; ?>" required name="edad" type="number" id="edad" placeholder="Edad de mascota" class="form-control">
</div>
<button type="submit" class="btn btn-success">Guardar</button>
<a href="./listar.php" class="btn btn-warning">Volver</a>
</form>
</div>
</div>
<?php include_once "pie.php"?>
La vista es esta:
Si miras el código verás que el formulario se envía a guardarDatosEditados.php que se ve así:
<?php
/*
CRUD con SQL Server y PHP
@author parzibyte [https://parzibyte.me/blog/]
@date 2019-06-03
================================
Este archivo guarda los datos del formulario
en donde se editan
================================
*/
?>
<?php
#Salir si alguno de los datos no está presente
if (
!isset($_POST["nombre"]) ||
!isset($_POST["edad"]) ||
!isset($_POST["id"])
) {
exit();
}
#Si todo va bien, se ejecuta esta parte del código...
include_once "base_de_datos.php";
$id = $_POST["id"];
$nombre = $_POST["nombre"];
$edad = $_POST["edad"];
$sentencia = $base_de_datos->prepare("UPDATE mascotas SET nombre = ?, edad = ? WHERE id = ?;");
$resultado = $sentencia->execute([$nombre, $edad, $id]); # Pasar en el mismo orden de los ?
if ($resultado === true) {
header("Location: listar.php");
} else {
echo "Algo salió mal. Por favor verifica que la tabla exista, así como el ID del usuario";
}
Si todo va bien, se redirecciona a listar.
Eliminar
Finalmente está el enlace que elimina. En la vida real no deberías hacer que se elimine algo con un simple enlace, pero esto que hacemos es una simple práctica.
Tomamos el id de la URL y hacemos el Delete. Si todo va bien, redireccionamos.
<?php
/*
CRUD con SQL Server y PHP
@author parzibyte [https://parzibyte.me/blog/]
@date 2019-06-03
================================
Este archivo elimina un dato por ID sin
pedir confirmación. El ID viene de la URL
================================
*/
if (!isset($_GET["id"])) {
exit();
}
$id = $_GET["id"];
include_once "base_de_datos.php";
$sentencia = $base_de_datos->prepare("DELETE FROM mascotas WHERE id = ?;");
$resultado = $sentencia->execute([$id]);
if ($resultado === true) {
header("Location: listar.php");
} else {
echo "Algo salió mal";
}
Conclusión
Así es como se realizan las 4 operaciones básicas con PHP y SQL Server usando PDO. Al inicio lo complicado es configurar la extensión y el driver; lo demás es puro código de PHP.
Si no sabes cómo se conectan algunas cosas o quieres ver todo el código, mira el repositorio en GitHub.