如何构建自己的mysql_在PHP和MySQL中从头开始构建自己的简单CRM

如何构建自己的mysql

by Richard

理查德(Richard)

在PHP和MySQL中从头开始构建自己的简单CRM (Build yourself a simple CRM from scratch in PHP and MySQL)

Customer Relationship Management (CRM) is a system that manages customer interactions and data throughout the customer life-cycle 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.

客户关系管理(CRM)是一个系统，它通过不同渠道在客户与公司之间的整个客户生命周期中管理客户交互和数据。 在本教程中，我们将使用PHP构建自定义的CRM，销售团队可以使用它来跟踪整个销售周期中的客户。

We’ll be creating a simple CRM system for salespeople to:

我们将为销售人员创建一个简单的CRM系统，以：

• Access their tasks
  访问他们的任务
• View their leads
  查看他们的线索
• Create new tasks for each lead
  为每个潜在客户创建新任务
• Create new opportunity
  创造新机会
• Close a sale
  结束销售

Sales managers will be able to:

销售经理将能够：

• Manage all customers
  管理所有客户
• Manage sales team
  管理销售团队
• View current sales activities
  查看当前销售活动

Download Demo Files

下载演示文件

CRM的基础 (Building Blocks of a CRM)

Here is a list of the essential components of the CRM:

这是CRM的基本组成部分的列表：

• Leads: initial contacts

  潜在客户 ：初步联系

• Accounts: Information about the companies you do business with

  帐户 ：与您开展业务的公司有关的信息

• Contact: Information about the people you know and work with. Usually, one account has many contacts

  联系人 ：有关您认识和与之共事的人的信息。 通常，一个帐户有很多联系人

• Opportunities: Qualified leads

  机会 ：合格的潜在客户

• Activities: Tasks, meetings, phone calls, emails and any other activities that allow you to interact with customers

  活动 ：任务，会议，电话，电子邮件和任何其他可让您与客户互动的活动

• Sales: Your sales team

  销售 ：您的销售团队

• Dashboard: CRM dashboards are much more than just eye candy. They should deliver key information at a glance and provide links to drill down for more details.

  仪表板 ：CRM仪表板不只是令人眼花candy乱的东西。 他们应该一目了然地提供关键信息，并提供链接以深入了解更多细节。

• Login: Salespeople and managers have different roles in the system. Managers have access to reports and sales pipeline information.

  登录名 ：销售人员和经理在系统中具有不同的角色。 经理可以访问报告和销售渠道信息。

系统要求 (System Requirements)

• PHP 5.3+,
  PHP 5.3+，
• MySQL or MariaDB
  MySQL或MariaDB

• phpGrid

  phpGrid

创建CRM数据库 (Create CRM Database)

We will start by creating our custom CRM database. The main tables we will be using are:

我们将从创建自定义CRM数据库开始。 我们将使用的主要表是：

• contact — contains basic customer data

  联系人 —包含基本客户数据

• notes — holds information collection from Contact by sales people.

  notes —保存销售人员从Contact中收集的信息。

• users — information about sales people

  用户 -有关销售人员的信息

The Contact table contains basic customer information including names, company addresses, project information, and so forth.

联系人表包含基本的客户信息，包括姓名，公司地址，项目信息等。

The Notes table stores all sales activity information such as meetings and phone calls.

Notes表存储所有销售活动信息，例如会议和电话。

The Users table holds login information about users of the system such as usernames and passwords. Users can also have roles, such as Sales or Manager.

Users表包含有关系统用户的登录信息，例如用户名和密码。 用户还可以具有角色，例如销售或经理。

All other tables are lookup tables to join to the three main relational database tables.

所有其他表都是查找表，可以连接到三个主要的关系数据库表。

• contact_status — contains contact status such as Lead and Opportunity. Each indicates a different stage in a typical sales cycle

  contact_status-包含联系状态，例如潜在客户和机会。 每个都表示典型销售周期中的不同阶段

• task_status — the task status can be either Pending or Completed

  task_status-任务状态可以为Pending或Completed

• user_status — a sale person can be Active or Inactive

  user_status-销售人员可以是活动的或不活动的

• todo_type — a type of task either Task or Meeting

  todo_type-任务或会议的任务类型

• todo_desc — description of a task such as Follow Up Email, Phone Call, and Conference etc.

  todo_desc —任务的描述，例如“后续电子邮件”，“电话”和“会议”等

• 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 such as tables, views, or 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 auto-increment number. This is a unique number that is generated when a new record is inserted into a table.

对于关系数据库中的每个表，最好有一个主键。 主键是每个记录的唯一标识符。 它可以是社会安全号码(SSN)，车辆识别号码(VIN)或自动递增号码。 这是将新记录插入表中时生成的唯一编号。

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.

下面是我们简单的CRM的数据库图。 每个表中的键符号代表表主键。 放大镜指示链接数据库中另一个表的外键。 有时我们称其为“查找”表。

install.sql (install.sql)

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.

了解数据库表结构后，在db文件夹中找到“ install.sql”脚本，然后使用MySQL工具(如MySQL Workbench或Sequel Pro)运行SQL脚本。 它应该创建一个名为custom_crm的新关系数据库及其数据库表。

ZenBase的附带说明 (A Side Note on ZenBase)

The CRM application is also one of the many application templates readily available at ZenBase (built on the top of phpGrid) for anyone — with or without coding skills — to use and customize for their own needs.

设置phpGrid (Setup phpGrid)

Our 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 webpage. We can use a datagrid tool from phpGrid. We use a tool instead of building them from scratch because developing the datagrid is usually 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:

我们的CRM包含许多数据网格。 数据网格是一个类似于电子表格的数据表，它显示代表数据库表中记录和字段的行和列。 数据网格使最终用户能够读取和写入网页上的数据库表。 我们可以使用phpGrid中的datagrid工具。 我们使用工具而不是从头开始构建它们，因为开发数据网格通常是乏味且容易出错的。 datagrid库将为我们处理所有内部数据库CRUD(创建，删除，更新和删除)操作，并且用很少的代码就能获得更好，更快的结果。 要安装phpGrid，请按照以下步骤操作：

1. Unzip the phpGrid download file.
   解压phpGrid下载文件。

2. Upload the phpGrid folder to the phpGrid folder.

   将phpGrid文件夹phpGrid文件夹。

3. Complete the installation by configuring the conf.php file.

   通过配置conf.php文件来完成安装。

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:

在开始编码之前，我们必须在phpGrid配置文件conf.php指定数据库信息。 这是数据库连接设置的示例：

• PHPGRID_DB_HOSTNAME — web server IP or host name

  PHPGRID_DB_HOSTNAME — Web服务器IP或主机名

• PHPGRID_DB_USERNAME — database user name

  PHPGRID_DB_USERNAME —数据库用户名

• PHPGRID_DB_PASSWORD — database password

  PHPGRID_DB_PASSWORD —数据库密码

• PHPGRID_DB_NAME — database name of our CRM

  PHPGRID_DB_NAME —我们的CRM的数据库名称

• PHPGRID_DB_TYPE — type of database

  PHPGRID_DB_TYPE —数据库类型

• PHPGRID_DB_CHARSET — always ‘utf8’ in MySQL

  PHPGRID_DB_CHARSET —在MySQL中始终为'utf8'

To learn more about configuring phpGrid, check out phpGrid complete installation guide.

要了解有关配置phpGrid的更多信息，请查看phpGrid完整安装指南 。

页面模板 (Page Template)

Before we can start building our first page of the CRM, it is a good practice to make the reusable page items such as header and footer.

在开始构建CRM的第一页之前，最好先制作可重复使用的页面项，例如页眉和页脚。

The page will comprise of a header, menu, body and footer. We will start by creating a reusable page template.

该页面将包含页眉，菜单，正文和页脚。 我们将从创建可重复使用的页面模板开始。

head.php (head.php)

This is a basic HTML5 template header. It includes a link to a custom stylesheet that will be created in a later step.

这是基本HTML5模板标头。 它包括指向将在以后的步骤中创建的自定义样式表的链接。

menu.php (menu.php)

Notice the usage of $_GET['currentPage']. Each page will set a value which will highlight the name of the current page on the top menu bar.

注意$_GET['currentPage']的用法。 每个页面都会设置一个值，该值将在顶部菜单栏上突出显示当前页面的名称。

Include the following code in style.css for menu styling (minified). It will transform the above, unordered list into a menu.

在style.css中包含以下代码以进行菜单样式设置(最小化)。 它将上面的无序列表转换为菜单。

footer.php (footer.php)

Simple closing body and html tags.

简单的结束正文和html标签。

完整页面模板 (Complete Page Template)

This is the complete page template. The main content will go after Section Title.

这是完整的页面模板。 主要内容将在Section Title 。

CRM主页 (CRM Main Pages)

Are you still with me? Good! We can now finally develop the first page in our CRM.

你还在吗？ 好！ 现在，我们终于可以在CRM中开发首页。

Our CRM for the sales team members has four pages:

我们针对销售团队成员的CRM共有四页：

• Tasks

  任务

• Leads

  潜在客户

• Opportunities

  商机

• Customers/Won

  客户/韩元

Each page indicates a different stage in a typical sales cycle.

每页表示典型销售周期中的不同阶段。

销售人员页面设计样机 (Sale People Page Design Mockup)

Here’s our CRM design mockup for the sales people.

这是我们为销售人员设计的CRM设计样机。

任务页面 (Tasks Page)

When a sales team member logged in, the first page he sees is a list of current tasks.

当销售团队成员登录时，他看到的第一页是当前任务的列表。

As you may recall, our Notes table holds all the sales activity information. We can create a datagrid and populate it from the Notes table using phpGrid.

您可能还记得，我们的Notes表包含所有销售活动信息。 我们可以创建一个数据网格，并使用phpGrid从Notes表中填充它。

The Tasks page main content is a datagrid. The following two lines will give us a list of tasks of the current sales person.

任务页面的主要内容是一个数据网格。 以下两行将为我们提供当前销售人员的任务清单。

• The first line creates a phpGrid object by passing the SELECT SQL statement, its primary key — ID, and then the name of the database table - notes.

  第一行通过传递SELECT SQL语句，其主键ID和数据库表notes的名称来创建phpGrid对象。

• The second and the final line calls display() function to render the datagrid on the screen. Check out the basic datagrid demo for more detail.

  第二行和最后一行调用display()函数，将datagrid呈现在屏幕上。 查看基本的datagrid演示以获取更多详细信息。

潜在顾客页面 (Leads Page)

The leads page contains list of current leads that the sales person is responsible for. Each Lead can have one or many Notes. We will use the phpGrid master-detail feature for that.

潜在客户页面包含销售人员负责的当前潜在客户列表。 每个线索可以具有一个或多个注释。 为此，我们将使用phpGrid主从功能。

We also need to use set_query_filter() to display only the leads, Status = 1, and only for the current sales person.

我们还需要使用set_query_filter ()仅显示销售线索Status = 1 ，并且仅显示当前销售人员。

联系人状态表 (Contact status table)

商机页面 (Opportunities Page)

A Lead becomes an Opportunity once it is qualified. The Opportunities page is similar to the Leads page. The only difference is the filtered status code in set_query_filter is Status = 2.

潜在客户一旦具备资格，便成为机会。 商机页面与商机页面相似。 唯一的区别是set_query_filter中已过滤的状态码为Status = 2 。

客户/获奖页面 (Customers/Won Page)

Customers/Won has the Status = 3. Similar to Leads and Opportunities, Customers/Won can also have Notes.

客户/韩元Status = 3 。 与潜在客户和机会类似，“客户/获胜者”也可以具有注释。

That’s all there is to it for sales people in our simple CRM.

对于我们简单的CRM中的销售人员而言，这就是全部。

经理仪表板 (Manager Dashboard)

The sales manager will have access to all records in the sales pipeline as well as the ability to manage sales team and customer data.

销售经理将有权访问销售渠道中的所有记录以及管理销售团队和客户数据的能力。

We will have a single web page with tabbed menu similar to the phpGrid tabbed grid demo.

我们将有一个带有选项卡式菜单的网页，类似于phpGrid的选项卡式网格演示 。

经理仪表板设计样机 (Manager Dashboard Design Mockup)

我的销售代表 (My Sales Reps)

主要内容 (Main content)

Each tab represents a table in the CRM database. $_GET['gn'] will store the table name. It dynamically generates the datagrid based on table name passed.

每个选项卡代表CRM数据库中的一个表。 $_GET['gn']将存储表名。 它根据传递的表名动态生成数据网格。

It’s very easy to integrate jQueryUI Tabs with phpGrid. Please refer to the phpGrid Tabbed Grid demo for more information.

将jQueryUI Tabs与phpGrid集成非常容易。 请参阅phpGrid 选项卡式网格演示以获得更多信息。

我的销售代表页面 (My Sales Rep Page)

Since a sales manager needs to quickly find out whom a sale person is working with, we added a detail grid $sdg populated from contact table and link with the master grid.

由于销售经理需要快速找出销售人员正在与谁合作，因此我们添加了一个详细表格$sdg ，该表格从联系人表填充并与主表格链接。

sales_rep is the connecting key in contact table to the id that is the foreign key in users table. Remember the users stores all of our sales people information.

sales_rep是contact表中与users表中外键id的连接键。 请记住， users存储了我们所有的销售人员信息。

屏幕截图 (Screenshots)

CRM-销售屏幕 (CRM — Sales Screen)

CRM —经理屏幕 (CRM — Manager Screen)

现场演示 (Live Demo)

CRM Sales Rep Screen | CRM Managers screen

CRM销售代表屏幕 | CRM经理屏幕

需要编写更少的代码吗？ (Need to Write Even Less Code?)

If you are new to programming and are not yet comfortable with coding, you may want to check out ZenBase that is built on the top of the phpGrid. The CRM is but one of the many application templates readily available at ZenBase for anyone — with or without coding skills — to use and customize for their own needs.

如果您是编程新手，但对编码还不满意，则可能需要查看phpGrid顶部构建的ZenBase。 CRM是ZenBase随时可用的许多应用程序模板之一，无论具有或没有编码技能的任何人都可以使用和定制他们自己的需求。

GitHub上的完整源代码 (Complete Source Code on GitHub)

phpcontrols/phpgrid-custom-crmphpgrid-custom-crm - Custom CRM Demo - Learn to build yourself a custom CRM in PHP and MySQL, which a sales team can…github.com

phpcontrols / phpgrid-custom-crm phpgrid-custom-crm-自定义CRM演示-学习在PHP和MySQL中构建自己的自定义CRM，销售团队可以… github.com

谢谢阅读。 如果您喜欢这篇文章，请点击那个拍手按钮？ 帮助其他人找到它，并在Twitter上关注我。 (Thanks for reading. If you enjoyed this article, please hit that clap button ? to help others find it and follow me on Twitter.)

翻译自: https://www.freecodecamp.org/news/building-a-simple-crm-from-scratch-in-php-58fef061b075/

如何构建自己的mysql