How do I loop through a table, create a .csv file from the results, attach the file to an email, and send it using SendGrid?

Post author: Adam VanBuskirk
Adam VanBuskirk
6/3/23 in
Tech
PHPWordPress

<?php
// Include necessary WordPress functions
require_once(ABSPATH . 'wp-load.php');

// Function to generate and send the CSV file via email
function generate_and_send_csv() {
    global $wpdb;
    
    // Fetch data from the invoices table
    $invoices = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}invoices");
    
    if (empty($invoices)) {
        return; // No records found, exit the function
    }
    
    // Create a temporary file to store the CSV data
    $csv_file = tempnam(sys_get_temp_dir(), 'invoices');
    
    // Open the file in write mode
    $file_handle = fopen($csv_file, 'w');
    
    // Write the CSV header
    $header = array('Invoice ID', 'Customer', 'Amount', 'Date');
    fputcsv($file_handle, $header);
    
    // Write each invoice record to the CSV file
    foreach ($invoices as $invoice) {
        $data = array($invoice->id, $invoice->customer, $invoice->amount, $invoice->date);
        fputcsv($file_handle, $data);
    }
    
    // Close the file handle
    fclose($file_handle);
    
    // Email the CSV file using SendGrid API
    $sendgrid_api_key = 'YOUR_SENDGRID_API_KEY';
    $to_email = 'recipient@example.com';
    $from_email = 'sender@example.com';
    $subject = 'Invoices CSV File';
    $message = 'Please find attached the invoices CSV file.';
    
    $sendgrid_url = 'https://api.sendgrid.com/v3/mail/send';
    
    $file_content = file_get_contents($csv_file);
    $file_encoded = base64_encode($file_content);
    
    $payload = array(
        'personalizations' => array(
            array(
                'to' => array(array('email' => $to_email)),
                'subject' => $subject
            )
        ),
        'from' => array('email' => $from_email),
        'content' => array(
            array(
                'type' => 'text/plain',
                'value' => $message
            )
        ),
        'attachments' => array(
            array(
                'content' => $file_encoded,
                'type' => 'text/csv',
                'filename' => 'invoices.csv',
                'disposition' => 'attachment'
            )
        )
    );
    
    $headers = array(
        'Authorization: Bearer ' . $sendgrid_api_key,
        'Content-Type: application/json'
    );
    
    $args = array(
        'body' => wp_json_encode($payload),
        'headers' => $headers,
        'method' => 'POST'
    );
    
    $response = wp_remote_post($sendgrid_url, $args);
    
    // Check if the email was sent successfully
    if (is_wp_error($response)) {
        error_log('SendGrid API error: ' . $response->get_error_message());
    } else {
        $response_code = wp_remote_retrieve_response_code($response);
        if ($response_code === 202) {
            // Email sent successfully
            echo 'Invoices CSV file sent successfully!';
        } else {
            error_log('SendGrid API error: Unexpected response code - ' . $response_code);
        }
    }
    
    // Delete the temporary CSV file
    unlink($csv_file);
}

// Hook the function to a
add_action('admin_init', 'generate_and_send_csv');

Explanation of the code

The code starts by including the necessary WordPress functions using require_once(ABSPATH . 'wp-load.php');. This ensures that WordPress core functions are available in the code.

The function generate_and_send_csv is defined. It performs the following steps:a. It retrieves the global $wpdb object, which provides access to the WordPress database.b. The function fetches data from the invoices table using the $wpdb->get_results method. The table name is assumed to be wp_invoices, where wp_ is the WordPress table prefix.c. If no records are found in the invoices table, the function exits.d. A temporary file is created using tempnam to store the CSV data. The file name begins with ‘invoices’ and is stored in the system’s temporary directory.e. The file is opened in write mode using fopen.f. The CSV header is written to the file using fputcsv.

In this example, the header contains the column names: ‘Invoice ID’, ‘Customer’, ‘Amount’, and ‘Date’.g. The function iterates over each invoice record and writes it to the CSV file using fputcsv.h. The file handle is closed with fclose.i. The function prepares the necessary data for sending an email via SendGrid’s API. You need to replace 'YOUR_SENDGRID_API_KEY' with your actual SendGrid API key. The recipient email address is set to $to_email, and the sender email address is set to $from_email.

The subject and message of the email are also defined.j. The file content is read using file_get_contents and then base64 encoded with base64_encode to prepare it for attachment in the email.k. The payload for the SendGrid API is constructed as an array containing the necessary information like personalizations, from, content, and attachments.l. Headers for the API request are set, including the authorization token and content type.

The API request is made using wp_remote_post with the SendGrid API URL, payload, and headers.n. The response is checked for errors and the response code. If the email is sent successfully, a success message is echoed. Otherwise, an error message is logged.o. Finally, the temporary CSV file is deleted using unlink.

The add_action function is used to hook the generate_and_send_csv function to a specific WordPress action. In this example, it is hooked to admin_init, which is triggered when the admin panel is initialized. You can choose a different action based on your requirements.

By using this code, the generate_and_send_csv function will be executed when the specified WordPress action is triggered, allowing you to read the invoices table, generate a CSV file, and send it via email using SendGrid’s API.

Sign up today for our weekly newsletter about AI, SEO, and Entrepreneurship

Leave a Reply

Your email address will not be published. Required fields are marked *


Read Next




© 2024 Menyu LLC