selecting distinct items and listing vehicles last cleaned based on criteria

[PatrickO]

Hi all, i've drawn a blank on how to do this task. Something I did years ago, but the brain ain't engaging gear.

I have two tables, one is vehicle cleaning and the other is vehicle specs.

I need a report where it'll select the distinct vehicles from the vehicle specs table, find the last time the vehicle was cleaned from the vehicle cleaning table based on a specific criteria, and refer back to vehicle_specs for the column of frequency to display whether the vehicle has been cleaned within the set time frame.

[requinix]

Seeing the two table schemas and some examples of what data you have and what you want to get from it would nice.

[PatrickO]

Is this what you require?

 

INSERT INTO `vehicle_cleaning` (`id`, `bus_no`, `date_cleaned`, `cleaner`, `washed`, `headtail`, `surfaces`, `rails`, `seats`, `cabin`, `windows`, `mop`, `tyres`, `washer`, `acfilter`, `additional`, `repairs`, `defects`) VALUES
(18, 'DV-77', '2022-04-18', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(19, 'EV-12', '2022-04-21', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(20, 'DV-19', '2022-04-22', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', '', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', 'Requires A/C Filter (none fitted)'),
(21, 'DV-82', '2022-04-22', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', '', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(22, 'DV-62', '2022-04-17', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', '');
 

 

INSERT INTO `vehicle_specs` (`id`, `bus_no`, `date_inservice`, `date_outservice`, `rego`, `type`, `frequency`) VALUES
(1, 'EV-01', '2018-01-01', '0000-00-00', 'XQ63MV', 'Electric | Rigid | 3 door', 5),
(2, 'EV-02', '2018-01-01', '0000-00-00', '914XZP', 'Electric | Rigid | 3 door', 5),
(3, 'EV-03', '2018-01-01', '0000-00-00', '913XZP', 'Electric | Rigid | 3 door', 5),
(4, 'EV-04', '2018-01-01', '0000-00-00', '915XZP', 'Electric | Rigid | 3 door', 5),
(5, 'EV-05', '2018-01-01', '0000-00-00', '916XZP', 'Electric | Rigid | 3 door', 5);

[PatrickO]

Sorry, this is a better snapsnot of what it needs to do.

 

INSERT INTO `vehicle_cleaning` (`id`, `bus_no`, `date_cleaned`, `cleaner`, `washed`, `headtail`, `surfaces`, `rails`, `seats`, `cabin`, `windows`, `mop`, `tyres`, `washer`, `acfilter`, `additional`, `repairs`, `defects`) VALUES
(18, 'EV-01, '2022-04-18', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(19, 'EV-02', '2022-04-21', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(20, 'EV-03', '2022-04-22', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', '', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', 'Requires A/C Filter (none fitted)'),
(21, 'EV-02', '2022-04-22', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', '', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(22, 'EV-05', '2022-04-17', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', '');
 

 

INSERT INTO `vehicle_specs` (`id`, `bus_no`, `date_inservice`, `date_outservice`, `rego`, `type`, `frequency`) VALUES
(1, 'EV-01', '2018-01-01', '0000-00-00', 'XQ63MV', 'Electric | Rigid | 3 door', 5),
(2, 'EV-02', '2018-01-01', '0000-00-00', '914XZP', 'Electric | Rigid | 3 door', 5),
(3, 'EV-03', '2018-01-01', '0000-00-00', '913XZP', 'Electric | Rigid | 3 door', 5),
(4, 'EV-04', '2018-01-01', '0000-00-00', '915XZP', 'Electric | Rigid | 3 door', 5),
(5, 'EV-05', '2018-01-01', '0000-00-00', '916XZP', 'Electric | Rigid | 3 door', 15);

 

So what the script would do is look for the distinct bus_no in vehicle_cleaning, and report that:

EV-01 was last cleaned on 2022-04-18, and as it's more than 5 days since todays date that it was cleaned, it'll list the vehicle as requiring cleaning.
EV-02 was last cleaned on 2022-04-22, and as it's more than 5 days since todays date that it was cleaned, it'll list the vehicle as requiring cleaning.
EV-03 was last cleaned on 2022-04-22, and as it's more than 5 days since todays date that it was cleaned, it'll list the vehicle as requiring cleaning.
EV-05 was last cleaned on 2022-04-17, and as it's less than 15 days since todays date, it wouldn't show the vehicle is due to be cleaned.

[Barand]

25 minutes ago, requinix said:

Seeing the two table schemas and some examples of what data you have and what you want to get from it would nice.

Two-thirds of the way there.

What have you tried so far?

[PatrickO]

Nothing so far, that's where the mind has just gone blank.

 

I know the distinct is required but then referencing the last record for each vehicle cleaning date & whether its < or > than the frequency is where I draw a blank.

[Barand]

Try

SELECT v.bus_no
     , v.rego
     , COALESCE(last_cleaned, date_inservice) as last_cleaned
     , DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) as days_ago
     , v.frequency
     , CASE WHEN DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency
            THEN 'REQUIRED'
            ELSE 'OK'
            END as cleaning
FROM vehicle_specs v
     LEFT JOIN
     (
        SELECT bus_no
             , MAX(date_cleaned) as last_cleaned
        FROM vehicle_cleaning
        GROUP BY bus_no
     ) c USING (bus_no);
     
+--------+--------+--------------+----------+-----------+----------+
| bus_no | rego   | last_cleaned | days_ago | frequency | cleaning |
+--------+--------+--------------+----------+-----------+----------+
| EV-01  | XQ63MV | 2022-04-18   |       12 |         5 | REQUIRED |
| EV-02  | 914XZP | 2022-04-22   |        8 |         5 | REQUIRED |
| EV-03  | 913XZP | 2022-04-22   |        8 |         5 | REQUIRED |
| EV-04  | 915XZP | 2018-01-01   |     1580 |         5 | REQUIRED |
| EV-05  | 916XZP | 2022-04-17   |       13 |        15 | OK       |
+--------+--------+--------------+----------+-----------+----------+

Note EV-04 hasn't been cleaned since it came into service - so must be due by now!

[PatrickO]

15 minutes ago, Barand said:

SELECT v.bus_no
     , v.rego
     , COALESCE(last_cleaned, date_inservice) as last_cleaned
     , DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) as days_ago
     , v.frequency
     , CASE WHEN DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency
            THEN 'REQUIRED'
            ELSE 'OK'
            END as cleaning
FROM vehicle_specs v
     LEFT JOIN
     (
        SELECT bus_no
             , MAX(date_cleaned) as last_cleaned
        FROM vehicle_cleaning
        GROUP BY bus_no
     ) c USING (bus_no);

Wow, that worked better than I thought, and you've used terms i've never known, would hate to see what my code would've looked like.

Any chance you can convert that to php code to show the results?

[Barand]

Just this once

<?php
const HOST     = 'localhost';                                                          
const USERNAME = '????';                                                               
const PASSWORD = '????';                                                               
const DATABASE = 'test';               // default db                                  
                                                                                
function pdoConnect($dbname=DATABASE)                                                  
{                                                                                      
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                      
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);                 
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);                              
    return $db;                                                                        
}                                                                                      

