CRUD en PHP mejorado

Mejora de la aplicación CRUD en PHP

¿Qué ocurre si falla la conexión a la base de datos?

Mejora: mostrar un error si falla la conexión a la base de datos

uso de IA con Gemini:

modifica el código para mostrar un mensaje de error en index.php cuando falle la conexión a la base de datos

db.php

<?php
session_start();

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
  $conn = mysqli_connect(
    'localhost',
    'crudUser',
    'malaga2526',
    'php_mysql_crud'
  );
} catch (mysqli_sql_exception $e) {
  $_SESSION['db_error'] = $e->getMessage();
  $conn = false;
}

index.php

<?php include("db.php"); ?>

<?php include('includes/header.php'); ?>

<main class="container p-4">
  <div class="row">
    <div class="col-md-12">
      <!-- MESSAGES -->

      <?php if (isset($_SESSION['message'])) { ?>
      <div class="alert alert-<?= $_SESSION['message_type']?> alert-dismissible fade show" role="alert">
        <?= $_SESSION['message']?>
        <button type="button" class="close" data-dismiss="alert" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <?php
        unset($_SESSION['message']);
        unset($_SESSION['message_type']);
      } ?>

      <?php if (isset($_SESSION['db_error'])) { ?>
      
      <div class="alert alert-danger alert-dismissible fade show" role="alert">
        Error de conexión a la base de datos: <?= htmlspecialchars($_SESSION['db_error']) ?>
        <button type="button" class="close" data-dismiss="alert" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <?php unset($_SESSION['db_error']); } ?>
    </div>
  </div>

  <?php if ($conn) { ?>
  <div class="row">
    <div class="col-md-12">
      <a href="insert.php"  class="btn btn-primary mt-4">Crear tarea</a>
      <hr>
    </div>
  </div>

  <div class="row">
    <div class="col-md-12">
      <table class="table table-bordered">
        <thead>
          <tr>
            <th>Title</th>
            <th>Description</th>
            <th>Created At</th>
            <th>Action</th>
          </tr>
        </thead>
        <tbody>

          <?php
          $query = "SELECT * FROM task";
          try {
            $result_tasks = mysqli_query($conn, $query);   
            while ($row = mysqli_fetch_assoc($result_tasks)) { ?>
            <tr>
              <td><?php echo htmlspecialchars($row['title']); ?></td>
              <td><?php echo htmlspecialchars($row['description']); ?></td>
              <td><?php echo htmlspecialchars($row['created_at']); ?></td>
              <td>
                <a href="edit.php?id=<?php echo (int)$row['id']?>" class="btn btn-secondary">
                  <i class="fas fa-marker"></i>
                </a>
                <a href="delete_task.php?id=<?php echo (int)$row['id']?>" class="btn btn-danger">
                  <i class="far fa-trash-alt"></i>
                </a>
              </td>
            </tr>
            <?php } 
            } catch (mysqli_sql_exception $e) {
            $_SESSION['message'] = 'Error fetching tasks: ' . $e->getMessage();
            $_SESSION['message_type'] = 'danger';
          }
          ?>
        </tbody>
      </table>
    </div>
  </div>
  <?php } else if (!isset($_SESSION['db_error'])) { ?>
    <div class="alert alert-warning" role="alert">The database connection is unavailable or the user/password is incorrect. Please check your configuration.</div>
  <?php } ?>
</main>

<?php include('includes/footer.php'); ?>

insert.php

<?php include('db.php'); ?>

<?php include('includes/header.php'); ?>

<main class="container p-4">
  <div class="row">
    <div class="col-md-8">

      <?php if (isset($_SESSION['message'])) { ?>
      <div class="alert alert-<?= $_SESSION['message_type']?> alert-dismissible fade show" role="alert">
        <?= $_SESSION['message']?>
        <button type="button" class="close" data-dismiss="alert" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <?php 
        unset($_SESSION['message']);
        unset($_SESSION['message_type']);
      } ?>

      <?php if (isset($_SESSION['db_error'])) { ?>
      <div class="alert alert-danger alert-dismissible fade show" role="alert">
        Error de conexión a la base de datos: <?= htmlspecialchars($_SESSION['db_error']) ?>
        <button type="button" class="close" data-dismiss="alert" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <?php unset($_SESSION['db_error']); } ?>

      <!-- ADD TASK FORM -->
      <?php if ($conn) { ?>
      <div class="card card-body">
        <form action="save_task.php" method="POST">
          <div class="form-group">
            <input type="text" name="title" class="form-control" placeholder="Task Title" autofocus>
          </div>
          <div class="form-group">
            <textarea name="description" rows="2" class="form-control" placeholder="Task Description"></textarea>
          </div>
          <input type="submit" name="save_task" class="btn btn-success btn-block" value="Save Task">
          <hr>
          <a class="btn btn-secondary btn-block" href="index.php">Cancel</a>
        </form>
      </div>
      <?php } else if (!isset($_SESSION['db_error'])) { ?>
        <div class="alert alert-warning">
          The database connection is unavailable. Please check your configuration.
        </div>
        <a href="index.php" class="btn btn-secondary btn-block">Back to Home</a>
      <?php } ?>
    </div>
  </div>
