Price Updates

Overview

The price updates system automatically tracks all product price changes, creating a complete audit trail. When a product price is modified through the edit form, the system records the old and new prices, who made the change, and when it occurred.

Price tracking is automatic and transparent - no separate interface is needed. Updates are logged when products are edited via editar.php.

Database Structure

CREATE TABLE `actualizacion` (
  `id_actualizacion` int(11) NOT NULL,
  `fecha` datetime DEFAULT current_timestamp(),
  `precio_anterior` decimal(10,2) NOT NULL,
  `precio_nuevo` decimal(10,2) NOT NULL,
  `id_prenda` int(11) NOT NULL,
  `id_empleado` int(11) NOT NULL
);

Foreign Key Relationships

ALTER TABLE `actualizacion`
  ADD CONSTRAINT `fk_act_prenda` FOREIGN KEY (`id_prenda`) 
      REFERENCES `prenda` (`id_prenda`),
  ADD CONSTRAINT `fk_act_emp` FOREIGN KEY (`id_empleado`) 
      REFERENCES `empleado` (`id_empleado`);

Product Link

id_prenda links to the product being updated

Employee Link

id_empleado tracks who made the price change

Automatic Price Tracking

Price updates are captured during the product edit workflow in editar.php:

Edit Form Process

Load Current Product Data

The edit form retrieves the current product information, including the current price:

$stmt = $conn->prepare("SELECT * FROM prenda WHERE id_prenda = ?");
$stmt->execute([$id]);
$prenda = $stmt->fetch(PDO::FETCH_ASSOC);

User Modifies Price

The form displays the current price, which the user can modify:

<input type="number" step="0.01" name="precio" 
       value="<?= $prenda['precio'] ?>" required>
<small class="text-muted">
  Si cambias este valor, se generará un registro histórico.
</small>

Employee Selection Required

The form requires selecting the employee making the change:

<select name="id_empleado" class="form-select border-danger" required>
    <option value="">-- Seleccione su nombre --</option>
    <?php
    $res = $conn->query("SELECT id_empleado, nombre FROM empleado");
    while($row = $res->fetch()) {
        echo "<option value='{$row['id_empleado']}'>{$row['nombre']}</option>";
    }
    ?>
</select>

Price Change Detection

On submit, the system compares old and new prices:

$nuevo_precio = $_POST['precio'];
$precio_anterior = $prenda['precio'];

Transaction-Based Update

The edit process uses database transactions for data integrity:

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $nuevo_precio = $_POST['precio'];
    $precio_anterior = $prenda['precio'];
    $id_empleado = $_POST['id_empleado'];

    try {
        $conn->beginTransaction();

        // If price changed, log it to actualizacion table
        if ($nuevo_precio != $precio_anterior) {
            $sql_act = "INSERT INTO actualizacion 
                        (precio_anterior, precio_nuevo, id_prenda, id_empleado) 
                        VALUES (?, ?, ?, ?)";
            $stmt_act = $conn->prepare($sql_act);
            $stmt_act->execute([
                $precio_anterior, 
                $nuevo_precio, 
                $id, 
                $id_empleado
            ]);
        }

        // Update the product with new data
        $sql_upd = "UPDATE prenda 
                    SET nombre=?, precio=?, stock_actual=?, 
                        id_categoria=?, id_talla=?, id_color=? 
                    WHERE id_prenda=?";
        $conn->prepare($sql_upd)->execute([
            $_POST['nombre'], $nuevo_precio, $_POST['stock'], 
            $_POST['id_categoria'], $_POST['id_talla'], 
            $_POST['id_color'], $id
        ]);

        $conn->commit();
        header("Location: index.php");
    } catch (Exception $e) {
        $conn->rollBack();
        echo "Error: " . $e->getMessage();
    }
}

The transaction ensures that both the product update and price history record are saved atomically. If either operation fails, both are rolled back.

Price Change Detection Logic

if ($nuevo_precio != $precio_anterior) {
    // Only log if price actually changed
    // Prevents duplicate records when other fields are updated
}

Price Changed
Price Unchanged

When the price is modified:

Insert record into actualizacion table
Update prenda table with new price
Commit both changes together

When only other fields are modified:

Skip actualizacion insert
Update prenda table only
No price history record created

Price History View

The actualizaciones.php page displays all price changes:

History Query

$sql = "SELECT a.*, p.nombre as p_nom, e.nombre as e_nom 
        FROM actualizacion a 
        JOIN prenda p ON a.id_prenda = p.id_prenda 
        JOIN empleado e ON a.id_empleado = e.id_empleado 
        ORDER BY fecha DESC";

This query:

Joins with prenda to show product names
Joins with empleado to show who made the change
Orders by date descending (newest first)

Table Display

<table class="table table-bordered bg-white shadow-sm mt-3">
    <thead class="table-warning">
        <tr>
            <th>Fecha</th>
            <th>Prenda</th>
            <th>Precio Anterior</th>
            <th>Precio Nuevo</th>
            <th>Empleado</th>
        </tr>
    </thead>
    <tbody>
        <?php
        $res = $conn->query($sql);
        while($row = $res->fetch()){
            echo "<tr>
                    <td>{$row['fecha']}</td>
                    <td>{$row['p_nom']}</td>
                    <td><s>\${$row['precio_anterior']}</s></td>
                    <td class='text-success fw-bold'>\${$row['precio_nuevo']}</td>
                    <td>{$row['e_nom']}</td>
                  </tr>";
        }
        ?>
    </tbody>
</table>