$pdo = pdoConnect();
 
$result = $pdo->query("SELECT  v.bus_no as BusNo
                             , v.rego as RegNo
                             , COALESCE(last_cleaned, date_inservice) as last_cleaned
                             , DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) as days_ago
                             , v.frequency
                             , CASE WHEN DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency
                                    THEN 'REQUIRED'
                                    ELSE 'OK'
                                    END as cleaning
                        FROM vehicle_specs v
                             LEFT JOIN
                             (
                                SELECT bus_no
                                     , MAX(date_cleaned) as last_cleaned
                                FROM vehicle_cleaning
                                GROUP BY bus_no
                             ) c USING (bus_no)");
$row = $result->fetch();
$tdata = '';
$theads = "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";
do {
    $tdata .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
} while ($row = $result->fetch()) ;
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<head>
<title>Example</title>
<style type='text/css'>
    body {
        font-family: calibri, sans-serif;
        font-size: 12pt;
    }
    table {
        border-collapse: collapse;
        width: 600px;
    }
    th {
        background-color: black;
        color: white;
        padding: 8px;
    }
    td {
        padding: 4px 8px;
    }
</style>
</head>
<body>
    <table border='1'>
        <?= $theads ?>
        <?= $tdata ?>
    </table>
</body>
</html>

Edited April 30, 2022 by Barand
add output image

[PatrickO]

That was amazing, thank you. The report is working perfectly. Downside is I was getting a few "-1" in the days_ago field. I suspect its because the timezone line wasn't added to the page.

If I was to have the field names defined and only show just what is required, how do i put that into the script?

I'm thinking something like:
SELECT bus_no , MAX(date_cleaned) as last_cleaned FROM vehicle_cleaning GROUP BY bus_no ) c USING (bus_no) WHERE "requested"")

but i know that's not going to work.

[Barand]

The "days_ago" will evaluate to a negative number if the date_cleaned or date_inservice is in the future.

