Customer Relationship Management (CRM) is a system that manages customer interactions and data throughout the customer lifecycle between the customer and the company across different channels. In this tutorial, we are going to build a custom CRM in PHP, which a sales team can use to track customers through the entire sales cycle.
We’ll be creating a simple CRM system for salespeople to:
We will start by creating our custom CRM database. The main tables we will be using are:
contact – contains basic customer data
notes – holds information collection from Contact by sales people.
users – information about sales people
The contact table contains basic customer information including names, company addresses, project information, etc.
The notes table is used to store all sales activity information such as meetings and phone calls.
The users table holds login information about users of the system such as usernames and passwords. Users can also have roles, either Sales or Manager.
All other tables are simple lookup tables to join to the aforementioned three main relational database tables.
contact_status – contains contact status such as Lead and Opportunity each indicating a different stage in a typical sales cycle
task_status – the task status can be either Pending or Completed
user_status – a sale person can be Active or Inactive
todo_type – a type of task either Task or Meeting
todo_desc – description of a task such as Follow Up Email, Phone Call, and Conference etc.
roles – a user can be either a Sales Rep or a Manager
Complete Database Schema Diagram
A database schema is the structure that represents the logical view (tables, views, primary and foreign keys) of the entire database. A database schema includes entities and the relationship among them.
It is a good practice to have one primary key for each table in a relational database. A primary key is a unique identifier for each record. It can be the social security number (SSN), vehicle identification number (VIN), or simply auto-increment number (a unique number that is generated when a new record is inserted into a table).
Below is the database diagram of our simple CRM. The key symbol in each table represents the table primary key. The magnifying glass indicates foreign key linking another table in the database. Sometimes we call it the “lookup” table.
Once you have an understanding of the database table structure, find the `install.sql` script in the db folder and use a MySQL tool such as MySQL Workbench or Sequel Pro to run the SQL script. It should create a new relational database named custom_crm and its database tables.
Our simple CRM contains many datagrids. The datagrid is a spreadsheet-like data table that displays rows and columns representing records and fields from the database table. The datagrid gives the end-user ability to read and write to database tables on a web page.
We opt-in a datagrid tool from phpGrid to create the datagrid. The reason to use a tool instead of building them from scratch is that developing the datagrid is usually extremely tedious and error-prone. The datagrid library will handle all internal database CRUD (Create, Remove, Update, and Delete) operations for us with better and faster results with little code.
To install phpGrid, follow these steps:
Unzip the phpGrid download file
Upload the phpGrid folder to the `phpGrid` folder
Complete the installation by configuring the `conf.php` file
Before we begin coding, we must specify the database information in `conf.php`, the phpGrid configuration file. Here is an example of database connection settings: