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

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.

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.

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?

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.

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?

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.

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.

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

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.