check multiple MYSQL rows of data from a value and email the result

user_19490868 0
Viewed 57

I have a column in MYSQL called Registration, Every Month a car check must be done. the user once the car check has been done will upload all the values online.

My Goal is to check if all Car Registrations have been done for the Month and if they Haven't send an Email to let them know that, that vehicle hasnt been checked, i am using cron job to run the code but that isnt the issue.

My problem is that when i go to check if all vehicle Registrations have been done. The emails that are responsible for checking check each row at a time. meaning even if all have been done a bunch of email will appear saying it has been done because not every registration was inputted into that singular row

I want the code to check the entire column and see if there are any registrations missing so that the emails will only send once for the column instead of once per row here is how my code is structured.

<?php

session_start();

$conn = mysqli_connect("localhost", "Username", "Password", "Database");

        require("PHPMailer/src/PHPMailer.php");
        require("PHPMailer/src/SMTP.php");
    
    
        $query = "SELECT Registration FROM vehiclecheck WHERE ID_carcheck";
        $query_run = mysqli_query($conn, $query);
   

    
if($query_run) {

       while($row = mysqli_fetch_assoc($query_run)) {

    if($row["Registration"] != "267AD7")
    {
    $mail = new PHPMailer\PHPMailer\PHPMailer();
    $mail->IsSMTP(); // enable SMTP
    
    $mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only
    $mail->SMTPAuth = true; // authentication enabled
    $mail->SMTPSecure = 'ssl'; // secure transfer enabled REQUIRED for Gmail
    $mail->Host = "atqldhub.com.au";
    $mail->Port = 465; // or 587
    $mail->IsHTML(true);
    $mail->Username = "info@atqldhub.com.au";
    $mail->Password = "5%226}Mh@Cdb";
    $mail->SetFrom("info@atqldhub.com.au");
    $mail->Subject = "Vehicle Checklist";
    $mail->Body = "A Vehicle has not been completed for this Month Registration, 267AD7 has not been Completed";
    $mail->AddAddress("alex.crabtree@atqld.com.au");
    $mail->send();
    }
    if($row["Registration"] != "244XST")
    {
    $mail = new PHPMailer\PHPMailer\PHPMailer();
    $mail->IsSMTP(); // enable SMTP
    
    $mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only
    $mail->SMTPAuth = true; // authentication enabled
    $mail->SMTPSecure = 'ssl'; // secure transfer enabled REQUIRED for Gmail
    $mail->Host = "atqldhub.com.au";
    $mail->Port = 465; // or 587
    $mail->IsHTML(true);
    $mail->Username = "info@atqldhub.com.au";
    $mail->Password = "5%226}Mh@Cdb";
    $mail->SetFrom("info@atqldhub.com.au");
    $mail->Subject = "Vehicle Checklist";
    $mail->Body = "A Vehicle has not been completed for this Month Registration, 244XST has not been Completed";
    $mail->AddAddress("alex.crabtree@atqld.com.au");
    $mail->send();
    }
    if($row["Registration"] != "572ZKK")
    {
    $mail = new PHPMailer\PHPMailer\PHPMailer();
    $mail->IsSMTP(); // enable SMTP
    
    $mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only
    $mail->SMTPAuth = true; // authentication enabled
    $mail->SMTPSecure = 'ssl'; // secure transfer enabled REQUIRED for Gmail
    $mail->Host = "atqldhub.com.au";
    $mail->Port = 465; // or 587
    $mail->IsHTML(true);
    $mail->Username = "info@atqldhub.com.au";
    $mail->Password = "5%226}Mh@Cdb";
    $mail->SetFrom("info@atqldhub.com.au");
    $mail->Subject = "Vehicle Checklist";
    $mail->Body = "A Vehicle has not been completed for this Month Registration, 572ZKK has not been Completed";
    $mail->AddAddress("alex.crabtree@atqld.com.au");
    $mail->send();
    }
    if($row["Registration"] != "072TVX")
    {
    $mail = new PHPMailer\PHPMailer\PHPMailer();
    $mail->IsSMTP(); // enable SMTP
    
    $mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only
    $mail->SMTPAuth = true; // authentication enabled
    $mail->SMTPSecure = 'ssl'; // secure transfer enabled REQUIRED for Gmail
    $mail->Host = "atqldhub.com.au";
    $mail->Port = 465; // or 587
    $mail->IsHTML(true);
    $mail->Username = "info@atqldhub.com.au";
    $mail->Password = "5%226}Mh@Cdb";
    $mail->SetFrom("info@atqldhub.com.au");
    $mail->Subject = "Vehicle Checklist";
    $mail->Body = "A Vehicle has not been completed for this Month Registration, 072TVX has not been Completed";
    $mail->AddAddress("alex.crabtree@atqld.com.au");
    $mail->send();
    }
    if($row["Registration"] != "348ZVB")
    {
    $mail = new PHPMailer\PHPMailer\PHPMailer();
    $mail->IsSMTP(); // enable SMTP
    
    $mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only
    $mail->SMTPAuth = true; // authentication enabled
    $mail->SMTPSecure = 'ssl'; // secure transfer enabled REQUIRED for Gmail
    $mail->Host = "atqldhub.com.au";
    $mail->Port = 465; // or 587
    $mail->IsHTML(true);
    $mail->Username = "info@atqldhub.com.au";
    $mail->Password = "5%226}Mh@Cdb";
    $mail->SetFrom("info@atqldhub.com.au");
    $mail->Subject = "Vehicle Checklist";
    $mail->Body = "A Vehicle has not been completed for this Month Registration, 348ZVB has not been Completed";
    $mail->AddAddress("alex.crabtree@atqld.com.au");
    $mail->send();
    }
    if($row["Registration"] != "066XXM")
    {
    $mail = new PHPMailer\PHPMailer\PHPMailer();
    $mail->IsSMTP(); // enable SMTP
    
    $mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only
    $mail->SMTPAuth = true; // authentication enabled
    $mail->SMTPSecure = 'ssl'; // secure transfer enabled REQUIRED for Gmail
    $mail->Host = "atqldhub.com.au";
    $mail->Port = 465; // or 587
    $mail->IsHTML(true);
    $mail->Username = "info@atqldhub.com.au";
    $mail->Password = "5%226}Mh@Cdb";
    $mail->SetFrom("info@atqldhub.com.au");
    $mail->Subject = "Vehicle Checklist";
    $mail->Body = "A Vehicle has not been completed for this Month Registration, 066XXM has not been Completed";
    $mail->AddAddress("alex.crabtree@atqld.com.au");
    $mail->send();
    }
    if($row["Registration"] != "063ZCD")
    {
    $mail = new PHPMailer\PHPMailer\PHPMailer();
    $mail->IsSMTP(); // enable SMTP
    
    $mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only
    $mail->SMTPAuth = true; // authentication enabled
    $mail->SMTPSecure = 'ssl'; // secure transfer enabled REQUIRED for Gmail
    $mail->Host = "atqldhub.com.au";
    $mail->Port = 465; // or 587
    $mail->IsHTML(true);
    $mail->Username = "info@atqldhub.com.au";
    $mail->Password = "5%226}Mh@Cdb";
    $mail->SetFrom("info@atqldhub.com.au");
    $mail->Subject = "Vehicle Checklist";
    $mail->Body = "A Vehicle has not been completed for this Month Registration, 063ZCD has not been Completed";
    $mail->AddAddress("alex.crabtree@atqld.com.au");
    $mail->send();
    }


     }
}
mysql php
1 Answers

