Building a Customer Application Management System Using PHP, MySQL, and DataTables.

Managing customer applications efficiently is crucial for any organization dealing with client data. In this project, we created a full-stack solution using PHP, MySQL, and DataTables. The system allows users to upload Excel files, store them in a database, and view them interactively in a web interface with pagination, search, and sorting.

This blog post will explain how we built it, the challenges we faced, and how we solved them.

 

1️⃣ Project Overview

• Tech Stack:
• Backend: PHP
• Database: MySQL
• Frontend Table: jQuery DataTables
• Server: XAMPP (Local Development)
• Excel Handling: PhpSpreadsheet

Key Features:

1. Excel file upload to import customer applications

2. MySQL database storage with auto-increment entry_id

3. Interactive DataTables table with server-side processing

4. Smooth pagination, sorting, and global search

5. Horizontal scrolling for wide tables

 

2️⃣ Database Setup

We created a MySQL database named excel_db and a table customer_applications with columns such as:

• entry_id (primary key, auto-increment)
• own_or_rent_property
• product_inquiry_for
• customer_name
• customer_phone
• customer_email
• customer_home_address
• delivery_address
• employer_name, landlord_name, co_renter_name … and more

 

 

3️⃣ File Upload (import.php)

We implemented a file upload form in list.php:

<form action=”import.php” method=”POST” enctype=”multipart/form-data”>
<input type=”file” name=”excel_file” required>
<button type=”submit” name=”import”>Upload Excel</button>
</form>

How Excel is processed:

• PHP library PhpSpreadsheet reads the uploaded Excel file.
• Convert each row into an array.
• Insert into MySQL using prepared statements:

$stmt = $conn->prepare(“INSERT INTO customer_applications (own_or_rent_property, product_inquiry_for, …) VALUES (?, ?, …)”);
$stmt->bind_param(“ss…”, $row[0], $row[1], …);
$stmt->execute();

Key Point: entry_id auto-increments automatically; we don’t insert it manually.

 

4️⃣ Display Table with DataTables (list.php)

• We used DataTables plugin for the table.
• Server-side processing was enabled for large datasets:

$(‘#table’).DataTable({
processing: true,
serverSide: true,
ajax: {
url: “fetch.php”,
type: “POST”
},
scrollX: true,
pageLength: 10,
deferRender: true
});

Why server-side?

• Efficient handling of large datasets
• Pagination, search, sorting all handled via AJAX requests

 

 

5️⃣ Server-Side Data Fetching (fetch.php)

• Receives AJAX requests from DataTables.
• Handles search, pagination, sorting.

$start = intval($_POST[‘start’] ?? 0);
$length = intval($_POST[‘length’] ?? 10);
$search = $_POST[‘search’][‘value’] ?? “”;

$sql = “SELECT * FROM customer_applications”;
if (!empty($search)) {
$sql .= ” WHERE customer_name LIKE ‘%$search%’ OR customer_email LIKE ‘%$search%’ …”;
}

$totalQuery = $conn->query(“SELECT COUNT(*) AS total FROM customer_applications”);
$totalRecords = $totalQuery->fetch_assoc()[‘total’];

$filteredQuery = $conn->query($sql);
$totalFiltered = $filteredQuery->num_rows;

$sql .= ” LIMIT $start, $length”;
$dataQuery = $conn->query($sql);

$data = [];
while ($row = $dataQuery->fetch_assoc()) {
$data[] = [
$row[‘entry_id’],
$row[‘own_or_rent_property’],
$row[‘product_inquiry_for’],
$row[‘customer_name’],
…
];
}

echo json_encode([
“draw” => intval($_POST[‘draw’]),
“recordsTotal” => $totalRecords,
“recordsFiltered” => $totalFiltered,
“data” => $data
]);

Key Points:

• draw parameter must match request
• recordsTotal and recordsFiltered are essential for pagination
• data array contains actual rows

 

6️⃣ Errors We Faced and Their Solutions

a) Entry ID Not Showing Properly

• Problem: Table showed 1,2,3… instead of database entry_id
• Solution: Use $row[‘entry_id’] in fetch.php instead of DataTables default row index

 

b) Invalid JSON Response

• Problem: DataTables threw “Invalid JSON”
• Cause: PHP warnings/notices, wrong column names
• Solution:
• Disable errors for production:
• error_reporting(0);
  ini_set(‘display_errors’, 0);
• Ensure no extra output before json_encode()

 

c) Loading Spinner Stuck

• Problem: Spinner stays visible even after data loads
• Cause: draw value mismatch or JSON incorrect
• Solution:
• Return correct JSON structure
• Use deferRender: true in DataTables
• Optionally hide spinner via CSS:
• .dataTables_processing { display: none !important; }

 

d) Pagination Issue (Server-Side / WordPress Style)

• Problem: Pagination broke, last page rows missing or duplicates
• Cause: $start / $length miscalculation or WordPress paged parameter missing
• Solution:
• $start = intval($_POST[‘start’] ?? 0);
  $length = intval($_POST[‘length’] ?? 10);
  $sql .= ” LIMIT $start, $length”;
• Ensure JSON returns proper recordsTotal and recordsFiltered
• For WordPress, set paged in WP_Query correctly

 

e) Git / GitHub Errors

• Problems:
• remote origin already exists
• repo.git not found
• Fatal error: build checkout not url found
• Solutions:
• git remote remove origin
  git remote add origin https://github.com/yourusername/excel_customer_app.git
  git branch -M main
  git add .
  git commit -m “Initial commit”
  git push -u origin main
• Use PAT for private repositories

 

7️⃣ Lessons Learned

1. Always match DB columns with code (search / fetch queries)

2. Server-side pagination and search are efficient for large datasets

3. PHP notices can silently break AJAX JSON responses

4. GitHub workflow: remote set, branch rename, PAT usage

5. Defer rendering in DataTables improves UX

 

8️⃣ Project Workflow Summary

1. Upload Excel → import.php reads and inserts into DB

2. Database → customer_applications stores all records with unique entry_id

3. Display Table → list.php + DataTables fetch data via AJAX

4. AJAX fetch → fetch.php handles pagination, search, sorting

5. User Interaction → Smooth scroll, search, pagination, horizontal scroll

 

9️⃣ Future Enhancements

Column-wise filters

Edit / Delete rows in table

Export table to Excel / PDF

Authentication and secure login

 

Conclusion

This project demonstrates a complete Excel → PHP → MySQL → DataTables pipeline:

Large datasets are handled efficiently

Users can upload, view, search, and paginate data easily

All common errors are addressed: ID issues, JSON errors, spinner loading, pagination, GitHub issues