[selecting distinct items and listing vehicles last cleaned based on criteria]

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");
 

[selecting distinct items and listing vehicles last cleaned based on criteria]

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.

[selecting distinct items and listing vehicles last cleaned based on criteria]

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.

[selecting distinct items and listing vehicles last cleaned based on criteria]

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?
 

[selecting distinct items and listing vehicles last cleaned based on criteria]

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.

[selecting distinct items and listing vehicles last cleaned based on criteria]

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?

[selecting distinct items and listing vehicles last cleaned based on criteria]

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.

[selecting distinct items and listing vehicles last cleaned based on criteria]

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.

[selecting distinct items and listing vehicles last cleaned based on criteria]

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);

[selecting distinct items and listing vehicles last cleaned based on criteria]

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.