Develop CRM System with PHP and MySQL

PHPZAG Team Tutorials

In our previous tutorial, we have developed Doctor Appointment Management System using PHP. In this tutorial we will develop CRM System with PHP and MySQL.

Customer Relationship Management (CRM) is an online system that manage relationship between customers and company. It handles customers relationship with sales and marketing and increase productivity.

So here in this tutorial, we explain how to develop CRM system with PHP and MySQL. We will cover following in this tutorial.

The Sales Manager will do the following:


  • Manage Sales People.
  • Manage Tasks.
  • Manage Contatcs.

The Sales People will do the following:

  • Manage Tasks.
  • Manage Leads.
  • Manage Opportunity.

So let’s proceed to implement System. The project has following file structure:

  • crm-system-php
    • config
      • database.php
    • class
      • User.php
      • Task.php
      • Leads.php
      • Opportunity.php
      • Customer.php
    • js
      • sales_rep.js
      • opportunity.js
      • leads.js
      • task.js
      • user.js
    • index.php
    • dashboard.php
    • tasks.php
    • leads.php
    • opportunity.php
    • sales_people.php
    • user.php

Step1: MySQL Database Tables

First we will create MySQL tables. The system have three MySQL tables to implement system.

We will create crm_users table to store user 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;

ALTER TABLE `crm_users`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `crm_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

We will create crm_contact table to store contact 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;

ALTER TABLE `crm_contact`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `crm_contact`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

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;

ALTER TABLE `crm_tasks`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `crm_tasks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

Step2: Manage Sales People

We will manage sales people by creating file sales_people.php. We will create HTML to add new sales people record and also list them.

<div class="panel-heading">
		<div class="row">
			<div class="col-md-10">
				<h3 class="panel-title"></h3>
			</div>
			<div class="col-md-2" align="right">
				<button type="button" id="addSalesRep" class="btn btn-info" title="Add Sales Rep"><span class="glyphicon glyphicon-plus">Add</span></button>
			</div>
		</div>
	</div>
	<table id="salesRepListing" class="table table-bordered table-striped">
		<thead>
			<tr>						
				<th>Id</th>					
				<th>Name</th>					
				<th>Email</th>
				<th>Status</th>	
				<th></th>	
				<th></th>	
				<th></th>						
			</tr>
		</thead>
	</table>
</div>

In sales_rep.js file, we will initialize jQuery DataTable and load sales people record making ajax request to manager_action.php with action listSalesRep to list record.

var contactRecords = $('#salesRepListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": false,
	'serverMethod': 'post',		
	"order":[],
	"ajax":{
		url:"manager_action.php",
		type:"POST",
		data:{action:'listSalesRep'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 4, 5, 6],
			"orderable":false,
		},
	],
	"pageLength": 10
});

In manager_action.php, we will check for action and call method listSalesRep() to list record.

$user = new User($db);

if(!empty($_POST['action']) && $_POST['action'] == 'listSalesRep') {
	$user->listSalesRep();
}

We will implement method listSalesRep() in class User.php that return response as json data to load in datatable.


public function listSalesRep(){

	$sqlWhere = '';
	if($_SESSION["role"] == 'manager') { 
		$sqlWhere = "WHERE roles = 'sales' and status = 1";
	}		
	$sqlQuery = "SELECT * FROM ".$this->userTable." $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->userTable." $sqlWhere");
	$stmtTotal->execute();
	$allResult = $stmtTotal->get_result();
	$allRecords = $allResult->num_rows;
	
	$displayRecords = $result->num_rows;
	$records = array();		
	while ($salesRep = $result->fetch_assoc()) { 				
		$rows = array();			
		$rows[] = $salesRep['id'];
		$rows[] = ucfirst($salesRep['name']);			
		$rows[] = $salesRep['email'];
		$rows[] = $salesRep['status'];					
		$rows[] = '<button  type="button" name="view" id="'.$salesRep["id"].'" class="btn btn-info btn-xs view"><span title="View Contacts">View Contacts</span></button>';			
		$rows[] = '<button type="button" name="update" id="'.$salesRep["id"].'" class="btn btn-warning btn-xs update"><span class="glyphicon glyphicon-edit" title="Edit">Edit</span></button>';			
		$rows[] = '<button type="button" name="delete" id="'.$salesRep["id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete">Delete</span></button>';			
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

Step3: Manage Contacts

In contact.php, we will design to add/edit and list contacts record.

<div class="panel-heading">
		<div class="row">
			<div class="col-md-10">
				<h3 class="panel-title"></h3>
			</div>
			<div class="col-md-2" align="right">
				<button type="button" id="addContact" class="btn btn-info" title="Add Contact"><span class="glyphicon glyphicon-plus">Add</span></button>
			</div>
		</div>
	</div>
	<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>
</div>

In contact.js, we will initialize Datatable to list record by making ajax request to contact_action.php with action listContact to list record.

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
});

We will check for action listContact and call method listContact() from class Contact.php.

$contact = new Contact($db);

if(!empty($_POST['action']) && $_POST['action'] == 'listContact') {
	$contact->listContact();
}

We will implement method listContact() in class Contact.php that return response as json data to load record in datatable.

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">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">Delete</span></button>';			
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

Step4: Manage Tasks

We will create file tasks.php to manage tasks. 


<div class="panel-heading">
	<div class="row">
		<div class="col-md-10">
			<h3 class="panel-title"></h3>
		</div>
		<div class="col-md-2" align="right">
			<button type="button" id="addTasks" class="btn btn-info" title="Add Tasks"><span class="glyphicon glyphicon-plus">Add Task</span></button>
		</div>
	</div>
</div>
<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 initialize jQuery datatable and make ajax request to task_action.php with action listTasks to list tasks.

var taskRecords = $('#tasksListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": false,
	'serverMethod': 'post',		
	"order":[],
	"ajax":{
		url:"task_action.php",
		type:"POST",
		data:{action:'listTasks'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 6, 7],
			"orderable":false,
		},
	],
	"pageLength": 10
});

