Inventory Management System — Timex

Inventory Management System — Timex

Timex required a web-based inventory management system to replace manual stock tracking with spreadsheets. The system was built using PHP and MySQL — covering real-time stock tracking, product management, low-stock alerts, and printable reports. The admin dashboard gives staff full control over inventory without any technical knowledge required.

Project Info

• Client: Timex
• Type: Inventory Management System
• Stack: PHP, MySQL, HTML5, CSS3, JavaScript
• Features: Stock tracking, product CRUD, category management, reports

Step 1 — Planning and Requirements

Requirements were gathered directly from the Timex team before any development began. Core deliverables identified:

• Product listing with stock quantities and categories
• Add, edit, and delete products from the admin panel
• Stock-in and stock-out transaction logging
• Low-stock threshold alerts on the dashboard
• Inventory reports exportable as PDF or printable
• Role-based access — admin and staff user levels

A flowchart of the stock movement logic was produced first to map how stock-in, stock-out, and adjustments would interact with the core product table.

Step 2 — Database Design

The MySQL schema was built around products, categories, transactions, and users. Stock quantity is derived from transaction history rather than a single mutable field — ensuring a full audit trail:

-- Categories
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

-- Products
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  sku VARCHAR(100) UNIQUE NOT NULL,
  category_id INT,
  unit VARCHAR(50),
  low_stock_threshold INT DEFAULT 10,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Stock transactions (in / out / adjustment)
CREATE TABLE stock_transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  type ENUM('in','out','adjustment') NOT NULL,
  quantity INT NOT NULL,
  note TEXT,
  created_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Current stock for any product is calculated by summing all transactions — in adds, out subtracts, adjustment sets an absolute value.

Step 3 — Stock Tracking Logic

A reusable PHP function handles all stock movement and keeps the transaction log consistent across the application:

function updateStock($pdo, $productId, $type, $qty, $note, $userId) {
  $stmt = $pdo->prepare(
    "INSERT INTO stock_transactions
     (product_id, type, quantity, note, created_by)
     VALUES (?, ?, ?, ?, ?)"
  );
  $stmt->execute([$productId, $type, $qty, $note, $userId]);
}

// Get current stock for a product
function getStock($pdo, $productId) {
  $stmt = $pdo->prepare(
    "SELECT
      SUM(CASE WHEN type='in' THEN quantity
               WHEN type='out' THEN -quantity
               WHEN type='adjustment' THEN quantity
          END) AS stock
     FROM stock_transactions
     WHERE product_id = ?"
  );
  $stmt->execute([$productId]);
  return $stmt->fetchColumn() ?? 0;
}

Every stock movement — whether a purchase, sale, or manual adjustment — is recorded through this function, maintaining a complete and tamper-evident audit trail.

Step 4 — Product CRUD (Add / Edit / Delete)

The admin panel provides full product management. All forms use PDO prepared statements to prevent SQL injection:

// Add new product
$stmt = $pdo->prepare(
  "INSERT INTO products
   (name, sku, category_id, unit, low_stock_threshold)
   VALUES (?, ?, ?, ?, ?)"
);
$stmt->execute([$name, $sku, $catId, $unit, $threshold]);

// Edit existing product
$stmt = $pdo->prepare(
  "UPDATE products
   SET name=?, sku=?, category_id=?, unit=?, low_stock_threshold=?
   WHERE id=?"
);
$stmt->execute([$name, $sku, $catId, $unit, $threshold, $id]);

// Soft delete — marks inactive rather than removing
$stmt = $pdo->prepare(
  "UPDATE products SET active=0 WHERE id=?"
);
$stmt->execute([$id]);

Products are soft-deleted rather than permanently removed — preserving historical transaction data and stock history for reporting purposes.

Step 5 — Low Stock Alerts and Dashboard

The dashboard queries all products where current stock is at or below the configured threshold and displays them as alerts at the top of the page:

-- Low stock products view
SELECT p.name, p.sku, p.low_stock_threshold,
  SUM(CASE
    WHEN t.type='in'         THEN t.quantity
    WHEN t.type='out'        THEN -t.quantity
    WHEN t.type='adjustment' THEN t.quantity
    ELSE 0 END) AS current_stock
FROM products p
LEFT JOIN stock_transactions t ON t.product_id = p.id
WHERE p.active = 1
GROUP BY p.id
HAVING current_stock <= p.low_stock_threshold
ORDER BY current_stock ASC;

The dashboard also shows total products, total stock value, recent transactions, and a category-wise stock breakdown at a glance.

Step 6 — Reports

Staff can generate two types of reports from the admin panel — a full inventory snapshot and a transaction history report filtered by date range. Reports are rendered as printable HTML pages and exported to PDF using mPDF:

// Generate PDF report with mPDF
require_once 'vendor/autoload.php';
$mpdf = new \Mpdf\Mpdf();

ob_start();
include 'report_template.php';
$html = ob_get_clean();

$mpdf->WriteHTML($html);
$mpdf->Output('inventory_report.pdf', 'D');

The PDF report includes the Timex logo, generation timestamp, stock quantities per product, and a summary table grouped by category. Date-range filtering is applied via SQL WHERE created_at BETWEEN ? AND ? on the transactions table.

Key Takeaways

• Deriving stock from transaction history gives a full audit trail — never mutate a raw quantity field directly
• Soft deletes preserve historical data — hard deletes break transaction foreign keys and reports
• PDO prepared statements are non-negotiable — all user input must be parameterised
• Low-stock thresholds per product are more useful than a single global threshold
• mPDF integrates cleanly with PHP for generating professional printable reports
• Role-based access ensures staff can log stock movements without accessing sensitive admin functions

Tools Used

• PHP 8 — backend logic, routing, and session management
• MySQL — relational database with PDO prepared statements
• mPDF — PDF report generation
• HTML5 / CSS3 — admin panel UI and responsive layout
• JavaScript — dynamic form validation and dashboard interactions
• Apache — web server with .htaccess routing
• VS Code — development environment