I am trying to create a custom php to check if a customer that has purchased a package is down to its last booking. If there is only 1 session left in their package, I'll set up a cron job to run the php and an automated email will be sent. 

In order for these to work, I'll need to be able to track the number of bookings/appointments a client has made. 

Isn't the bookingsCount in the screenshot supposed to increase when a customer make a booking? I need some way to track how many bookings a customer has made. I remember trying it about a month ago and I believe that I did make it work but I can't seem to remember what the issue is right now. 

This is my custom php: 

<?php

// Load WordPress

require_once('/home1/oqknirmy/public_html/wp-load.php');

// Custom function to check for customers with only one session left

function check_one_session_left() {

    global $wpdb;

    // Query to get customers with only 1 session left

    $results = $wpdb->get_results("

    SELECT 

        customers.email, 

        customers.firstName, 

        customers.lastName, 

        package_customers.bookingsCount AS booked,  

        services.bookingsCount AS total, 

        package_customers.id AS packageCustomerId

    FROM c30_amelia_packages_to_customers AS package_customers

    JOIN c30_amelia_packages_customers_to_services AS services 

        ON package_customers.id = services.packageCustomerId

    JOIN c30_amelia_users AS customers 

        ON package_customers.customerId = customers.id

    WHERE (services.bookingsCount - package_customers.bookingsCount = 1)

    AND package_customers.notifiedOneSession = 0

");

    // Check if results are returned

    if (!empty($results)) {

        echo "Found " . count($results) . " customers with only 1 session left.<br>";  // Display how many customers were found

        // Send notification email to each customer

        foreach ($results as $result) {

            $email = $result->email;

            $first_name = $result->firstName;

            $last_name = $result->lastName;

            $booked = $result->booked;

            $total = $result->total;

            $packageCustomerId = $result->packageCustomerId;

        // Personalize the email message

            $subject = 'Reminder: One Last Session Left!';

            // Format the email message

            $message = "Hi $first_name $last_name, ";

            $message .= "I sincerely hope you’re enjoying your Pilates sessions! This is a friendly reminder that you have just one session left in your current package. ";

            $message .= "To continue your progress, I encourage you to renew your package soon. If you have any questions or need assistance with the renewal process, please let me know! ";

            $message .= "Thank you for your continued support, and see you in the studio! ";

            $message .= "Best regards, ";

            $message .= "The Team ";

            echo "Sending email to: $email (1 session remaining). ";  // Log the email sending

            // Send email via WordPress wp_mail function

            $mail_sent = wp_mail($email, $subject, $message);

            // Log if the mail wasn't sent

            if (!$mail_sent) {

                echo "Failed to send email to: $email.<br>";  // Display error on screen

                error_log("Failed to send email to: $email");

            } else {

                echo "Email successfully sent to: $email.<br>";  // Display success on screen

                error_log("Email successfully sent to: $email");

                // Mark this customer as notified

                $wpdb->update(

                    'c30_amelia_packages_to_customers',

                    array('notifiedOneSession' => 1), // Update notifiedOneSession to 1

                    array('id' => $packageCustomerId) // Where package ID matches

                );

            }

        }

    } else {

        echo "No customers with only 1 session left or notifications have been sent.<br>";  // Display message if no customers were found

        error_log("No customers with only 1 session left found.");

    }

}

// Run the session left check

check_one_session_left();

Hey Marko, 

I understand, I am ok figuring out the rest myself but I just have one major question. I would like to know why is the bookingsCount number not increasing as I book an appointment. What should I do to fix it?