Making multiple queries repeatedly, cleaner code

[RonEskinder]

I'm trying to make my code cleaner, i have tried adding functions and calling them when i need to update or insert values, the problem is this will not work because yield needs to be inside a the first function and loop.

Any ideas on how to accomplish something similar but working

working.php

// Get all emails pending
Amp\Loop::run(function () {

    $date = date('Y-m-d H:i:s');
    //echo "Date: $date";
    $conn = "host=" . DB_HOST . " user=" . DB_USER . " password=" . DB_PASS . " dbname=" . DB_DATA;
    $config = ConnectionConfig::fromString($conn);
    $pool = Postgres\pool($config);

    // Get all mails not sent yet
    $statement = yield $pool->prepare("SELECT * FROM tc_mail WHERE status = 0 and \"dateSent\" is null;");
    $result = yield $statement->execute();

    while (yield $result->advance()) {

        // Get id and mail info
        $row = $result->getCurrent();
        $id = $row["id"];
        $mailSubject = $row["mailSubject"];
        $mailType = $row["mailType"];
        $body = $row["body"];
        $dateCreated = $row["dateCreated"];
        $extras = $row["extras"];
        $mailAddress = $row["mailAddress"];
        
        $mailAddress = explode(",", $mailAddress);

        // convert all emails to array
        $mailArray = array();
        foreach ($mailAddress as $address) {
            array_push($mailArray, new To($address));
        }

        // Send content to HTML template
        $file = __DIR__ . '/templates/general.php';
        $emailContent = array('content' => $body);
        $body = '';
        $body .= template($file, $emailContent);

        // Create new Email
        $email = new \SendGrid\Mail\Mail();
        $email->setFrom("info@mail.com", "TEST");
        $email->setSubject($mailSubject);
        $email->addTos($mailArray);
        $email->addContent(
            "text/html", $body
        );

        $sendgrid = new \SendGrid(SENDGRID_API_KEY);

        // Check for email Type
        if (isset($extras) && !empty($extras) && $mailType = "alert") {

            // Check if deviceid has emails from 30 minutes ago
            $statement = yield $pool->prepare("SELECT * from tc_mail WHERE extras = ? and status = 1 and \"dateSent\" > current_timestamp - interval '30 minutes' limit 1");
            $result2 = yield $statement->execute([$extras]);

            if (!yield $result2->advance()) {
                // Send email for alert
                $response = $sendgrid->send($email);

                if ($response->statusCode() == 202) {
                    // if correct update status=1
                    $statement = yield $pool->prepare('UPDATE "public"."tc_mail" SET "dateSent" = ?, "status" = ? WHERE "id" = ?');
                    yield $statement->execute([$date, 1, $id]);
                } else {

                    // if error update status=3
                    $statement = yield $pool->prepare('UPDATE "public"."tc_mail" SET "dateSent" = ?, "status" = ? WHERE "id" = ?');
                    yield $statement->execute([$date, 3, $id]);
                }

            } else {
                // Set status = 2 and dont send email
                $statement = yield $pool->prepare('UPDATE "public"."tc_mail" SET "dateSent" = ?, "status" = ? WHERE "id" = ?');
                yield $statement->execute([$date, 2, $id]);

            }
        } else {

            // Just send the email for other notifications
            // Send email for alert
            $response = $sendgrid->send($email);

            if ($response->statusCode() == 202) {
                // if correct update status=1
                $statement = yield $pool->prepare('UPDATE "public"."tc_mail" SET "dateSent" = ?, "status" = ? WHERE "id" = ?');
                yield $statement->execute([$date, 1, $id]);
            } else {

                // if error update status=3
                $statement = yield $pool->prepare('UPDATE "public"."tc_mail" SET "dateSent" = ?, "status" = ? WHERE "id" = ?');
                yield $statement->execute([$date, 3, $id]);
            }
        }
    }

    if (isset($pool)) {
        $pool->close();
    }

});

function template($file, $args)
{
    // ensure the file exists
    if (!file_exists($file)) {
        return "File $file does not exists.";
    }

    // Make values in the associative array easier to access by extracting them
    if (is_array($args)) {
        extract($args);
    }

    // buffer the output (including the file is "output")
    ob_start();
    include $file;
    return ob_get_clean();
}