date_cleaned should always be historical (or it's an error) but it is feasible that there may be new buses in the table which come into service in the next few days.

We can check that only current buses are reported by

WHERE CURDATE() BETWEEN date_inservice AND IF(date_outservice=0, '9999-12-31', date_outservice)

If you only want to list buses where cleaning is required, the condition would be

WHERE DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency

If you only want to show the bus no and date_last_cleaned, only select those.

Then the query becomes

SELECT v.bus_no as 'Bus No'
     , COALESCE(last_cleaned, date_inservice) as 'Last Cleaned'
FROM vehicle_specs v
     LEFT JOIN
     (
        SELECT bus_no
             , MAX(date_cleaned) as last_cleaned
        FROM vehicle_cleaning
        GROUP BY bus_no
     ) c USING (bus_no)
WHERE CURDATE() BETWEEN date_inservice AND IF(date_outservice=0, '9999-12-31', date_outservice)
      AND DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency
      
+--------+--------------+
| Bus No | Last Cleaned |
+--------+--------------+
| EV-02  | 2022-04-22   |
| EV-03  | 2022-04-22   |
| EV-04  | 2018-01-01   |
+--------+--------------+

 

[PatrickO]

Worked like a pearler, thank you

 

One last question to close this one off, i've got 
date_default_timezone_set("Australia/Brisbane");
located in my index.php file, but until around 10 or 11am each day, the pre loaded date field shows the previous date. Am I doing something wrong?
 

[PatrickO]

Actually I have another question, and i've tried a couple of different ways all to no avail.

In mysql, this syntax works and displays the desired results of how many times a vehicle has been cleaned.
SELECT bus_no, COUNT(*) FROM vehicle_cleaning WHERE date_cleaned BETWEEN '2022-04-25' AND '2022-05-01' GROUP BY bus_no

but when I put it into php, it doesn't work.
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
//foreach($dbh->query('SELECT bus_no,date_cleaned COUNT(*) FROM vehicle_cleaning GROUP BY bus_no') as $row) {
foreach($dbh->query("SELECT * FROM `vehicle_cleaning` WHERE date_cleaned BETWEEN '2022-04-25' AND '2022-05-01' GROUP BY bus_no") as $row) {    
echo "<tr>";
echo "<td>" . $row['bus_no'] . "</td>";
echo "</tr>";
}

 

Ultimately, i'll be wanting to have a search criteria either by month or by week to get the results.

[Barand]

Have you tried it with COUNT(*) in php?

foreach($dbh->query("SELECT bus_no
                          , COUNT(*) as total 
                    FROM `vehicle_cleaning` 
                    WHERE date_cleaned BETWEEN '2022-04-25' AND '2022-05-01' 
                    GROUP BY bus_no") as $row) {    
    echo "<tr>";
    echo "<td>" . $row['bus_no'] . "</td>";
    echo "<td>" . $row['total'] . "</td>";
    echo "</tr>";
} 

Re your datetime problem, what is the timezone setting on your mysql server?

Edited May 2, 2022 by Barand

[PatrickO]

Sadly, that didn't work.

Here is the full code:
 

<table class='table table-bordered'>
<tr>
<th>Bus No</th><th>No of Cleans</th>
</tr>
<?php
$hostname="localhost";
$username="xxx";
$password="xxx";
$db = "xxx";

$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
//foreach($dbh->query('SELECT bus_no,date_cleaned COUNT(*) FROM vehicle_cleaning GROUP BY bus_no') as $row) {
foreach($dbh->query("SELECT bus_no
                          , COUNT(*) as total 
                    FROM `vehicle_cleaning` 
                    WHERE date_cleaned BETWEEN '2022-04-25' AND '2022-05-01' 
                    GROUP BY bus_no") as $row) {    
    echo "<tr>";
    echo "<td>" . $row['bus_no'] . "</td>";
    echo "<td>" . $row['total'] . "</td>";
    echo "</tr>";
} 
?>
</table>
 

Also unsure how to check timezone in mysql server. It's shared hosting if that helps.

[PatrickO]

Got it working, just having difficulty with the input boxes for the search:

I'm missing something, just not sure.

<form action="vehicle_cleaning-stats.php" method="post">
<input type="date" name="date_from"><input type="date" name="date_to"><input type="submit" value="Search">
</form>

$result = $pdo->query("SELECT bus_no, COUNT(*) as total FROM `vehicle_cleaning` WHERE date_cleaned BETWEEN '$date_from' AND '$date_to' GROUP BY bus_no");
 

[Barand]

Your input dates will be in the $_POST array.

Don't put user input data directly into your sql, use prepared queries.

 <form action="vehicle_cleaning-stats.php" method="post">
<input type="date" name="date_from"><input type="date" name="date_to"><input type="submit" value="Search">
</form>

php

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $stmt = $pdo->prepare("SELECT bus_no
                                , COUNT(*) as total
                           FROM `vehicle_cleaning` 
                           WHERE date_cleaned BETWEEN ? AND ? 
                           GROUP BY bus_no
                           ");
    $stmt->execute( [ $_POST['date_from'], $_POST['date_to'] ] );
    echo "<table>";
    foreach ($stmt as $row) {
        echo "<tr>";
        echo "<td>" . $row['bus_no'] . "</td>";
        echo "<td>" . $row['total'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
}