Visual Design Elements

Old Price

Previous price shown with strikethrough styling (<s> tag)

New Price

New price displayed in bold green (text-success fw-bold)

Warning Header

Table header uses yellow (table-warning) to highlight audit importance

Example Price History Data

Real examples from the system:

INSERT INTO `actualizacion` VALUES
(1, '2026-02-07 20:24:25', 420.00, 450.00, 1, 1),
(2, '2026-02-07 20:24:25', 600.00, 650.50, 2, 4),
(3, '2026-02-07 20:24:25', 1100.00, 1200.00, 3, 1),
(4, '2026-02-07 20:24:25', 320.00, 350.00, 4, 4),
(5, '2026-02-07 20:24:25', 380.00, 400.00, 5, 1),
(6, '2026-02-18 21:55:06', 1200.00, 1350.00, 3, 1);

Price Change Analysis

Update #1
Update #2
Update #6

Camisa Oxford Slim (Product #1)

Old: $420.00
New: $450.00
Change: +$30.00 (+7.1%)
By: Karina Sánchez (Employee #1)

Employee Accountability

The system requires employee identification for every price change:

Required Employee Selection

<div class="mb-3">
    <label class="form-label text-danger fw-bold">
        Empleado que realiza la modificación *
    </label>
    <select name="id_empleado" class="form-select border-danger" required>
        <option value="">-- Seleccione su nombre --</option>
        <!-- Employee options populated from database -->
    </select>
</div>

The employee field is required and highlighted in red to emphasize its importance for audit compliance.

Workflow Integration

Price tracking integrates seamlessly with the product edit workflow:

Navigate to Edit

Click “Editar” button in inventory table → redirects to editar.php?id=X

Load Current Data

Form pre-fills with current product data including price

Modify Price

User changes price in the precio field (step 0.01 for cents)

Select Employee

Required dropdown selection of employee making the change

Submit Changes

Click “Actualizar y Guardar Historial” button

Automatic Logging

System detects price change and creates audit record

Transaction Commit

Both product update and price history saved atomically

Redirect

Returns to inventory view with updated price displayed

Audit Trail Benefits

Price History

Complete record of all price changes over time

Employee Tracking

Know exactly who authorized each price change

Trend Analysis

Analyze pricing patterns and frequency of changes

Compliance

Meet audit requirements with automatic documentation

Decimal Precision

Prices use DECIMAL(10,2) format:

`precio_anterior` decimal(10,2) NOT NULL,
`precio_nuevo` decimal(10,2) NOT NULL,

10 digits total: Maximum price is 99,999,999.99
2 decimal places: Cent-level precision
Fixed-point: No floating-point rounding errors

The HTML form uses step="0.01" to allow cent-level input matching the database precision.

Transaction Safety

The BEGIN TRANSACTION / COMMIT / ROLLBACK pattern ensures:

Success Case
Failure Case

Both operations succeed:

$conn->beginTransaction();
// Insert into actualizacion ✓
// Update prenda ✓
$conn->commit(); // Both saved

Either operation fails:

$conn->beginTransaction();
// Insert into actualizacion ✓
// Update prenda ✗ (error)
$conn->rollBack(); // Both cancelled

Without transactions, a failure after the price history insert but before the product update would leave inconsistent data.

Best Practices

Document Changes

Train staff to note reasons for price changes in external documentation

Approval Process

Establish which employees (gerente vs empleado) can modify prices

Regular Reviews

Review price history monthly to identify unusual patterns

Competitor Analysis

Use historical data to track competitive positioning over time

Inventory Management

Edit products through the main inventory interface

Stock Movements

Track inventory changes with similar employee accountability

Get Started

Core Features

User Guide

Overview

Database Structure

Foreign Key Relationships

Product Link

Employee Link

Automatic Price Tracking

Edit Form Process

Transaction-Based Update

Price Change Detection Logic

Price History View

History Query

Table Display

Visual Design Elements

Old Price

New Price

Warning Header

Example Price History Data

Price Change Analysis

Employee Accountability

Required Employee Selection

Workflow Integration

Audit Trail Benefits

Price History

Employee Tracking

Trend Analysis

Compliance

Decimal Precision

Transaction Safety

Best Practices

Document Changes

Approval Process

Regular Reviews

Competitor Analysis

Inventory Management

Stock Movements

Build docs developers (and LLMs) love

Get Started

Core Features

User Guide

​Overview

​Database Structure

​Foreign Key Relationships

Product Link

Employee Link

​Automatic Price Tracking

​Edit Form Process

​Transaction-Based Update

​Price Change Detection Logic

​Price History View

​History Query

​Table Display

​Visual Design Elements

Old Price

New Price

Warning Header

​Example Price History Data

​Price Change Analysis

​Employee Accountability

​Required Employee Selection

​Workflow Integration

​Audit Trail Benefits

Price History

Employee Tracking

Trend Analysis

Compliance

​Decimal Precision

​Transaction Safety

​Best Practices

Document Changes

Approval Process

Regular Reviews

Competitor Analysis

​Related Features

Inventory Management

Stock Movements

Build docs developers (and LLMs) love

Overview

Database Structure

Foreign Key Relationships

Automatic Price Tracking

Edit Form Process

Transaction-Based Update

Price Change Detection Logic

Price History View

History Query

Table Display

Visual Design Elements

Example Price History Data

Price Change Analysis

Employee Accountability

Required Employee Selection

Workflow Integration

Audit Trail Benefits

Decimal Precision

Transaction Safety

Best Practices

Related Features