Instead of making this possible in PHP code, you should be thinking about changing the SQL query

SELECT Registration FROM vehiclecheck 
WHERE Registration NOT IN ('267AD7', '244XST',
 '572ZKK', '072TVX', '348ZVB', '066XXM', '063ZCD')

Or you can create a table which holds all the code and meaning of each code something like table named RegistrationCode. Then write SQl as below

SELECT Registration FROM vehiclecheck 
WHERE Registration NOT IN 
(SELECT Code from RegistrationCode 
WHERE status = 'INVALID')

If more than one Registration entry of the same person is made then make the SQL query to check the date and last status of the registration done

SELECT Registration FROM vehiclecheck 
WHERE Registration R NOT IN 
(SELECT Code from RegistrationCode 
WHERE status = 'INVALID') 
AND RegistrationDate = 
(SELECT MAX(RegistrationDate ) from Registration S
WHERE S.CustomerId = R.CustomerId)

Then don't need a if condition at all to send email

Second MySQL table RegistrationCode

Code status
267AD7 INVALID
244XST INVALID
572ZKK INVALID
072TVX INVALID
348ZVB INVALID
066XXM INVALID
063ZCD INVALID

Left Join Example

SELECT V.Registration FROM vehiclecheck V 
LEFT JOIN RegistrationCode C ON V.Registration = C.Code 
WHERE C.status = 'INVALID' 
AND RegistrationDate = 
(SELECT MAX(RegistrationDate ) from Registration S
 WHERE S.CustomerId = V.CustomerId)

You can change your PHP code like this

$mail = new PHPMailer\PHPMailer\PHPMailer();
$mail->IsSMTP(); // enable SMTP
    
$mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only
$mail->SMTPAuth = true; // authentication enabled
$mail->SMTPSecure = 'ssl'; // secure transfer enabled REQUIRED for Gmail
$mail->Host = "atqldhub.com.au";
$mail->Port = 465; // or 587
$mail->IsHTML(true);
$mail->Username = "info@atqldhub.com.au";
$mail->Password = "5%226}Mh@Cdb";
$mail->SetFrom("info@atqldhub.com.au");
$mail->Subject = "Vehicle Checklist";
$mail->AddAddress("alex.crabtree@atqld.com.au");

$body = "";

if($query_run) {
    while($row = mysqli_fetch_assoc($query_run)) {
        $body = $body."<br/>A Vehicle has not been completed for this Month Registration, ".$row["Registration"]." has not been Completed";
    }
    $mail->Body = $body;
    $mail->send();
}

Now you have a single email send only once with accumulated body content inside the while loop

user_44891640
Related