How to Make an Awesome Inventory Management Application in PHP and MySQL

Are you looking to build a powerful inventory management system without the hefty price tag of enterprise software? With some basic PHP and MySQL skills, you can create your own custom web-based application to efficiently track inventory, purchases, sales orders, and more.

In this step-by-step guide, we‘ll walk through the process of developing a feature-rich inventory manager from scratch using open source tools. By the end, you‘ll have a solid foundation to implement your own inventory solution tailored to your specific business needs.

System Requirements

To follow along with this tutorial, you‘ll need:

• PHP 7.x+
• MySQL 5.6+ / MariaDB 10.0+
• Web server (Apache, Nginx, etc.)
• phpGrid (Lite or Enterprise)
• phpChart

Using components like phpGrid and phpChart allows us to quickly implement advanced functionality like master-detail CRUD interfaces and dynamic charts without having to write everything from the ground up. We‘ll be utilizing the commercial versions for their added features, but the free Lite editions can also work with some limitations.

Designing the Database Schema

At the core of any inventory management system is the database. We‘ll need tables to store information on:

• Products
• Incoming Purchases/Shipments
• Outgoing Sales Orders
• Suppliers

Here‘s a simplified schema design to get us started:

CREATE TABLE `products` (
  `product_id` int AUTO_INCREMENT PRIMARY KEY,
  `sku` varchar(50),
  `name` varchar(255),
  `description` text,
  `unit_price` decimal(10,2),
  `qty_on_hand` int,
  `qty_committed` int,
  `qty_available` int,
  `min_qty` int
);

