Converting a Lead Into a Customer in PHP Without Creating Duplicate Records

A practical PHP and MySQL pattern for checking existing customers before inserting another record.

Building a CRM has forced me to think about problems that look simple in the interface but become more interesting once I start working through the database logic behind them. One of those problems is converting a lead into a customer.

From the user's perspective, the workflow is straightforward: open a lead, click a button, and turn that lead into a customer. From the database's perspective, there is an important question that needs to be answered first:

Does this customer already exist?

Without that check, clicking Convert to Customer can easily create duplicate customer records. Those duplicates eventually affect everything connected to the customer: invoices, quotes, appointments, work orders, communication history, and reporting.

Here is the approach I use to think about this problem in a PHP/MySQL application.

The Basic Data Model

A simple CRM might begin with separate leads and customers tables.

A lead represents someone who has entered the sales pipeline but has not necessarily become a customer yet.

CREATE TABLE leads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(50),
    status VARCHAR(50) NOT NULL DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The customer table contains people or businesses that have moved beyond the lead stage.

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The exact schema will depend on the application. The important part is recognizing that converting a lead should not automatically mean inserting a new customer.

First, I need to determine whether that person is already represented in customers.

Why Checking by Name Is Not Enough

An obvious first attempt would be something like this:

SELECT id
FROM customers
WHERE name = ?
LIMIT 1;

That is better than performing no duplicate check at all, but names make poor identifiers.

Two customers can legitimately have the same name. The same customer's name might also be entered differently:

John Liter
John A. Liter
john liter
J. Liter

Business names introduce similar problems.

Whenever possible, I would rather use stronger identifiers such as an email address or phone number.

For example:

SELECT id
FROM customers
WHERE email = ?
LIMIT 1;

The problem is that not every lead will necessarily have an email address. A practical CRM may therefore need to check more than one field.

Checking for an Existing Customer With PHP

Using PDO and prepared statements, the application can look for an existing customer before inserting anything.

A simplified example looks like this:

$existingCustomer = null;

if (!empty($lead['email'])) {
    $stmt = $pdo->prepare(
        'SELECT id
         FROM customers
         WHERE email = ?
         LIMIT 1'
    );

    $stmt->execute([$lead['email']]);
    $existingCustomer = $stmt->fetch(PDO::FETCH_ASSOC);
}

if (!$existingCustomer && !empty($lead['phone'])) {
    $stmt = $pdo->prepare(
        'SELECT id
         FROM customers
         WHERE phone = ?
         LIMIT 1'
    );

    $stmt->execute([$lead['phone']]);
    $existingCustomer = $stmt->fetch(PDO::FETCH_ASSOC);
}

This establishes a simple priority:

Email → Phone → No Match

If an email matches an existing customer, I can use that customer's ID instead of creating another record. If there is no email match, I can try the phone number.

The application now has a decision to make.

Insert Only When Necessary

If no customer was found, then I can create one from the lead information.

if (!$existingCustomer) {
    $stmt = $pdo->prepare(
        'INSERT INTO customers (name, email, phone)
         VALUES (?, ?, ?)'
    );

    $stmt->execute([
        $lead['name'],
        $lead['email'],
        $lead['phone']
    ]);

    $customerId = (int) $pdo->lastInsertId();
} else {
    $customerId = (int) $existingCustomer['id'];
}

Now $customerId points to the correct customer regardless of which path was taken.

That is useful because everything that happens after conversion can reference the same variable. The rest of the application does not necessarily need to care whether the customer was just created or already existed.

The Database Should Help Enforce the Rule

Application-level checks are useful, but I do not want PHP to be the only thing protecting the database.

Suppose two requests arrive almost simultaneously.

Both requests could potentially perform the duplicate check before either inserts the customer:

Request A → Customer doesn't exist
Request B → Customer doesn't exist
Request A → INSERT
Request B → INSERT

Now I still have a duplicate.

This is where database constraints become important.

If email addresses must be unique in the application, I can enforce that requirement at the database level:

ALTER TABLE customers
ADD UNIQUE KEY uq_customers_email (email);

The database is now protecting an assumption that the application depends on.

That does not eliminate the need for application logic. It gives me another layer of protection.

The PHP code should also be prepared to handle a duplicate-key exception rather than assuming every insert will succeed.

Normalize Before Comparing

Another lesson is that duplicate detection is only as reliable as the data being compared.

Consider phone numbers:

2545551234
254-555-1234
(254) 555-1234
+1 254 555 1234

Those might all represent the same number.

If I compare the raw strings, MySQL may see completely different values.

A better approach is to normalize information before storing and comparing it. For a U.S. phone number, that might mean stripping formatting characters and consistently deciding how country codes will be represented.

Email addresses also deserve normalization. At minimum, whitespace should not be allowed to create accidental differences between otherwise identical values.

The goal is not simply to ask:

Are these strings identical?

The more useful question is:

Do these records represent the same real customer?

Those are not always the same problem.

What Happens to the Lead?

Creating or finding the customer is only part of the conversion.

The lead also needs to reflect what happened.

One approach is to update its status:

$stmt = $pdo->prepare(
    'UPDATE leads
     SET status = ?
     WHERE id = ?'
);

$stmt->execute([
    'won',
    $leadId
]);

An even more useful design is to maintain a relationship between the original lead and resulting customer.

For example, the leads table could contain:

customer_id INT UNSIGNED NULL

After conversion:

$stmt = $pdo->prepare(
    'UPDATE leads
     SET status = ?, customer_id = ?
     WHERE id = ?'
);

$stmt->execute([
    'won',
    $customerId,
    $leadId
]);

Now I have traceability.

I can determine which lead produced a customer instead of throwing away that relationship once the conversion happens.

That becomes increasingly useful when I start asking questions about lead sources, conversions, marketing performance, or customer history.

Consider Using a Transaction

There is another failure scenario worth thinking about.

Imagine this sequence:

1. Create customer
2. Update lead
3. Something fails

If the customer is successfully inserted but the lead update fails, the database can end up in an inconsistent state.

A transaction can make the conversion behave more like one operation.

try {
    $pdo->beginTransaction();

    // Find or create customer.

    // Update the lead with the customer ID.

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    throw $e;
}

The exact transaction boundaries depend on the application's workflow, but this is the point where I start thinking beyond whether each individual SQL statement works.

I need to think about whether the entire business operation succeeds.

That distinction has become increasingly important as I build applications with multiple related tables.

Security Still Matters on Internal Actions

A conversion button may only exist inside an authenticated CRM, but I still do not want a state-changing action to happen through an unprotected request.

At minimum, I want to think about:

• Authentication
• Authorization
• CSRF protection
• Prepared SQL statements
• Server-side validation
• Error handling

For example, a Convert to Customer action should generally be a POST request rather than something triggered simply by visiting a URL.

<form method="post">
    <input
        type="hidden"
        name="csrf_token"
        value="<?= htmlspecialchars($csrfToken) ?>"
    >

    <input
        type="hidden"
        name="lead_id"
        value="<?= (int) $lead['id'] ?>"
    >

    <button type="submit">
        Convert to Customer
    </button>
</form>

The server then validates the CSRF token and verifies that the authenticated user has permission to perform the conversion.

Internal software is still software exposed to mistakes and potentially malicious requests.

What I Learned

The interesting part of this feature was not the INSERT statement. It was realizing how many decisions surround that insert.

A reliable lead-to-customer conversion needs to answer several questions:

1. Does this customer already exist?
2. What fields determine whether two records represent the same person?
3. Is the database enforcing the same assumptions as the PHP application?
4. Can I trace the customer back to the original lead?
5. What happens if one part of the conversion fails?
6. Is the state-changing request properly protected?

That is a much different problem from simply copying information from one table into another.

It is also one of the things I enjoy about building database-backed applications. A button that looks trivial on the frontend can represent several important data-modeling decisions behind the scenes.

The more interconnected an application becomes, the more those decisions matter.