We will check for action listTasks and call method listTasks() to list tasks.

$task = new Tasks($db);

if(!empty($_POST['action']) && $_POST['action'] == 'listTasks') {
	$task->listTasks();
}

We will implement method listTasks() in class Tasks.php that return response as json data to list tasks in DataTable.

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">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">Delete</span></button>';			
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

Step5: Manage Leads

We will create file leads.php and code to manage leads.

<div class="panel-heading">
	<div class="row">
		<div class="col-md-10">
			<h3 class="panel-title"></h3>
		</div>
		<div class="col-md-2" align="right">
			<button type="button" id="addLeads" class="btn btn-info" title="Add Leads"><span class="glyphicon glyphicon-plus">Add</span></button>
		</div>
	</div>
</div>
<table id="leadsListing" class="table table-bordered table-striped">
	<thead>
		<tr>						
			<th>Id</th>					
			<th>Name</th>					
			<th>Company</th>
			<th>Industry</th>
			<th>Phone</th>
			<th>Email</th>
			<th>Website</th>
			<th>Status</th>
			<th>Contact Date</th>
			<th></th>	
			<th></th>	
			<th></th>						
		</tr>
	</thead>
</table>

We will initialize DataTable to make ajax request to leads_action.php with action listLeads to list leads.


var leadsRecords = $('#leadsListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": false,
	'serverMethod': 'post',		
	"order":[],
	"ajax":{
		url:"leads_action.php",
		type:"POST",
		data:{action:'listLeads'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 9, 10, 11],
			"orderable":false,
		},
	],
	"pageLength": 10
});

we will check for action action listLeads and call method listLeads() from class Leads.php to list leads.

$leads = new Leads($db);

if(!empty($_POST['action']) && $_POST['action'] == 'listLeads') {
	$leads->listLeads();
}

We will implement method listLeads() in class Leads.php that return response as json data to list.

public function listLeads(){
		
		$sqlWhere = '';
		if($_SESSION["role"] == 'sales') { 
			$sqlWhere = " WHERE c.sales_rep = '".$_SESSION["userid"]."' and c.status = 'Lead'";
		}	
		
		$sqlQuery = "SELECT c.id, c.contact_first, c.company, c.industry, c.budget, u.name, c.phone, c.website, c.status, c.initial_contact_date, c.email
		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['phone'];		
			$rows[] = $contact['email'];
			$rows[] = $contact['website'];
			$rows[] = $contact['status'];	
			$rows[] = $contact['initial_contact_date'];			
			$rows[] = '<button  type="button" name="view" id="'.$contact["id"].'" class="btn btn-info btn-xs view"><span title="View Tasks">View Notes</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">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">Delete</span></button>';			
			$records[] = $rows;
		}
		
		$output = array(
			"draw"	=>	intval($_POST["draw"]),			
			"iTotalRecords"	=> 	$displayRecords,
			"iTotalDisplayRecords"	=>  $allRecords,
			"data"	=> 	$records
		);
		
		echo json_encode($output);
	}

Demo Download

Leave a Reply

Your email address will not be published. Required fields are marked *