cleaner_not_working.php

use Amp\Postgres;
use Amp\Postgres\ConnectionConfig;
use SendGrid\Mail\To;

// Get all emails pending

Amp\Loop::run(function () {

    $date = date('Y-m-d H:i:s');
    //echo "Date: $date";
    $conn = "host=" . DB_HOST . " user=" . DB_USER . " password=" . DB_PASS . " dbname=" . DB_DATA;
    $config = ConnectionConfig::fromString($conn);
    $pool = Postgres\pool($config);

    // Get all mails not sent yet
    $statement = yield $pool->prepare("SELECT * FROM tc_mail WHERE status = 0 and \"dateSent\" is null;");
    $result = yield $statement->execute();

    while (yield $result->advance()) {

        // Get id and mail info
        $row = $result->getCurrent();
        $id = $row["id"];
        $mailSubject = $row["mailSubject"];
        $mailType = $row["mailType"];
        $body = $row["body"];
        $dateCreated = $row["dateCreated"];
        $extras = $row["extras"];
        $mailAddress = $row["mailAddress"];
        $mailAddress = explode(",", $mailAddress);

        // convert all emails to array
        $mailArray = array();
        foreach ($mailAddress as $address) {
            array_push($mailArray, new To($address));
        }

        // Send content to HTML template
        $file = __DIR__ . '/templates/general.php';
        $emailContent = array('content' => $body);
        $body = '';
        $body .= template($file, $emailContent);

        // Create new Email
        $email = new \SendGrid\Mail\Mail();
        $email->setFrom("info@email.com", "TEST");
        $email->setSubject($mailSubject);
        $email->addTos($mailArray);
        $email->addContent(
            "text/html", $body
        );

        $sendgrid = new \SendGrid(SENDGRID_API_KEY);

        // Check for email Type
        if (isset($extras) && !empty($extras) && $mailType = "alert") {

            // Check if deviceid has emails from 30 minutes ago
            if (check_previous_email_sent_minutes_ago($pool, $extras)) {

                // Set status = 2 and dont send email
                update_mail($pool, $date, 2, $id);

            } else {

                sendEmail($pool, $date, $id, $sendgrid, $email);
            }
        } else {

            sendEmail($pool, $date, $id, $sendgrid, $email);
        }
    }

    if (isset($pool)) {
        $pool->close();
    }
});

function update_mail($pool, $date, $status, $id)
{
    try {
        $statement = yield $pool->prepare('UPDATE "public"."tc_mail" SET "dateSent" = ?, "status" = ? WHERE "id" = ?');
        yield $statement->execute([$date, $status, $id]);

    } catch (Exception $e) {
        echo "ERROR UPDATING tc_mail: " . $e . "\n";
    }

}

function template($file, $args)
{
    // ensure the file exists
    if (!file_exists($file)) {
        return "File $file does not exists.";
    }

    // Make values in the associative array easier to access by extracting them
    if (is_array($args)) {
        extract($args);
    }

    // buffer the output (including the file is "output")
    ob_start();
    include $file;
    return ob_get_clean();
}

function check_previous_email_sent_minutes_ago($pool, $extras)
{
    //SELECT * FROM tc_mail tm where extras = ? and id not in (?) and dateSent > NOW() - INTERVAL '30 minutes' and status = 1 limit 1;
    $statement = yield $pool->prepare("SELECT * from tc_mail WHERE extras = ? and status = 1 and \"dateSent\" > current_timestamp - interval '30 minutes' limit 1");
    $result = yield $statement->execute([$extras]);

    return yield $result->advance();
}

function sendEmail($pool, $date, $id, $sendgrid, $email)
{
    $response =  $sendgrid->send($email);

    // check email sent correctly
    if ($response->statusCode() == 202) {

        // if correct update status=1
        $this->update_mail($pool, $date, 1, $id);
    } else {

        // if error update status=3
        $this->update_mail($pool, $date, 3, $id);
    }
}