create table mytable(id int, date datetime, project varchar(255), Duration int);
insert into mytable values(1, "2022-01-30", "Project 1", 3),
(2, "2022-01-30", "Project 1", 2),
(3, "2022-01-30", "Project 2", 4),
(4, "2022-01-29", "Project 3", 4),
(5, "2022-01-28", "Project 3", 3),
(6, "2022-01-28", "Project 3", 2);
SELECT SUM(Duration) AS duration
FROM mytable
GROUP BY date
<?php
use Carbon\Carbon;
$now = Carbon::now()->format('d/m/Y');
printf("Today is %s\nCurrent PHP version: %s \n\n", $now, phpversion());
$query = "SELECT VERSION() as version;";
// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
printf('DB version (PDO): %s ' . PHP_EOL, $row['version']);
// Run query using mysqli
$result = $mysqli->query($query);
$version = $result->fetch_object();
printf('DB version (mysqli): %s ' . PHP_EOL, $version->version);
// Select using Laravel
$version = $db::select($db::raw("SELECT VERSION() as version;"));
printf('DB version (Laravel Query Builder): %s ' . PHP_EOL, $version[0]->version);
$sql = "SELECT id, date, project, duration FROM mytable order by date desc";
$result = $mysqli->query($sql);
$last_date = null;
$subtotal = 0;
$total = 0;
if ($result->num_rows > 0) {
printf("Date Project Duration\n");
while($row = $result->fetch_assoc()) {
if ($last_date == null){
$last_date = $row["date"];
}
if( $last_date != $row["date"] )
{
printf("------------------------------------- \n");
printf("%s Total Hours %d Hours \n", $last_date, $subtotal);
printf("------------------------------------- \n");
$last_date = $row["date"];
$subtotal = 0;
}
printf( "%s %s %d Hours \n",
$row["date"],
$row["project"],
$row["duration"]
);
$subtotal += $row["duration"];
$total += $row["duration"];
}
printf("------------------------------------- \n");
printf("%s Total Hours %d Hours \n", $last_date, $subtotal);
printf(" Total Hours %d Hours \n", $total);
printf("------------------------------------- \n");
} else {
echo "0 results";
}