Skip to main content

​
Database Overview

RALQ uses MySQL for persistent data storage, primarily handling user authentication and session management.

​
Database Configuration

​
Connection Details

Database connection parameters are defined in php/validar.php: 
<?php
$servername = "localhost";
$username = "root";
$password = "1234";
$dbname = "registro_RALQ";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Conexión fallida: " . $conn->connect_error);
}
?>
​
servername
string
default:"localhost"
MySQL server hostname
​
username
string
default:"root"
Database user account
​
password
string
default:"1234"
Database user password
​
dbname
string
default:"registro_RALQ"
Target database name
Security Notice: The default credentials shown above are for development only. Change these values for production deployment:
  • Use a dedicated database user (not root)
  • Set a strong, unique password
  • Restrict database user privileges to only required operations
  • Store credentials in environment variables, not in code

​
Database Schema

​
usuarios Table

The primary table for user authentication and profile information. Inferred Schema (based on code analysis): 
CREATE TABLE usuarios (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

​
Column Definitions

​
id
INT
Primary key, auto-incrementing unique identifier for each user
​
nombre
VARCHAR(100)
User’s first name(s)Validation: Required, cannot be empty (validated in alerta-registro.php:13)
​
apellido_paterno
VARCHAR(100)
User’s paternal last nameValidation: Required field
​
apellido_materno
VARCHAR(100)
User’s maternal last nameValidation: Required field
​
email
VARCHAR(255)
User’s email address, used as login usernameConstraints:
  • UNIQUE (prevents duplicate registrations)
  • Must be valid email format
  • Checked for duplicates before insertion (alerta-registro.php:27-36)
​
password
VARCHAR(255)
Hashed password using PHP’s password_hash() with PASSWORD_DEFAULT algorithmSecurity:
  • Never stored in plain text
  • Uses bcrypt hashing by default
  • Verified with password_verify() during login
​
created_at
TIMESTAMP
Timestamp of user registration (optional, recommended for auditing)

​
Database Operations

​
User Registration

Implemented in php/alerta-registro.php: 
// Check if email already exists
$checkStmt = $conn->prepare("SELECT id FROM usuarios WHERE email = ?");
$checkStmt->bind_param("s", $email);
$checkStmt->execute();
$checkStmt->store_result();

if ($checkStmt->num_rows > 0) {
    $_SESSION['errors']['email'] = 'El correo ya está registrado';
    header('Location: ../registro.php');
    exit();
}
$checkStmt->close();

​
User Authentication

Implemented in php/log.php: 
// Retrieve user by email
$stmt = $conn->prepare("SELECT id, email, password FROM usuarios WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$stmt->store_result();

if ($stmt->num_rows > 0) {
    $stmt->bind_result($user_id, $user_email, $hashed_password);
    $stmt->fetch();
    
    // Verify password
    if (password_verify($password, $hashed_password)) {
        // Successful login
        $_SESSION['user_id'] = $user_id;
        $_SESSION['user_email'] = $user_email;
        header("Location: ../menu.php");
        exit;
    } else {
        $_SESSION['errors']['password'] = "Contraseña incorrecta.";
        header("Location: ../iniciosesion.php");
        exit;
    }
} else {
    $_SESSION['errors']['email'] = "El correo no está registrado.";
    header("Location: ../iniciosesion.php");
    exit;
}

​
Session Management

​
Session Storage

PHP sessions are used to maintain user authentication state: 
session_start(); // Called at the start of every page

​
Session Variables

​
$_SESSION['user_id']
int
Unique user identifier from the databaseSet on: Successful login (log.php:48)
​
$_SESSION['user_email']
string
User’s email address, displayed in the UIUsage: Shown in the user menu on menu.php:47
<p><strong><?php echo $_SESSION['user_email']; ?></strong></p>
​
$_SESSION['errors']
array
Temporary error messages for form validationCleared after: Display on form pages (registro.php:155, iniciosesion.php:73)
​
$_SESSION['post_data']
array
Form data preserved after validation errors (optional)

​
Session Lifecycle

​
Logout Implementation

Session termination in php/logout.php: 
<?php
session_start();
session_unset();
session_destroy();
header('Location: ../index.php');
exit();
?>
Session Security: Consider implementing:
  • Session regeneration after login (session_regenerate_id(true))
  • Session timeout (e.g., 30 minutes of inactivity)
  • Secure cookie flags (httponly, secure, samesite)

​
Database Setup

​
Initial Setup Instructions

1

Create Database

CREATE DATABASE registro_RALQ CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
2

Create usuarios Table

USE registro_RALQ;

CREATE TABLE usuarios (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3

Create Database User (Production)

-- Create dedicated user
CREATE USER 'ralq_user'@'localhost' IDENTIFIED BY 'strong_password_here';

-- Grant minimal required privileges
GRANT SELECT, INSERT, UPDATE ON registro_RALQ.usuarios TO 'ralq_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;
4

Update Connection Configuration

Edit php/validar.php with production credentials:
$servername = "localhost";
$username = "ralq_user";
$password = "strong_password_here";
$dbname = "registro_RALQ";

​
Docker Setup

If using Docker Compose with MySQL:
docker-compose.yml
version: '3.8'

services:
  web:
    build: .
    ports:
      - "80:80"
    depends_on:
      - db
    environment:
      DB_HOST: db
      DB_USER: ralq_user
      DB_PASS: ralq_password
      DB_NAME: registro_RALQ

  db:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: registro_RALQ
      MYSQL_USER: ralq_user
      MYSQL_PASSWORD: ralq_password
    volumes:
      - mysql_data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

volumes:
  mysql_data:

​
Backup and Maintenance

​
Database Backup

# Export all data
mysqldump -u root -p registro_RALQ > backup_$(date +%Y%m%d).sql

# Export only usuarios table
mysqldump -u root -p registro_RALQ usuarios > usuarios_backup.sql

​
Database Restore

# Restore from backup
mysql -u root -p registro_RALQ < backup_20260305.sql

​
Maintenance Queries

-- Count total users
SELECT COUNT(*) as total_users FROM usuarios;

-- Recent registrations
SELECT nombre, apellido_paterno, email, created_at 
FROM usuarios 
ORDER BY created_at DESC 
LIMIT 10;

​
Connection Pooling

The current implementation creates a new database connection for each request. For high-traffic applications, consider implementing connection pooling or using persistent connections:
// Use persistent connection
$conn = new mysqli('p:' . $servername, $username, $password, $dbname);

​
Error Handling

​
Connection Errors

From php/validar.php:11-13: 
if ($conn->connect_error) {
    die("Conexión fallida: " . $conn->connect_error);
}
Production Security: Never display database errors directly to users. Log errors and show generic messages:
if ($conn->connect_error) {
    error_log("Database connection failed: " . $conn->connect_error);
    die("Service temporarily unavailable. Please try again later.");
}

​
Query Errors

From alerta-registro.php:47-50: 
if ($stmt->execute()) {
    header('Location: ../index.php');
    exit();
} else {
    $_SESSION['errors']['general'] = 'Error al registrar. Intenta más tarde.';
    header('Location: ../registro.php');
    exit();
}

​
Future Enhancements

Consider these database improvements:
  1. Additional Tables: User preferences, learning progress, favorite models
  2. Audit Logging: Track login attempts, user activity
  3. Password Reset: Add password_reset_token and reset_expires columns
  4. User Roles: Add role column (student, teacher, admin)
  5. Migration System: Use tools like Phinx or custom migration scripts
  6. Full-text Search: Add FULLTEXT indexes for searching content

Build docs developers (and LLMs) love

Get started for freeTalk to us