</main>
<?php include('includes/footer.php'); ?>

save_task.php

<?php

include('db.php');

if (isset($_POST['save_task'])) {
  if (!$conn) {
    $_SESSION['message'] = 'Database connection failed. Cannot save task.';
    $_SESSION['message_type'] = 'danger';
    header('Location: insert.php');
    exit();
  }

  $title = mysqli_real_escape_string($conn, $_POST['title']);
  $description = mysqli_real_escape_string($conn, $_POST['description']);
  $query = "INSERT INTO task(title, description) VALUES ('$title', '$description')";
  try {
    mysqli_query($conn, $query);
    $_SESSION['message'] = 'Task Saved Successfully';
    $_SESSION['message_type'] = 'success';
    header('Location: index.php');
    exit();
  } catch (mysqli_sql_exception $e) {
    $_SESSION['message'] = 'Task Save Failed: ' . $e->getMessage();
    $_SESSION['message_type'] = 'danger';
    header('Location: insert.php');
    exit();
  }
}

?>

edit.php

<?php
include("db.php");
$title = '';
$description= '';

if  (isset($_GET['id'])) {
  $id = $_GET['id'];
  $query = "SELECT * FROM task WHERE id=$id";
  try {
    $result = mysqli_query($conn, $query);
    if (mysqli_num_rows($result) == 1) {
        $row = mysqli_fetch_array($result);
        $title = $row['title'];
        $description = $row['description'];
    } else {
        $_SESSION['message'] = 'Task Not Found';
        $_SESSION['message_type'] = 'danger';
        header('Location: index.php');
        exit();
    }
  } catch (mysqli_sql_exception $e) {
    $_SESSION['message'] = 'Task Not Found: ' . $e->getMessage();
    $_SESSION['message_type'] = 'danger';
    header('Location: index.php');
    exit();
  }

}

if (isset($_POST['update'])) {
  if (!$conn) {
    $_SESSION['message'] = 'Database connection failed. Cannot update task.';
    $_SESSION['message_type'] = 'danger';
    header('Location: index.php');
    exit();
  }

  $id = (int)$_GET['id'];
  $title = mysqli_real_escape_string($conn, $_POST['title']);
  $description = mysqli_real_escape_string($conn, $_POST['description']);
  $query = "UPDATE task SET title = '$title', description = '$description' WHERE id = $id";
  try {
    mysqli_query($conn, $query);
    $_SESSION['message'] = 'Task Updated Successfully';
    $_SESSION['message_type'] = 'success';
    header('Location: index.php');
    exit();
  } catch (mysqli_sql_exception $e) {
    $_SESSION['message'] = 'Task Update Failed: ' . $e->getMessage();
    $_SESSION['message_type'] = 'danger';
  }
}
?>

