I have the following chart on the website of a cycling club:
CREATE TABLE `mileage` ( `id` int (11) NOT NULL AUTO_INCREMENT, `rideID` mediumint (9) NOT NULL, `rideDate` date NOT NULL, `isWeekly` tinyint (1) NOT NULL, `riderUid` smallint (6) NOT NULL, `leaderUid` smallint (6) NOT NULL, `mileage` smallint (6) NOT NULL, `days` smallint (6) NOT NULL DEFAULT & # 39; 1 & # 39; `eBike` tinyint (1) NOT NULL DEFAULT & # 39; 0 & # 39; PRIMARY KEY (`id`) ) ENGINE = MyISAM AUTO_INCREMENT = 8081 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci
Each registration represents a runner / trip, where the runner is the person who flew and the trip is the runner's part of a club ride.
During the construction of the site, the club wanted to be able to obtain the mileage between the dates (by default, the current month or the cumulation since one year).
The following query works well for this application (variables come from PHP):
SELECT m.riderUid, w.firstName, w.lastName, w.isMale, SUM (mileage) in miles, COUNT (*) as cnt OF `mileage` m JOIN LEFT `members` w ON (w.uid = m.riderUid) WHERE (m.rideDate> = $ startDate AND m.rideDate <$ endDate>) GROUP BY m.riderUid ORDER BY DESC miles
Now they asked to get the data Month by month for a given pair of dates. Which means that they want to see the columns released for, for example, January, February, March, and so on.
I guess I can do it iteratively, using PHP to feed the successive monthly start and end dates, and then dragging them all together on the page, but that sounds pretty awkward. I wonder if there is a simpler way to use a MySQL query.