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
ci-version-3-4