<?php include('includes/header.php'); ?>
<div class="container p-4">
  <?php if (isset($_SESSION['message'])) { ?>
    <div class="alert alert-<?= $_SESSION['message_type']?> alert-dismissible fade show" role="alert">
      <?= $_SESSION['message']?>
      <button type="button" class="close" data-dismiss="alert" aria-label="Close">
        <span aria-hidden="true">&times;</span>
      </button>
    </div>
    <?php 
      unset($_SESSION['message']);
      unset($_SESSION['message_type']);
  } ?>

  <?php if (isset($_SESSION['db_error'])) { ?>
    <div class="alert alert-danger alert-dismissible fade show" role="alert">
      Error de conexión a la base de datos: <?= htmlspecialchars($_SESSION['db_error']) ?>
      <button type="button" class="close" data-dismiss="alert" aria-label="Close">
        <span aria-hidden="true">&times;</span>
      </button>
    </div>
    <?php 
      unset($_SESSION['message']);
      unset($_SESSION['message_type']);
  } ?>

  <div class="row">
    <div class="col-md-4 mx-auto">
      <div class="card card-body">
      <form action="edit.php?id=<?php echo $_GET['id']; ?>" method="POST">
        <div class="form-group">
          <input name="title" type="text" class="form-control" value="<?php echo htmlspecialchars($title); ?>" placeholder="Update Title">
        </div>
        <div class="form-group">
        <textarea name="description" class="form-control" cols="30" rows="10"><?php echo htmlspecialchars($description);?></textarea>
        </div>
        <input type="submit" class="btn btn-success btn-block" name="update" value="Update">
        <hr>
        <a class="btn btn-secondary btn-block" href="index.php">Cancel</a>
      </form>
      </div>
    </div>
  </div>
</div>
<?php include('includes/footer.php'); ?>

delete_task.php

<?php

include("db.php");

if(isset($_GET['id'])) {
  $id = (int)$_GET['id'];

  // Si no se ha confirmado la eliminación, mostramos un cuadro de diálogo (UI)
  if (!isset($_GET['confirm'])) {
    include('includes/header.php'); ?>
    <div class="container p-4">
      <div class="row">
        <div class="col-md-4 mx-auto">
          <div class="card card-body text-center">
            <h5 class="mb-4">¿Estás seguro de que deseas eliminar esta tarea?</h5>
            <div class="d-flex justify-content-around">
              <a href="delete_task.php?id=<?php echo $id; ?>&confirm=1" class="btn btn-danger">Sí, eliminar</a>
              <a href="index.php" class="btn btn-secondary">Cancelar</a>
            </div>
          </div>
        </div>
      </div>
    </div>
    <?php include('includes/footer.php');
    exit();
  }

  $query = "DELETE FROM task WHERE id = $id";
    try {
    mysqli_query($conn, $query);
    $_SESSION['message'] = 'Task Deleted Successfully';
    $_SESSION['message_type'] = 'success';
  } catch (mysqli_sql_exception $e) {
    $_SESSION['message'] = 'Task Delete Failed: ' . $e->getMessage();
    $_SESSION['message_type'] = 'danger';
  }
  header('Location: index.php');
}

?>

Instalación

descargar el archivo zip del repositorio en GitHub en https://github.com/paco-portada/crud2026

https://github.com/paco-portada/crud2026/archive/refs/heads/master.zip

~~Conectarse por ssh para trabajar en el VPS:~~

ssh usuario@alumno.me

Situarse en la carpeta del servidor web (en Linux es /var/www/html), descomprimir el archivo y cambiar el nombre a la carpeta

cd /var/www/html
sudo wget https://github.com/paco-portada/crud2026/archive/refs/heads/master.zip
sudo unzip master.zip
sudo mv crud2026-master crud2026

crear la base de datos con el script:

crud2026/database/script.sql

CREATE DATABASE php_mysql_crud;

use php_mysql_crud;

CREATE TABLE task (
  id          INT(11)      PRIMARY KEY AUTO_INCREMENT,
  title       VARCHAR(255) NOT NULL CHECK (CHAR_LENGTH(title) > 4),
  description TEXT,
  created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

DESCRIBE task;

el título tiene la restricción de ser superior a 4 caracteres (para que se pueda probar el fallo al realizar un INSERT)

sudo mysql < crud2026/database/script.sql

crear un usuario y contraseña y darle permiso para acceder a la base de datos php-mysql-crud

crudUser2026/malaga2526

sudo mysql
create user crudUser2026@localhost identified by 'malaga2526';
grant all privileges on php_mysql_crud.* to crudUser@localhost with grant option;
flush privileges; 
exit;

modiifcar en el fichero db.php el usuario y contraseña creados

sudo nano crud2026/db.php

fichero db.php

<?php
session_start();

$conn = mysqli_connect(
  'localhost',
  'crudUser2026',
  'malaga2526',
  'php_mysql_crud'
) or die(mysqli_erro($mysqli));

?>

acceder en el navegador a la ruta en el servidor local:

localhost/crud2026/

o en el VPS

alumnoportada.com.es/crud/

Deja una respuesta Cancelar la respuesta

Lo siento, debes estar conectado para publicar un comentario.