ESI + PHP Snippet.

Hello! beginner dev here. I have tried to make this piece of PHP code to be excluded from being cached with ESI. But I can’t get it to work.

This code:

session_start(); // Start the session at the beginning of the script

$servername = "localhost"; // your server name
$username = "testbase"; // your database username
$password = "123"; // your database password
$dbname = "basetester"; // your database name

// Check if either "msclkid" or "gclid" is present in the URL
if (isset($_GET['msclkid']) || isset($_GET['gclid'])) {
    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $ip_address = $_SERVER['REMOTE_ADDR'];
    $full_url = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://" . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
    $click_id = null;

    if (isset($_GET['msclkid']) && !empty($_GET['msclkid'])) {
        $click_id = urldecode($_GET['msclkid']);
    } elseif (isset($_GET['gclid']) && !empty($_GET['gclid'])) {
        $click_id = urldecode($_GET['gclid']);
    }

    // Store click_id in session
    $_SESSION['click_id'] = $click_id;

    $sql = "INSERT INTO visitor_log (ip_address, full_url, click_id) VALUES (?, ?, ?)";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("sss", $ip_address, $full_url, $click_id);
    if ($stmt->execute()) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $stmt->error;
    }
    $stmt->close();
    $conn->close();
}

However, I managed to get ESI working and exclude caching with the code below.

add_action('litespeed_esi_load-custom_js_block', 'custom_js_block_load');

function custom_js_block_load() {
    
    $ipList = [
        '192.168.0.1',
        '192.168.0.2',
        '192.168.0.3',
        // Add IPv6 addresses here, e.g., '2001:db8::/32'
    ];

    $userIp = $_SERVER['REMOTE_ADDR'];
    $matchFound = false;
    foreach ($ipList as $ipEntity) { 
        if (ipMatch($userIp, $ipEntity)) {
            $matchFound = true;
            break;
        }
    }

    if (!$matchFound) {
        return;
    }
    do_action('litespeed_control_set_nocache'); // Ensures this block isn't cached
    ?>
    <script type="text/javascript">
        window.addEventListener('beforeunload', function(e) {
            e.preventDefault();
            e.returnValue = '';
            var confirmationMessage = 'Are you sure that you wanna quit?';
            e.returnValue = confirmationMessage;
            return confirmationMessage;
        });
    </script>
    <?php
}

function add_custom_js() {
    echo apply_filters('litespeed_esi_url', 'custom_js_block', 'Custom JS Block');
}
add_action('wp_footer', 'add_custom_js');

function ipMatch($userIp, $ipEntity) {
    // If both IPs are IPv4
    if (strpos($userIp, ':') === false && strpos($ipEntity, ':') === false) {
        $userSegments = explode('.', $userIp);
        $entitySegments = explode('.', $ipEntity);

        $numSegments = count($userSegments);
        for ($i = 0; $i < $numSegments; $i++) {
            if ($entitySegments[$i] === '*') {
                continue;
            }
            if ($userSegments[$i] !== $entitySegments[$i]) {
                return false;
            }
        }
        return true;
    }

    // If both IPs are IPv6
    elseif (strpos($userIp, ':') !== false && strpos($ipEntity, ':') !== false) {
        return strtolower($userIp) === strtolower($ipEntity); // Simple equality check for IPv6
    }

    // Return false if none of the above conditions match
    return false;
}

My best try:

// Start the session at the beginning of the script
session_start();

// Function to handle database interaction
function my_custom_db_code() {
    error_log('my_custom_db_code: Function called.'); // Debug
    do_action('litespeed_control_set_nocache'); // Ensures this block isn't cached

    $servername = "localhost"; // your server name
$username = "testbase"; // your database username
$password = "123"; // your database password
$dbname = "basetester"; // your database name

    // Check if either "msclkid" or "gclid" is present in the URL
    if (isset($_GET['msclkid']) || isset($_GET['gclid'])) {
        error_log('my_custom_db_code: msclkid or gclid detected.'); // Debug
        $conn = new mysqli($servername, $username, $password, $dbname);
        if ($conn->connect_error) {
            error_log('my_custom_db_code: Connection failed - ' . $conn->connect_error); // Debug
            die("Connection failed: " . $conn->connect_error);
        }

        $ip_address = $_SERVER['REMOTE_ADDR'];
        $full_url = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://" . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
        $click_id = null;

        if (isset($_GET['msclkid']) && !empty($_GET['msclkid'])) {
            $click_id = urldecode($_GET['msclkid']);
        } elseif (isset($_GET['gclid']) && !empty($_GET['gclid'])) {
            $click_id = urldecode($_GET['gclid']);
        }

        // Store click_id in session
        $_SESSION['click_id'] = $click_id;

        $sql = "INSERT INTO visitor_log (ip_address, full_url, click_id) VALUES (?, ?, ?)";
        $stmt = $conn->prepare($sql);
        if ($stmt === false) {
            error_log('my_custom_db_code: Prepare failed - ' . $conn->error); // Debug
        }

        $stmt->bind_param("sss", $ip_address, $full_url, $click_id);
        if ($stmt->execute()) {
            error_log('my_custom_db_code: New record created successfully'); // Debug
        } else {
            error_log('my_custom_db_code: Error on execute - ' . $stmt->error); // Debug
        }
        $stmt->close();
        $conn->close();
    } else {
        error_log('my_custom_db_code: msclkid and gclid not present.'); // Debug
    }
}

// Register the function with LiteSpeed ESI
add_action('litespeed_esi_load-my_custom_db_code', 'my_custom_db_code');

// Function to invoke ESI URL
function add_my_custom_db_code() {
    error_log('add_my_custom_db_code: Function called.'); // Debug
    // This filter call outputs the ESI URL
    echo apply_filters('litespeed_esi_url', 'my_custom_db_code', 'My Custom DB Code');
}

// Hook the ESI URL invocation function to wp_head
add_action('wp_head', 'add_my_custom_db_code');

Could someone please help me? Many thanks!

The page I need help with: [log in to see the link]