PHPize Online / SQLize Online  /  SQLtest Online

<?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($query); printf('DB version (Laravel Query Builder): %s ' . PHP_EOL, $version[0]->version); //---------------------------------------------- // get DB version using PDO function getDoleanceCounts1($pdo){ $query = "SELECT * FROM doleance;"; $stmt = $pdo->prepare($query); $stmt->execute(); $row = $stmt->fetchall(PDO::FETCH_ASSOC); return $row; } //print_r(getDoleanceCounts1($pdo)); function getDoleanceCounts($pdo, $byYear = false) { // Base SQL query $sql = " SELECT " . ($byYear ? "year AS yrar," : "0 AS yrar,") . " interet AS inte, SUM(CASE WHEN etat = 'traite' THEN 1 ELSE 0 END) AS trait, SUM(CASE WHEN etat = 'non traite' THEN 1 ELSE 0 END) AS non_trait, COUNT(*) AS total FROM doleance GROUP BY interet" . ($byYear ? ", year" : ""); $stmt = $pdo->prepare($sql); $stmt->execute(); $results = $stmt->fetchAll(PDO::FETCH_ASSOC); // Group by year or globally $finalResults = []; $globalCounts = []; foreach ($results as $row) { $year = $byYear ? $row['yrar'] : 0; $interet = $row['inte']; // Initialize the year array if it doesn't exist if (!isset($finalResults[$year])) { $finalResults[$year] = []; } // Add the row to the final results $finalResults[$year][] = $row; // Aggregate global counts if (!isset($globalCounts[$interet])) { $globalCounts[$interet] = ['trait' => 0, 'non_trait' => 0, 'total' => 0]; } $globalCounts[$interet]['trait'] += $row['trait']; $globalCounts[$interet]['non_trait'] += $row['non_trait']; $globalCounts[$interet]['total'] += $row['total']; } // Add the "all" counts foreach ($finalResults as $year => $rows) { $allCounts = ['yrar' => $year, 'inte' => 'all', 'trait' => 0, 'non_trait' => 0, 'total' => 0]; foreach ($rows as $row) { $allCounts['trait'] += $row['trait']; $allCounts['non_trait'] += $row['non_trait']; $allCounts['total'] += $row['total']; } $finalResults[$year][] = $allCounts; } // If not by year, return only the global counts if (!$byYear) { $finalResults[0] = []; foreach ($globalCounts as $interet => $counts) { $finalResults[0][] = [ 'yrar' => 0, 'inte' => $interet, 'trait' => $counts['trait'], 'non_trait' => $counts['non_trait'], 'total' => $counts['total'], ]; } // Add the global "all" counts $finalResults[0][] = [ 'yrar' => 0, 'inte' => 'all', 'trait' => array_sum(array_column($globalCounts, 'trait')), 'non_trait' => array_sum(array_column($globalCounts, 'non_trait')), 'total' => array_sum(array_column($globalCounts, 'total')), ]; } return json_encode(array_values($finalResults)); } // Global count without year details $globalCounts = getDoleanceCounts($pdo, false); echo $globalCounts; // Count detailed by year $yearlyCounts = getDoleanceCounts($pdo, true); echo $yearlyCounts;