How I Solved a Challenging Backend Problem with PHP & MySQL

Hello there, it's been a while since I wrote a blog post; well, here I am writing about one of the most challenging problems I encountered and an overview of how I solved it.

Challenges especially ones that will get you worked up cannot be escaped as a backend developer. Recently, while working on an ad network using PHP and MySQL, I encountered a complex issue related to optimizing the Cost Per Mille (CPM) for publishers based on specific criteria. This problem tested my technical skills and provided an invaluable learning experience. In this post, I will walk you through how I solved this problem step-by-step, highlighting the challenges faced and the solutions implemented. I will be glad if there are suggestions I can get on how to efficiently implement or a better way to go about it.

The Problem

The task was to adjust the publisher’s CPM based on the following criteria:

1. 5% increase if there are clicks from 10 different IP addresses in the last 10 minutes.
2. 8% decrease if there are clicks from the same IP address 5 times in the last 10 minutes.
3. 2% increase if there are 10 consecutive clicks from the same country.

The Solution Journey

Step 1: Understanding the Requirements

Before jumping into the implementation, I needed a clear understanding of the requirements. I discussed with stakeholders to confirm the criteria and the desired behavior for adjusting the CPM. This initial step was crucial in planning the solution effectively.

Step 2: Setting Up the Database

I already have my database and tables created, also, the project was already live, so I just needed to add the criteria. I ensured that the database and table were set up to store and accommodate necessary information for clicks tracking. The table schema included fields for storing the click timestamp, ip address, and country code (there are other fields/columns which I wouldn't include 'cos they are not really important for the cause of this post).

Step 3: Capturing Click Data

Next, I implemented a function to get and save the IP address and country code whenever a user clicks an ad. This data was stored in the clicks table.

Step 4: Implementing the Criteria Checks

With the data being captured, the next step was to implement the logic to check the criteria and adjust the CPM accordingly.

1. Checking for Unique IP Addresses I count the unique IP addresses in the last 10 minutes using the COUNT function and DISTINCT keyword which evaluates expression for each row in a group and returns the number of unique, non-null values.

/** 
 * Get the count of unique IP addresses in the last 10 minutes
 */
$stmt = $pdo->prepare("
    SELECT COUNT(DISTINCT ip_address) AS unique_ips
    FROM clicks
    WHERE date_time >= NOW() - INTERVAL 10 MINUTE
");
$stmt->execute();
$unique_ips = $stmt->fetchColumn();

if ($unique_ips >= 10) {
    $cpm *= 1.05;
} else {
    /**
     * Here, I check for clicks from the same IP address
     * This is in the second condition
     * ...
     **/
}

1. Checking for Repeated IP Addresses I then check for repeated or non-unique IP addresses in the last 10 minutes using the SQL COUNT function.

/** 
 * Checking non-unique (repeated) IP addresses in the last 10 minutes
 * If the condition is true, decrease the CPM by 8%
 */
$stmt = $pdo->prepare("
    SELECT ip_address, COUNT(*) AS click_count
    FROM clicks
    WHERE timestamp >= NOW() - INTERVAL 10 MINUTE
    GROUP BY ip_address
    HAVING click_count >= 5
");
$stmt->execute();
$repeated_ips = $stmt->fetchAll();

if (count($repeated_ips) > 0) {
    $cpm *= 0.92;
} else {
    /**
     * Here, I check for consecutive clicks from the same country
     * This is in the third condition
     * ...
     **/
}

1. Checking for Consecutive Clicks from the Same Country Here, I apply the third criterion which is checking for consecutive clicks from the same country.

/** 
 * Checking clicks from the same country consecutively
 * If the condition is true, increase the CPM by 2%
 */
$stmt = $pdo->prepare("
    SELECT country_code
    FROM clicks
    ORDER BY timestamp DESC
    LIMIT 10
");
$stmt->execute();
$last_ten_clicks = $stmt->fetchAll(PDO::FETCH_COLUMN);

if (count(array_unique($last_ten_clicks)) === 1) {
    $cpm *= 1.02;
}

Step 5: Updating the CPM

For this part, I update the publisher's CPM and use it to calculate his earnings for that particular click.

The Outcome

By implementing this solution, the ad network now dynamically adjusts the CPM based on click data from the user. This ensures a fair and optimized revenue model for publishers, enhancing the overall effectiveness of the ad network.

About Me and Why HNG Internship?

I'm Kingsley Gbutemu Kefas, a backend developer passionate about building scalable and efficient systems. I am a problem solver, I love to learn new things and ways of doing something. As a developer, I am motivated by solving challenges, especially complex ones that make me think critically. I know HNG Internship is an opportunity for me to grow as a developer by working on real-world projects and learning from industry experts. I am excited to start my journey with HNG Internship and contribute to the tech community. I believe this journey will sharpen my skills and contribute to impactful projects and I am sure that I will benefit more from the benefits of HNG Premium.