Building an AI-Powered CRM with PHP 8, MySQL, Bootstrap 5, and OpenAI

In this tutorial, we’ll build a simple AI-enhanced CRM (Customer Relationship Management) app from scratch. We’ll use XAMPP (Apache + PHP 8 + MySQL) on localhost, Bootstrap 5 for the frontend, and the OpenAI PHP client to generate draft emails. All this is going to happen step-by-step, establishing an environment, creating the database and the PHP files, and connecting to the OpenAI API. We will maintain the friendly tone and emphasize clear instructions, and apply the instructions to all levels of experience. All code files will be well-marked, and we will refer to important concepts and commands.

1. Setting Up XAMPP and Your Project Folder

First, download and install XAMPP from the Apache Friends website – it bundles Apache, MariaDB/MySQL, PHP, and phpMyAdmin. During installation, make sure to include Apache and MySQL. Once installed, open the XAMPP Control Panel and start the Apache and MySQL modules.

• Open the XAMPP Control Panel and click Start for Apache and MySQL. Ensure they turn green. You can then click Admin for MySQL to open phpMyAdmin (for database setup later).
• Create a project folder: In your XAMPP install directory (e.g., C:\xampp\ on Windows or /Applications/XAMPP/ on macOS), locate the htdocs folder. This is where web files are. Inside htdocs, create a new folder for your project (e.g., crm_app).
  

Example file structure on Windows:

C:\xampp\htdocs\crm_app\ 

You can test PHP by creating a simple index.php inside the crm_app with <?php phpinfo(); ?> and navigating to http://localhost/crm_app/ in your browser to see if PHP is running.

Helpful Tip: The XAMPP htdocs directory should hold all your web projects. Create a unique subfolder there for each project.

2. Creating the MySQL Database and Tables

Next, set up the MySQL database for the CRM. Open phpMyAdmin (usually at http://localhost/phpmyadmin/ after starting MySQL). Create a new database, e.g., crm_db.

We’ll create three main tables: users, contacts, and notes. These tables will store your app users, customer contacts, and notes/activity logs, respectively. For example:

• Users: Stores login info (username/email and hashed password) and user metadata.
• Contacts: Stores contact details (name, email, phone, company, etc.) and a foreign key to the user who owns the contact.
• Notes: Stores notes or activity logs linked to a contact (e.g., “called client on X date”) and which user added them.

In phpMyAdmin, select crm_db, go to the SQL tab, and run queries like below. (You can also run these via a MySQL client.)

-- Create users table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create contacts table
CREATE TABLE contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,   -- who this contact belongs to
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  phone VARCHAR(20),
  company VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create notes table (activity logs)
CREATE TABLE notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NOT NULL,
  user_id INT NOT NULL,
  note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

These SQL statements create our three tables. The users table holds user credentials (we’ll hash passwords before storing them). The contacts table links each contact to a user_id (so each user only sees their own contacts). The notes table lets users attach text notes to a contact (e.g., meeting notes or follow-up reminders). As noted, this simple schema (contacts, notes, users) is common in CRM systems.

-- From a GfG CRM tutorial for reference:contentReference[oaicite:9]{index=9}:contentReference[oaicite:10]{index=10}
CREATE TABLE contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  phone VARCHAR(15),
  company VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(255) NOT NULL
);

You can adapt fields as needed (e.g., add user_id in contacts, add a name column in users, etc.).

Note: The notes table isn’t shown above, but it logically stores each note with contact_id and user_id. In our schema, we have a note TEXT plus foreign keys for contact_id and user_id. We’ll use this to build the activity log.

3. Writing the Database Connection (db.php)

Create a PHP file (e.g., db.php) in your project to handle database connections. We recommend using PDO for security and flexibility. In db.php, connect to MySQL using PDO like this:

<?php
// db.php
$host = 'localhost';
$db   = 'crm_db';
$user = 'root';      // or your MySQL username
$pass = '';          // or your MySQL password
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES   => false, // disable emulation for real prepared statements
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    exit("Database connection failed: " . $e->getMessage());
}

This code sets up a PDO connection to crm_db. We turned off emulated prepared statements (ATTR_EMULATE_PREPARES = false) to ensure true parameter binding (helps prevent SQL injection). We also enabled exception mode for errors.

You can alternatively use mysqli, but PDO is recommended. (If using mysqli, remember to use prepare() before executing SQL to avoid injection.)

Security tip: Use prepared statements (with PDO or mysqli->prepare()) so that user input is never directly interpolated into SQL. This separates commands from data and guards against injection.

4. Creating Layout Files (Header, Footer, Navbar)

It is a habit to contain shared header and footer files to eliminate the repetition of code. Bootstrap 5 will be used to do the styling. Get Bootstrap or CDN download. As an illustration, in your header.php, you can add the Bootstrap CSS and begin the HTML:

<!-- header.php -->
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>My CRM App</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- Bootstrap CSS (via CDN) -->
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
  <!-- Navbar -->
  <nav class="navbar navbar-expand-lg navbar-dark bg-primary">
    <div class="container-fluid">
      <a class="navbar-brand" href="index.php">CRM App</a>
      <div class="collapse navbar-collapse">
        <ul class="navbar-nav me-auto">
          <li class="nav-item"><a class="nav-link" href="contacts.php">Contacts</a></li>
          <li class="nav-item"><a class="nav-link" href="logout.php">Logout</a></li>
        </ul>
      </div>
    </div>
  </nav>

Then, on each page, include header.php at the top and footer.php at the bottom. This ensures a consistent navbar and styling across pages. No special references needed here, just Bootstrap docs for syntax.

5. User Authentication (Register, Login, Sessions)

We’ll allow users to register and log in. First, create a register.php with a form (username/email and password) and processing code. In the form POST handling, hash the password and save the user.

<!-- register.php -->
<?php
require 'db.php'; // load $pdo
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $email = $_POST['email'];
    $password = $_POST['password'];
    // Hash the password securely
    $hash = password_hash($password, PASSWORD_DEFAULT);
    // Insert new user
    $stmt = $pdo->prepare("INSERT INTO users (email, password) VALUES (?, ?)");
    if ($stmt->execute([$email, $hash])) {
        echo '<div class="alert alert-success">Registration successful. <a href="login.php">Login here</a>.</div>';
    } else {
        echo '<div class="alert alert-danger">Registration failed.</div>';
    }
    exit;
}
?>
<?php include 'header.php'; ?>
<div class="container mt-4">
<h2>Register</h2>
<form method="post" action="register.php">
  <div class="mb-3">
    <label>Email:</label>
    <input type="email" name="email" class="form-control" required>
  </div>
  <div class="mb-3">
    <label>Password:</label>
    <input type="password" name="password" class="form-control" required>
  </div>
  <input type="submit" value="Register" class="btn btn-primary">
</form>
</div>
<?php include 'footer.php'; ?>

Notice we used password_hash() to hash the password before saving. The PHP manual states, “password_hash() creates a new password hash using a strong one-way hashing algorithm.” We store the result (which includes its salt) in the DB. Never store raw passwords!

Next, create login.php with a form. On POST, look up the user and use password_verify():

<!-- login.php -->
<?php
require 'db.php';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $email = $_POST['email'];
    $password = $_POST['password'];
    // Find user by email
    $stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
    $stmt->execute([$email]);
    if ($user = $stmt->fetch()) {
        if (password_verify($password, $user['password'])) {
            session_start();
            $_SESSION['user_id'] = $user['id'];
            header('Location: contacts.php');
            exit;
        }
    }
    echo '<div class="alert alert-danger">Invalid email or password.</div>';
}
?>
<?php include 'header.php'; ?>
<div class="container mt-4">
<h2>Login</h2>
<form method="post" action="login.php">
  <div class="mb-3">
    <label>Email:</label>
    <input type="email" name="email" class="form-control" required>
  </div>
  <div class="mb-3">
    <label>Password:</label>
    <input type="password" name="password" class="form-control" required>
  </div>
  <input type="submit" value="Login" class="btn btn-primary">
</form>
</div>
<?php include 'footer.php'; ?>

This code verifies the hashed password using password_verify(). Using hashed passwords and verifying them is a must for security. After successful login, we set a session (e.g. $_SESSION[‘user_id’]) to remember the user.

On pages that require login (like contacts.php), always start with session_start() and check that $_SESSION[‘user_id’] is set; if not, redirect to login. This protects pages from unauthorized access.

Security Tip: Always hash user passwords (with password_hash()) and never store plaintext. PHP built-in functions ensure strong hashes. Also, use prepared statements (see next point) to safely handle login queries.

6. Building the Contacts Pages

Now, let’s add functionality to manage contacts. We’ll create:

• contacts.php: Lists all contacts for the logged-in user.
• add_contact.php: Form to add a new contact.
• view_contact.php: Displays details for one contact (and notes, and AI email generation).
• delete_contact.php: (optional) to remove a contact.

contacts.php

This page fetches all contacts for the current user (user_id from the session) and displays them in a table. Example snippet:

<!-- contacts.php -->
<?php
require 'db.php';
session_start();
if (!isset($_SESSION['user_id'])) { header('Location: login.php'); exit; }
$userId = $_SESSION['user_id'];

// Fetch contacts for this user
$stmt = $pdo->prepare("SELECT * FROM contacts WHERE user_id = ? ORDER BY created_at DESC");
$stmt->execute([$userId]);
$contacts = $stmt->fetchAll();
?>
<?php include 'header.php'; ?>
<div class="container mt-4">
  <h2>Your Contacts</h2>
  <a href="add_contact.php" class="btn btn-success mb-3">Add New Contact</a>
  <table class="table table-bordered">
    <thead><tr><th>Name</th><th>Email</th><th>Company</th><th>Actions</th></tr></thead>
    <tbody>
      <?php foreach($contacts as $c): ?>
      <tr>
        <td><?= htmlspecialchars($c['name']) ?></td>
        <td><?= htmlspecialchars($c['email']) ?></td>
        <td><?= htmlspecialchars($c['company']) ?></td>
        <td>
          <a href="view_contact.php?id=<?= $c['id'] ?>" class="btn btn-primary btn-sm">View</a>
        </td>
      </tr>
      <?php endforeach; ?>
    </tbody>
  </table>
</div>
<?php include 'footer.php'; ?>

This lists each contact with a “View” button. We use htmlspecialchars() to safely output data.

add_contact.php

Provide a form to add contacts:

<!-- add_contact.php -->
<?php
require 'db.php';
session_start();
$userId = $_SESSION['user_id'];
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $name = $_POST['name']; $email = $_POST['email'];
    $phone = $_POST['phone']; $company = $_POST['company'];
    $stmt = $pdo->prepare("INSERT INTO contacts (user_id, name, email, phone, company) VALUES (?, ?, ?, ?, ?)");
    $stmt->execute([$userId, $name, $email, $phone, $company]);
    header('Location: contacts.php');
    exit;
}
?>
<?php include 'header.php'; ?>
<div class="container mt-4">
  <h2>Add Contact</h2>
  <form method="post" action="add_contact.php">
    <div class="mb-3"><label>Name:</label>
      <input type="text" name="name" class="form-control" required></div>
    <div class="mb-3"><label>Email:</label>
      <input type="email" name="email" class="form-control" required></div>
    <div class="mb-3"><label>Phone:</label>
      <input type="text" name="phone" class="form-control"></div>
    <div class="mb-3"><label>Company:</label>
      <input type="text" name="company" class="form-control"></div>
    <input type="submit" value="Add Contact" class="btn btn-primary">
  </form>
</div>
<?php include 'footer.php'; ?>

Again, we used a prepared statement ($pdo->prepare) when inserting to keep SQL safe.

view_contact.php

On this page, show the contact full info and any notes, and provide the AI email generation feature (in the next section). For example:

<!-- view_contact.php -->
<?php
require 'db.php';
session_start();
$userId = $_SESSION['user_id'];
$contactId = $_GET['id'] ?? 0;
// Fetch contact
$stmt = $pdo->prepare("SELECT * FROM contacts WHERE id = ? AND user_id = ?");
$stmt->execute([$contactId, $userId]);
$contact = $stmt->fetch();
if (!$contact) { exit('Contact not found.'); }

// Fetch notes for this contact
$notesStmt = $pdo->prepare("SELECT * FROM notes WHERE contact_id = ? ORDER BY created_at DESC");
$notesStmt->execute([$contactId]);
$notes = $notesStmt->fetchAll();
?>
<?php include 'header.php'; ?>
<div class="container mt-4">
  <h2>Contact Details</h2>
  <p><strong>Name:</strong> <?= htmlspecialchars($contact['name']) ?><br>
     <strong>Email:</strong> <?= htmlspecialchars($contact['email']) ?><br>
     <strong>Phone:</strong> <?= htmlspecialchars($contact['phone']) ?><br>
     <strong>Company:</strong> <?= htmlspecialchars($contact['company']) ?></p>

  <h4>Notes / Activity</h4>
  <ul class="list-group">
    <?php foreach($notes as $note): ?>
      <li class="list-group-item"><?= htmlspecialchars($note['note']) ?> 
          <em class="text-muted">(<?= $note['created_at'] ?>)</em>
      </li>
    <?php endforeach; ?>
  </ul>
  <!-- Add note form -->
  <form method="post" action="add_note.php">
    <div class="mb-3 mt-3">
      <textarea name="note" class="form-control" placeholder="Add a note"></textarea>
      <input type="hidden" name="contact_id" value="<?= $contactId ?>">
    </div>
    <input type="submit" value="Add Note" class="btn btn-secondary">
  </form>

  <!-- Generate email button will go here (next section) -->
  <button id="generateEmail" class="btn btn-success mt-4">Generate Email</button>
  <pre id="emailDraft" class="mt-3"></pre>
</div>
<?php include 'footer.php'; ?>

This shows the contact and lists all notes for them. We’ll add the “Generate Email” button in the next step.

7. Adding Notes (Activity Logs)

We already prepared the notes table. To add notes, make a simple add_note.php:

<!-- add_note.php -->
<?php
require 'db.php';
session_start();
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $contactId = $_POST['contact_id'];
    $userId = $_SESSION['user_id'];
    $noteText = $_POST['note'];
    $stmt = $pdo->prepare("INSERT INTO notes (contact_id, user_id, note) VALUES (?, ?, ?)");
    $stmt->execute([$contactId, $userId, $noteText]);
    header("Location: view_contact.php?id=$contactId");
    exit;
}

This script takes the note text, associates it with the contact and user, and saves it. After adding, it redirects back to view_contact.php for that contact. We used a prepared statement to safely insert user-supplied text.

Now in view_contact.php, the new note appears at the top of the list (due to ORDER BY created_at DESC). This serves as our “activity log” for each contact.

8. Installing Composer and Necessary Libraries

Now we’ll integrate the OpenAI API. First, install Composer if you haven’t yet. Then, from your project folder in a terminal, run:

composer require openai-php/client

composer require guzzlehttp/guzzle

composer require vlucas/phpdotenv

• openai-php/client is the official PHP client for the OpenAI API.
  
• guzzlehttp/guzzle is an HTTP client that the OpenAI client can use.
  
• vlucas/phpdotenv lets us load environment variables from a .env file.
  

For reference, the OpenAI client README confirms installing via Composer, e.g.:

composer require openai-php/client

composer require guzzlehttp/guzzle

and a guide on PHP dotenv shows how to run:

composer require vlucas/phpdotenv

These commands create a vendor/ folder with the libraries. Composer also generates vendor/autoload.php, which we’ll include in our PHP files.

9. Setting Up the .env File and OpenAI Client

To keep API keys and other secrets out of code, create a .env file in your project root (same level as vendor/). In it, add your OpenAI API key:

OPENAI_API_KEY=sk-YourOpenAIKeyHere

You can also store DB credentials here if desired. The point is to never hard-code secrets in your PHP files.

Now create a bootstrap.php (or add to an existing config file) that loads Composer and the environment variables:

<?php
// bootstrap.php
require __DIR__ . '/vendor/autoload.php';    // Composer autoload

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();   // loads variables into $_ENV

// Initialize OpenAI client using the env var
$openai = OpenAI::client($_ENV['OPENAI_API_KEY']);

We used Dotenv\Dotenv::createImmutable() and then load() as shown in the PHP dotenv docs. This makes $_ENV[‘OPENAI_API_KEY’] available. The OpenAI client is initialized by calling OpenAI::client($apiKey).

For example, the CodeSignal tutorial on OpenAI explains initializing the client like this:

// After loading .env with vlucas/phpdotenv:
$openai = new OpenAI\Client($_ENV['OPENAI_API_KEY']);

This mirrors the official docs (or you can use OpenAI::factory()->withApiKey(), etc). In our code above, we used the static client() helper.

Include bootstrap.php at the top of any PHP file that needs the API client or database (e.g., at the top of ai_draft.php and any page that uses OpenAI).

10. Creating the AI Endpoint (ai_draft.php)

Now we’ll make a PHP endpoint to generate an email draft using OpenAI. Call it ai_draft.php. It will accept a contact ID, fetch that contact info from the database, craft a prompt, send it to OpenAI, and return the draft text as JSON.

