In our previous PHP project tutorial, we have implemented project management system with PHP and MySQL. In this tutorial, we will implement CRM System with PHP and MySQL.
Customer Relationship Management (CRM) is a system to automate and manage the relationship between customers and company. The system covers customers relationship with sales and marketing department and also improve performance and increase productivity.
The CRM systems are always in demand as these are useful and widely used to track the customers and sales records. So if you’re developer and thinking about to develop a CRM system, then you’re here at the right place. In this tutorial you will learn how to develop CRM system with PHP and MySQL.
Also, read:
- User Management System with PHP & MySQL
- Build Helpdesk System with jQuery, PHP & MySQL
- Build Online Voting System with PHP & MySQL
- School Management System with PHP & MySQL
- Project Management System with PHP and MySQL
- Hospital Management System with PHP & MySQL
In this tutorial, we will implement CRM system for sales people to track customers. We will developer Sales Manager section and Sales People section to build the system. We will cover following in this tutorial.
The Sales Manager will be able to do following:
- Manage customers
- Manage sales team
- View sales activities
The Sales People will be able to do following:
- Access tasks
- View leads
- Create new tasks for each lead
- Create new opportunity
- Close a sale
So let’s start developing CRM system with PHP and MySQL. The major files are:
- index.php
- sales_people.php
- tasks.php
- contact.php
- leads.php
- opportunity.php
- User.php: A class contains users methods.
- Leads.php: A class contains leads methods.
- Tasks.php: A class contains tasks methods.
- Opportunity.php: A class contains sales oppotunity methods.
- Customer.php: A class contains customer methods.
Step1: Create MySQL Database Table
First we will create MySQL database tables for our system. The major tables are following.
We will create crm_users table to store users details.
CREATE TABLE `crm_users` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`email` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`roles` enum('manager','sales') NOT NULL,
`status` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
We will create crm_contact table to store conact details.
CREATE TABLE `crm_contact` (
`id` int(11) NOT NULL,
`contact_title` varchar(255) NOT NULL,
`contact_first` varchar(255) NOT NULL,
`contact_middle` varchar(255) NOT NULL,
`contact_last` varchar(255) NOT NULL,
`initial_contact_date` datetime NOT NULL DEFAULT current_timestamp(),
`title` varchar(255) NOT NULL,
`company` varchar(255) NOT NULL,
`industry` varchar(255) NOT NULL,
`address` text NOT NULL,
`city` varchar(255) NOT NULL,
`state` varchar(255) NOT NULL,
`country` varchar(255) NOT NULL,
`zip` int(11) NOT NULL,
`phone` int(11) NOT NULL,
`email` varchar(50) NOT NULL,
`status` enum('Lead','Proposal','Customer / won','Archive') NOT NULL,
`website` varchar(255) NOT NULL,
`sales_rep` int(11) NOT NULL,
`project_type` varchar(255) NOT NULL,
`project_description` text NOT NULL,
`proposal_due_date` varchar(255) NOT NULL,
`budget` int(11) NOT NULL,
`deliverables` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
and we will create crm_tasks table to store tasks details.
CREATE TABLE `crm_tasks` (
`id` int(11) NOT NULL,
`created` datetime NOT NULL DEFAULT current_timestamp(),
`task_type` varchar(255) NOT NULL,
`task_description` text NOT NULL,
`task_due_date` varchar(255) NOT NULL,
`task_status` enum('Pending','Completed') NOT NULL,
`task_update` varchar(255) NOT NULL,
`contact` int(11) NOT NULL,
`sales_rep` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Step2: Implement User Login and Access
We will implement the user login for sales manager and sales people to access related section to manage system. So we will create login form in index.php file.
<form id="loginform" class="form-horizontal" role="form" method="POST" action="">
<div style="margin-bottom: 25px" class="input-group">
<span class="input-group-addon"><i class="glyphicon glyphicon-user"></i></span>
<input type="text" class="form-control" id="email" name="email" value="<?php if(!empty($_POST["email"])) { echo $_POST["email"]; } ?>" placeholder="email" style="background:white;" required>
</div>
<div style="margin-bottom: 25px" class="input-group">
<span class="input-group-addon"><i class="glyphicon glyphicon-lock"></i></span>
<input type="password" class="form-control" id="password" name="password" value="<?php if(!empty($_POST["password"])) { echo $_POST["password"]; } ?>" placeholder="password" required>
</div>
<label class="radio-inline">
<input type="radio" name="loginType" value="manager">Sales Manager
</label>
<label class="radio-inline">
<input type="radio" name="loginType" value="sales">Sales People
</label>
<div style="margin-top:10px" class="form-group">
<div class="col-sm-12 controls">
<input type="submit" name="login" value="Login" class="btn btn-info">
</div>
</div>
</form>
Then we will implement login functionality by calling method login() from class User.php.
public function login(){
if($this->email && $this->password) {
$sqlQuery = "
SELECT * FROM ".$this->userTable."
WHERE status = 1
AND roles = ? AND email = ? AND password = ?";
$stmt = $this->conn->prepare($sqlQuery);
$password = md5($this->password);
$stmt->bind_param("sss", $this->loginType, $this->email, $password);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows > 0){
$user = $result->fetch_assoc();
$_SESSION["userid"] = $user['id'];
$_SESSION["role"] = $this->loginType;
$_SESSION["name"] = $user['email'];
return 1;
} else {
return 0;
}
} else {
return 0;
}
}
Step3: Manage Contacts
The contact is the information about the people we know and work with. Usually, one sales representative has many contacts. So here we will manage the contact. In contact.php file, we will display the list of contact.
<table id="contactListing" class="table table-bordered table-striped"> <thead> <tr> <th>Id</th> <th>Name</th> <th>Company</th> <th>Industry</th> <th>Budget</th> <th>Sales Rep</th> <th></th> <th></th> <th></th> </tr> </thead> </table>
We will make ajax request to load contact list with action listContact to action contact_action.php.
var contactRecords = $('#contactListing').DataTable({
"lengthChange": false,
"processing":true,
"serverSide":true,
"bFilter": false,
'serverMethod': 'post',
"order":[],
"ajax":{
url:"contact_action.php",
type:"POST",
data:{action:'listContact'},
dataType:"json"
},
"columnDefs":[
{
"targets":[0, 6, 7, 8],
"orderable":false,
},
],
"pageLength": 10
});
In contact_action.php on action listContact, we will call method listContact() from class Contact.php.
$contact = new Contact($db);
if(!empty($_POST['action']) && $_POST['action'] == 'listContact') {
$contact->listContact();
}
Now wwe will implement the method listContact() in class Contact.php
public function listContact(){
$sqlWhere = '';
if($_SESSION["role"] == 'sales') {
$sqlWhere = " WHERE c.sales_rep = '".$_SESSION["userid"]."'";
}
$sqlQuery = "SELECT c.id, c.contact_first, c.company, c.industry, c.budget, u.name
FROM ".$this->contactTable." c
LEFT JOIN ".$this->userTable." u ON c.sales_rep = u.id $sqlWhere";
if(!empty($_POST["order"])){
$sqlQuery .= ' ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= ' ORDER BY id ASC ';
}
if($_POST["length"] != -1){
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
$stmtTotal = $this->conn->prepare("SELECT * FROM ".$this->contactTable);
$stmtTotal->execute();
$allResult = $stmtTotal->get_result();
$allRecords = $allResult->num_rows;
$displayRecords = $result->num_rows;
$records = array();
while ($contact = $result->fetch_assoc()) {
$rows = array();
$rows[] = $contact['id'];
$rows[] = ucfirst($contact['contact_first']);
$rows[] = $contact['company'];
$rows[] = $contact['industry'];
$rows[] = $contact['budget'];
$rows[] = $contact['name'];
$rows[] = '<button type="button" name="view" id="'.$contact["id"].'" class="btn btn-info btn-xs view"><span title="View Tasks">View Tasks</span></button>';
$rows[] = '<button type="button" name="update" id="'.$contact["id"].'" class="btn btn-warning btn-xs update"><span class="glyphicon glyphicon-edit" title="Edit"></span></button>';
$rows[] = '<button type="button" name="delete" id="'.$contact["id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete"></span></button>';
$records[] = $rows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"iTotalRecords" => $displayRecords,
"iTotalDisplayRecords" => $allRecords,
"data" => $records
);
echo json_encode($output);
}
we have also implemented contact add, edit and delete functionality.
Step4: Manage Tasks
Now we will manage activities like meetings, phone calls, emails and any other activities that allow us to interact with customers. We will implement to list the tasks details.
So in tasks.php, we will display tasks list.
<table id="tasksListing" class="table table-bordered table-striped"> <thead> <tr> <th>Id</th> <th>Description</th> <th>Due Date</th> <th>Contact</th> <th>Sales Rep</th> <th>Status</th> <th></th> <th></th> </tr> </thead> </table>
We will implement method listTasks() in class Tasks.php
public function listTasks(){
$sqlWhere = '';
if($_SESSION["role"] == 'sales') {
$sqlWhere = " WHERE t.sales_rep = '".$_SESSION["userid"]."'";
}
$sqlQuery = "SELECT t.id, t.created, t.task_type, t.task_description, t.task_due_date, t.task_status, t.task_update, c.contact_first, u.name
FROM ".$this->tasksTable." t
LEFT JOIN ".$this->contactTable." c ON t.contact = c.id
LEFT JOIN ".$this->userTable." u ON t.sales_rep = u.id $sqlWhere";
if(!empty($_POST["order"])){
$sqlQuery .= ' ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= ' ORDER BY id ASC ';
}
if($_POST["length"] != -1){
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
$stmtTotal = $this->conn->prepare("SELECT * FROM ".$this->contactTable);
$stmtTotal->execute();
$allResult = $stmtTotal->get_result();
$allRecords = $allResult->num_rows;
$displayRecords = $result->num_rows;
$records = array();
while ($tasks = $result->fetch_assoc()) {
$rows = array();
$rows[] = $tasks['id'];
$rows[] = $tasks['task_description'];
$rows[] = $tasks['task_due_date'];
$rows[] = $tasks['contact_first'];
$rows[] = $tasks['name'];
$rows[] = $tasks['task_status'];
$rows[] = '<button type="button" name="update" id="'.$tasks["id"].'" class="btn btn-warning btn-xs update"><span class="glyphicon glyphicon-edit" title="Edit"></span></button>';
$rows[] = '<button type="button" name="delete" id="'.$tasks["id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete"></span></button>';
$records[] = $rows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"iTotalRecords" => $displayRecords,
"iTotalDisplayRecords" => $allRecords,
"data" => $records
);
echo json_encode($output);
}
We have also handled tasks add, edit delete functionality.
You may also like:
- User Management System with PHP & MySQL
- Datatables Add Edit Delete with Ajax, PHP & MySQL
- Build Helpdesk System with jQuery, PHP & MySQL
- Build Online Voting System with PHP & MySQL
- School Management System with PHP & MySQL
- DataTables Add Edit Delete with CodeIgniter
- Create RESTful API using CodeIgniter
- Build Reusable Captcha Script with PHP
- Product Search Filtering using Ajax, PHP & MySQL
- Image Upload and Crop in Modal with jQuery, PHP & MySQL
- Build Push Notification System with PHP & MySQL
- Project Management System with PHP and MySQL
- Hospital Management System with PHP & MySQL
- Build Newsletter System with PHP and MySQL
- Skeleton Screen Loading Effect with Ajax and PHP
- Build Discussion Forum with PHP and MySQL
- Online Exam System with PHP & MySQL
- Expense Management System with PHP & MySQL
You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download
i encountered this error “User register request failed.” in user registration… it seems not to work.
please i need help, my user registration seems not to be working.
please alter id field to auto increment, may be that causing issue. thanks!
I setup the source codes and DB in my server. I can successfully run the website and login with the credentials.
I’m able to add records in the Tasks. However, I can’t View notes and Delete. Clicking on the delete button nothing happens.
may be there error in your code, try to debug issue. thanks!
Hey,
thank you so much for your awesome coding work! I have one question: is possible to built in a search box for the tables?
My guess would be to expand the sql where statement and setup an input field, but I dont know how if I need to change anything within the jquery.datatables, too.
I hope you can help me.
Many greetings,
Marc
there are already search box available in datatables, you will have to enable this. please datatables doc for this. thanks!
Hi,
I downloaded tha script and implemented on my webpage but noticed that the insert Task (+) doesn’t works.
I am a beginner and I not able to found the error.
Anybody know how fix it?
Many Thanks
regards B.
Is there id field is auto increment, I think it needs to be auto increment. Try this, thanks!