A PHP script to parse Ko-Fi webhook data and store it in a database

kofi-sync.php

<?php

//
// The KOFI_TOKEN value must match the verification token on your Ko-Fi account
// Database settings should be self explanatory
//

const KOFI_TOKEN = '';
const DB_HOST = '';
const DB_USER = '';
const DB_PASS = '';
const DB_NAME = '';

if (!$_POST['data'] || !$data = json_decode($_POST['data'], true)) {
    http_response_code(500);
    die();
}

if ($data['verification_token'] !== KOFI_TOKEN) {
    http_response_code(500);
    die();
}

$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
$dt = new DateTime($data['timestamp']);
$timestamp = $dt->format('Y-m-d H:i:s');

if ($data['type'] === 'Shop Order') {
    $query = "
        INSERT INTO
            orders 
            (
                message_id,
                timestamp,
                from_name,
                amount,
                email,
                currency,
                full_name,
                street_address,
                city,
                state_or_province,
                postal_code,
                country,
                country_code,
                kofi_transaction_id
            )
        VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )";
        
    $conn->execute_query($query, [
        $data['message_id'],
        $timestamp,
        $data['from_name'],
        $data['amount'],
        $data['email'],
        $data['currency'],
        $data['shipping']['full_name'],
        $data['shipping']['street_address'],
        $data['shipping']['city'],
        $data['shipping']['state_or_province'],
        $data['shipping']['postal_code'],
        $data['shipping']['country'],
        $data['shipping']['country_code'],
        $data['kofi_transaction_id']
    ]);

    foreach ($data['shop_items'] as $item) {
        $stmt = $conn->prepare("
            INSERT INTO
                order_items
                (
                    message_id,
                    code,
                    variation,
                    quantity
                )
            VALUES (
                ?, ?, ?, ?
            )");
    
        $stmt->bind_param(
            "ssss",
            $data['message_id'],
            $item['direct_link_code'],
            $item['variation_name'],
            $item['quantity']
        );
        
        $stmt->execute();
    };
    
    exit();
}

if ($data['type'] === 'Donation' || $data['type'] === 'Subscription') {
    $query = "
        INSERT INTO
            donations 
            (
                message_id,
                timestamp,
                type,
                from_name,
                message,
                amount,
                email,
                currency,
                kofi_transaction_id
            )
        VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?
        )";
        
    $conn->execute_query($query, [
        $data['message_id'],
        $timestamp,
        $data['type'],
        $data['from_name'],
        $data['message'],
        $data['amount'],
        $data['email'],
        $data['currency'],
        $data['kofi_transaction_id']
    ]);
    
    exit();
}

http_response_code(500);

schema.sql

DROP TABLE IF EXISTS `donations`;

CREATE TABLE `donations` (
  `message_id` varchar(36) NOT NULL,
  `type` set('Donation','Subscription') NOT NULL,
  `from_name` varchar(64) NOT NULL,
  `message` varchar(512) DEFAULT NULL,
  `amount` decimal(6,2) unsigned NOT NULL,
  `email` varchar(64) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `kofi_transaction_id` varchar(36) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `order_items`;

CREATE TABLE `order_items` (
  `message_id` varchar(36) NOT NULL,
  `code` varchar(10) NOT NULL,
  `variation` varchar(32) DEFAULT NULL,
  `quantity` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`message_id`,`code`),
  CONSTRAINT `message_id-orders` FOREIGN KEY (`message_id`) REFERENCES `orders` (`message_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
  `message_id` varchar(36) NOT NULL,
  `from_name` varchar(64) NOT NULL,
  `amount` decimal(6,2) unsigned NOT NULL,
  `email` varchar(64) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `kofi_transaction_id` varchar(36) NOT NULL,
  `full_name` varchar(64) NOT NULL,
  `street_address` varchar(128) NOT NULL,
  `city` varchar(32) NOT NULL,
  `state_or_province` varchar(32) NOT NULL,
  `postal_code` varchar(10) NOT NULL,
  `country` varchar(16) NOT NULL,
  `country_code` varchar(3) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Hi, I tried your script but it doesn't work for me. Nothing is added in my db.
I added the script url in ko-fi API page but trying ti send a test request nothing happen.
Can you help me please?

Hi, I tried your script but it doesn't work for me. Nothing is added in my db. I added the script url in ko-fi API page but trying ti send a test request nothing happen. Can you help me please?

Hi, check your server logs to see if your server is actually receiving the request, and whether there are any errors being thrown. It's hard to say where the issue is without seeing the logs.