Here’s an outline for ai_draft.php:

<?php
// ai_draft.php
require 'bootstrap.php';  // loads $openai and $pdo
session_start();
if (!isset($_SESSION['user_id'])) exit('Unauthorized');

$contactId = $_GET['id'] ?? null;
if (!$contactId) { echo json_encode(['error'=>'Missing contact ID']); exit; }

// Fetch contact details
$stmt = $pdo->prepare("SELECT * FROM contacts WHERE id = ?");
$stmt->execute([$contactId]);
$contact = $stmt->fetch();
if (!$contact) { echo json_encode(['error'=>'Contact not found']); exit; }

// Build the AI prompt using contact info
$prompt = "Write a professional follow-up email to {$contact['name']} from {$contact['company']}. "
        . "Include a friendly tone and mention how our company can assist them. ";
// Optionally include more context or info here.

// Call OpenAI API
$response = $openai->chat()->create([
    'model' => 'gpt-3.5-turbo',
    'messages' => [
        ['role' => 'system', 'content' => 'You are a helpful assistant who writes emails.'],
        ['role' => 'user', 'content' => $prompt]
    ],
]);
$emailDraft = $response->choices[0]->message->content;

// Return as JSON
header('Content-Type: application/json');
echo json_encode(['draft' => $emailDraft]);

Key points:

• We load bootstrap.php, which sets up $openai and $pdo. Make sure the session is active and the user is authorized.
• We retrieve contact by ID. Use a prepared statement to prevent injection.
• We construct a prompt. In this example, we tell the model to write a professional email for the contact by name and company.
• We call the OpenAI client Chat completion endpoint. The code $openai->chat()->create([…]) is from the OpenAI-PHP client library. It specifies the model (e.g., gpt-3.5-turbo) and a list of messages. The response object contains an array of choices, each with a message->content.
• We output the draft email as JSON (so JavaScript can handle it easily).

The OpenAI client documentation shows a similar usage example:

$response = $client->chat()->create([
    'model' => 'gpt-3.5-turbo',
    'messages' => [
        ['role' => 'user', 'content' => 'Hello!']
    ],
]);

Which matches our call. We added a ‘system’ message to set the assistant’s behavior (writing emails).

Make sure your prompt guides the AI well (you can refine it to your needs). The result $emailDraft is the AI-generated email text.

11. Adding the “Generate Email” Button (JavaScript)

Back in view_contact.php, we put a button <button id=”generateEmail”>Generate Email</button>. Now we’ll add JavaScript to call ai_draft.php when that button is clicked and display the result on the page:

<!-- In view_contact.php, after including header and showing contact details -->
<button id="generateEmail" class="btn btn-success mt-3">Generate Email</button>
<pre id="emailDraft" class="border p-3 mt-2"></pre>

<script>
document.getElementById('generateEmail').addEventListener('click', async () => {
  const contactId = <?= json_encode($contactId) ?>;
  const res = await fetch(`ai_draft.php?id=${contactId}`);
  const data = await res.json();
  if (data.draft) {
    document.getElementById('emailDraft').innerText = data.draft;
  } else {
    document.getElementById('emailDraft').innerText = 'Error generating email.';
  }
});
</script>

When the button is clicked, this JS uses the Fetch API to request ai_draft.php?id=…. It then parses the JSON and displays the draft field in a <pre> block. This is standard JavaScript usage; see MDN Fetch docs for more. No external reference is needed for this basic fetch call.

Conclusion

You now have a basic AI-powered CRM running on localhost! We set up XAMPP, created a MySQL database with users, contacts, and notes tables, and built PHP pages for registration, login, and managing contacts. We integrated Bootstrap 5 for a clean UI and used the OpenAI PHP client to generate an email draft for any contact via a simple button. Throughout, we used secure coding practices (hashed passwords, prepared statements, environment variables) to protect data.

From here, you can extend the CRM as you like (e.g., add editing or deleting contacts, improve the AI prompt, or add more features). But the core functionality – user auth, contact management, note-taking, and AI-assisted email generation – is all set up.

About the writer

Hassan Tahir wrote this article, drawing on his experience to clarify WordPress concepts and enhance developer understanding. Through his work, he aims to help both beginners and professionals refine their skills and tackle WordPress projects with greater confidence.