CREATE TABLE `purchases` (
  `purchase_id` int AUTO_INCREMENT PRIMARY KEY, 
  `product_id` int,
  `supplier_id` int,
  `purchase_date` datetime,
  `qty_purchased` int,
  `unit_cost` decimal(10,2),
  FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`),
  FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`supplier_id`)  
);

CREATE TABLE `orders` (
  `order_id` int AUTO_INCREMENT PRIMARY KEY,
  `order_date` datetime,
  `customer_name` varchar(255),
  `total_amount` decimal(10,2),
  `status` varchar(50)
);

CREATE TABLE `order_items` (
  `order_item_id` int AUTO_INCREMENT PRIMARY KEY,
  `order_id` int,
  `product_id` int,
  `qty_ordered` int,
  `unit_price` decimal(10,2),
  FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`),
  FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`)
);

CREATE TABLE `suppliers` (
  `supplier_id` int AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255),
  `contact` varchar(255),
  `phone` varchar(50),
  `email` varchar(100)  
);

This gives us the basic relations we need while avoiding redundant data. The products table maintains a current inventory count via the qty_on_hand field which gets incremented by incoming purchases and decremented by outgoing orders.

Additional fields can be added as needed, but this provides us a starting point. Be sure to modify the data types, constraints, and relations to fit your particular requirements.

Implementing the User Interface

With our database in place, we can start building out the front-end pages that will allow users to view and manage the inventory data. Let‘s break it down into the core sections:

Current Inventory

The main inventory screen provides an overview of all products and their current quantities on hand. We‘ll utilize phpGrid to quickly generate a paginated, sortable, and searchable grid pulled from our products table.

<?php
// products.php

require_once("phpGrid/conf.php");
use phpGrid\C_DataGrid;

$db = new C_DataGrid("SELECT * FROM products", "product_id", "products");
$db->enable_edit();
$db->display();
?>

This gives us a CRUD interface out of the box with the ability to view/add/edit/delete products on the fly. To provide more insight into the movement of each product, we can enhance the grid by adding a couple detail views.

First, let‘s create a sub-grid to display all related purchase orders for a given product when expanding a row:

$purchase_grid = new C_DataGrid("SELECT * FROM purchases", "purchase_id", "purchases");
$purchase_grid->set_col_hidden("product_id");
$purchase_grid->set_sortname("purchase_date", "desc"); 
$purchase_grid->enable_edit();

$db->set_masterdetail($purchase_grid, "product_id");

And we‘ll do the same for sales orders, showing all outgoing order items tied to the product:

$order_item_grid = new C_DataGrid("SELECT oi.*, o.order_date, o.status 
                                   FROM order_items oi
                                   JOIN orders o ON oi.order_id = o.order_id", "order_item_id", "order_items");
$order_item_grid->set_col_hidden("product_id");                                   
$order_item_grid->set_sortname("order_date", "desc");
$order_item_grid->enable_edit();

$db->add_detail_grid($order_item_grid, "product_id");

Now we have a consolidated view letting us easily see how purchases are increasing inventory and orders are depleting it over time for each specific product. This is incredibly useful for demand planning and forecasting.

Some additional features we could implement:

• Conditional formatting to highlight low stock levels
• Ability to set custom thresholds/reorder points per product
• Dedicated pages for managing purchases and sales orders
• Barcode scanning integration for faster receiving/picking workflows

Incoming Purchases

While the inventory page provides a way to see all incoming product purchases, it‘s helpful to have a dedicated interface for receiving new inventory and managing purchase orders.

We can reuse the same purchases grid from before, but this time grouping and summing by product_id to get a collapsible view of incoming inventory.

<?php 
// purchases.php

require_once("phpGrid/conf.php");
use phpGrid\C_DataGrid;

$db = new C_DataGrid("SELECT p.*, pr.name FROM purchases p 
                      JOIN products pr ON p.product_id = pr.product_id", "purchase_id", "purchases");
$db->set_query_group("p.product_id");
$db->set_group_summary(array(
    "product_id" => array("unique"),
    "name" => array("unique"),
    "qty_purchased" => array("sum"),
    "unit_cost" => array("avg")
    )
);  
$db->enable_edit();
$db->display();
?>

This allows us to quickly see how much of each product we have on order and drill down into the individual purchase orders as needed. When new inventory arrives, the quantities can be marked as received against the PO which will then update the master on hand values.

Outgoing Orders

On the sales side, we‘ll want to track all outgoing orders and easily pick, pack, and ship the items that have been purchased.

By joining the order_items and orders tables, we can generate a master-detail view displaying each order with its associated line items:

<?php
// orders.php

require_once("phpGrid/conf.php");
use phpGrid\C_DataGrid;

// Order Items grid
$order_items_grid = new C_DataGrid("SELECT * FROM order_items", "order_item_id", "order_items");  
$order_items_grid->set_query_group("order_id");
$order_items_grid->set_group_summary(array(
    "order_id" => array("unique"),
    "qty_ordered" => array("sum"),
    "unit_price" => array("sum")
    )
);
$order_items_grid->enable_edit();  

// Orders grid  
$orders_grid = new C_DataGrid("SELECT * FROM orders", "order_id", "orders");
$orders_grid->set_masterdetail($order_items_grid, "order_id");
$orders_grid->enable_edit();
$orders_grid->display();
?>

With this setup, we can manage the statuses of orders as they move through the fulfillment process and have clear visibility into what needs to be shipped out.

Potential enhancements include:

• Automatically allocating inventory to orders based on timestamps
• Generating packing slips and shipping labels
• Integrating with carriers for real-time shipping costs and tracking info
• Barcode scanning for faster picking/shipping workflows

Reporting & Analytics

The real power of having all this inventory data in a structured format is the ability to slice and dice it to gain valuable insights. By integrating phpChart with our phpGrid backend, we can create dynamic dashboards visualizing key metrics.

Some ideas for chart widgets:

• Current inventory levels by product category
• Incoming vs. outgoing unit quantities over time
• Average fulfillment times by product
• Sales and profit by product

For example, here‘s how we could generate an interactive pie chart displaying the breakdown of current inventory values using SQL aggregates:

<?php
// index.php

require_once("phpChart/conf.php");
use phpChart\C_PhpChartX;

$sql = "SELECT p.name, SUM(p.qty_on_hand * p.unit_price) AS inventory_value 
        FROM products p
        GROUP BY p.name";

$pie = new C_PhpChartX([], "PieChart");  
$pie->set_title(array(‘text‘ => ‘Current Inventory Value by Product‘));
$pie->set_series_default(array(
    ‘renderer‘ => ‘PieRenderer‘, 
    ‘rendererOptions‘ => array(
        ‘dataLabels‘ => ‘value‘,
        ‘showDataLabels‘ => true
    )
));

$pie->set_sql_source($sql, $db_conf);
$pie->draw(1000, 500);

We could have this chart update dynamically as the underlying data changes, giving us an always-up-to-date view of where our inventory value lies.

With a reporting foundation in place, the possibilities are endless for the types of insights we can surface. The key is to focus on the metrics that matter most to the business and will drive smarter inventory decisions.

Conclusion

Building an inventory management system from scratch may seem like a daunting task, but by leveraging powerful open source tools and following a modular approach, we can rapidly piece together a sophisticated solution.

The combination of PHP, MySQL, phpGrid, and phpChart provides a robust framework to handle the basic needs of most small-to-medium businesses while offering the flexibility to customize and extend functionality as requirements evolve.

Whether you‘re running an e-commerce shop, a brick-and-mortar store, or a complex supply chain operation, the fundamental concepts outlined in this guide can help you take control of your inventory management process and streamline workflows.

By investing in a modern, web-based inventory management application, you‘ll be able to:

• Maintain accurate stock counts across all products and locations
• Automate purchase orders and reorder points to prevent stockouts
• Optimize picking/packing/shipping to meet customer demand
• Identify trends to make smarter purchasing and pricing decisions
• Cut operational costs by eliminating manual/paper processes
• Gain a real-time view of the entire business

The sample code provided is just a starting point — feel free to adapt it to fit your own unique needs and continue enhancing the system as your business scales.

With a solid inventory management application as the backbone of your operations, you‘ll be well equipped to tackle the challenges of today‘s fast-paced, hyper-competitive environment. Now go forth and conquer the world, one SKU at a time!