PHP Lang
  1. PHP Lang
  3. Многомерный массив и агрегатные функции в MySQL с использованием PHP?
Intereting Posts
Laravel 4 и Eloquent ORM – Как выбрать последние 5 строк таблицы Проблема с запросом MySQL Только переменные могут передаваться по ошибке Как я могу использовать маршруты / подстановочные знаки в Laravel? Получение специальных символов из базы данных MySQL с помощью PHP Проверка Laravel не возвращает ошибку PHP: выполните ORDER BY, используя внешние данные? Метод Joomla 1.6 JCategories :: get () создает «PHP Fatal error: Allowed memory outed» в пользовательском компоненте MVC waitUntilObjectExists () Amazon S3 PHP SDK метод, как он работает? Math / Algorithm / JS: Как определить, пересекаются ли прямоугольники 2+, учитывая TopLeft (x0, y0) и Bottom-Right (x1, y1) каждого прямоугольника ZF2 отключает прослушиватель от EventManager .htaccess переписать переменные GET Изменить стандартную локаль в Symfony2 WordPress: как вызвать функцию плагина с помощью ajax-вызова? Программирование многоязычных PHP-приложений

Многомерный массив и агрегатные функции в MySQL с использованием PHP?

ОБНОВЛЕНО

Как отобразить еще 1 столбец рядом с этим u2, называемый CUMULATIVE TOTAL
он должен отображать общее количество учащихся, подлежащих выплате, общее уплаченное и общее вознаграждение на основе консультантов.

Рассмотрим, что у меня есть c1, c2, c3, c4 в качестве консультантов и u1, u2, поскольку университеты говорят, что c1 имеет 5 студентов в каждом университете. В этом случае столбец CUMULATIVE TOTAL должен показывать общее количество столбцов студентов как [c1] [No of students] = 10 , [c1] [Payable] = некоторое значение, [c1] [Paid] = некоторое значение, [c1] [Balence] = некоторое значение

введите описание изображения здесь

Пожалуйста, проверьте следующий код и сообщите мне, есть ли способ написать запрос выбора внутри этой совокупной функции SUM или любого альтернативного решения, потому что я хочу, чтобы wll_invoice.total_payable должен группироваться по customer_id. 

<?php define('DB_MAIN', 'localhost|user|passowd|database'); class my_db{ private static $databases; private $connection; public function __construct($connDetails){ if(!is_object(self::$databases[$connDetails])){ list($host, $user, $pass, $dbname) = explode('|', $connDetails); $dsn = "mysql:host=$host;dbname=$dbname"; self::$databases[$connDetails] = new PDO($dsn, $user, $pass); } $this->connection = self::$databases[$connDetails]; } public function fetchAll($sql){ $args = func_get_args(); array_shift($args); $statement = $this->connection->prepare($sql); $statement->execute($args); return $statement->fetchAll(PDO::FETCH_OBJ); } } $db = new my_db(DB_MAIN); $universities = $db->fetchAll('SELECT distinct customer_university FROM wll_customer'); $counselors = $db->fetchAll('SELECT distinct customer_counselor FROM wll_customer'); $payments_ = $db->fetchAll('SELECT customer_counselor, customer_university, COUNT(DISTINCT customer_name) AS \'no of students\', SUM(DISTINCT wll_invoice.total_payable) AS payable,**//I want to make total_payable should GROUP BY customer_id** SUM(wll_invoice.total_pay) AS paid, SUM(wll_invoice.due) AS balance FROM wll_customer LEFT JOIN wll_invoice ON wll_invoice.customer_id = wll_customer.customer_id GROUP BY customer_counselor,customer_university;'); $payments = []; foreach ($payments_ as $payment) $payments[$payment->customer_counselor][$payment->customer_university] = $payment; ?> <table id="table_id" class='display table-bordered'> <thead> <tr> <td rowspan="2">Sl</td> <td rowspan="2" >counselor</td> <?php foreach ($universities as $key => $university){ ?> <td colspan="4" ><?=$university->customer_university ?> </td> <?php } ?> </tr> <tr> <?php foreach ( $universities as $university){?> <td>no of students</td> <td>payable</td> <td>paid</td> <td>balance</td> <?php } ?> </tr> </thead> <tbody> <tr> <?php foreach ( $counselors as $counselor){?> <?php foreach ( $universities as $key => $university){ $payment = $payments[$counselor->customer_counselor][$university->customer_university]; ?> <?php if(!$key){?> <td></td> <td><?=$counselor->customer_counselor?></td> <?php } ?> <td><?=(int)$payment->{'no of students'}?></td> <td><?=number_format($payment->payable,0,',','')?></td> <td><?=number_format($payment->paid,0,',','')?></td> <td><?=number_format($payment->balance,0,',','')?></td> <?php } ?> </tr> <?php } ?> </tbody> </table>

Надеюсь, это код, который вы ищете: 

 <?php define('DB_MAIN', 'localhost|user|password|database'); class my_db{ private static $databases; private $connection; public function __construct($connDetails){ if(!is_object(self::$databases[$connDetails])){ list($host, $user, $pass, $dbname) = explode('|', $connDetails); $dsn = "mysql:host=$host;dbname=$dbname"; self::$databases[$connDetails] = new PDO($dsn, $user, $pass); } $this->connection = self::$databases[$connDetails]; } public function fetchAll($sql){ $args = func_get_args(); array_shift($args); $statement = $this->connection->prepare($sql); $statement->execute($args); return $statement->fetchAll(PDO::FETCH_OBJ); } } $db = new my_db(DB_MAIN); $universities = $db->fetchAll('SELECT distinct customer_university FROM wll_customer order by customer_university'); /** * Adding Cummulative university */ $cumulativeUniversity = new StdClass(); $cumulativeUniversity->customer_university = "CUMULATIVE TOTAL"; $universities[] = $cumulativeUniversity; $counselors = $db->fetchAll('SELECT distinct customer_counselor FROM wll_customer order by customer_counselor'); $payments_ = $db->fetchAll('(SELECT customer_counselor, customer_university, COUNT(distinct wll_invoice.customer_id) AS \'no of students\', SUM(wll_invoice.total_payable) AS payable, SUM(wll_invoice.total_pay) AS paid, SUM(wll_invoice.due) AS balance FROM wll_customer LEFT JOIN wll_invoice ON wll_invoice.customer_id = wll_customer.customer_id GROUP BY customer_counselor, customer_university order by `customer_counselor`, `customer_name`) UNION (SELECT customer_counselor, "CUMULATIVE TOTAL" as university, COUNT(distinct wll_invoice.customer_id) AS \'no of students\', SUM(wll_invoice.total_payable) AS payable, SUM(wll_invoice.total_pay) AS paid, SUM(wll_invoice.due) AS balance FROM wll_customer LEFT JOIN wll_invoice ON wll_invoice.customer_id = wll_customer.customer_id GROUP BY customer_counselor ORDER BY `customer_counselor`)'); $payments = []; foreach ($payments_ as $payment) $payments[$payment->customer_counselor][$payment->customer_university] = $payment; ?> <table id="table_id" class='display table-bordered' border="1"> <thead> <tr> <td rowspan="2" >Counselor</td> <?php foreach ($universities as $key => $university): ?> <td colspan="4" ><?=$university->customer_university ?> </td> <?php endforeach ?> </tr> <tr> <?php foreach ( $universities as $university): ?> <td>no of students</td> <td>payable</td> <td>paid</td> <td>balance</td> <?php endforeach ?> </tr> <?php foreach ( $counselors as $counselor):?> <tr> <td> <?php echo $counselor->customer_counselor;?> </td> <?php foreach ( $universities as $key => $university): $payment = isset($payments[$counselor->customer_counselor][$university->customer_university]) ? $payments[$counselor->customer_counselor][$university->customer_university] : null; if($payment):?> <td><?=(int)$payment->{'no of students'}?></td> <td><?=number_format($payment->payable,0,',','')?></td> <td><?=number_format($payment->paid,0,',','')?></td> <td><?=number_format($payment->balance,0,',','')?></td> <?php else:?> <td colspan="4"></td> <?php endif?> <?php endforeach; ?> </tr> <?php endforeach; ?> </thead> </table>

Я использовал следующий запрос, когда я использую Union для добавления общих данных консультантом, который вы ищете. Кроме того, если вы заметили в коде, я добавил кумулятивный университетский объект к списку университетов для обработки его в одном цикле. 

 (SELECT customer_counselor, customer_university, COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students', SUM(wll_invoice.total_payable) AS payable, SUM(wll_invoice.total_pay) AS paid, SUM(wll_invoice.due) AS balance FROM wll_customer LEFT JOIN wll_invoice ON wll_invoice.customer_id = wll_customer.customer_id GROUP BY customer_counselor, customer_university ORDER BY `customer_counselor`, `customer_name`) UNION (SELECT customer_counselor, "CUMULATIVE TOTAL" AS university, COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students', SUM(wll_invoice.total_payable) AS payable, SUM(wll_invoice.total_pay) AS paid, SUM(wll_invoice.due) AS balance FROM wll_customer LEFT JOIN wll_invoice ON wll_invoice.customer_id = wll_customer.customer_id GROUP BY customer_counselor ORDER BY `customer_counselor`)

Попробуйте использовать этот запрос для определенного значения, но вам действительно нужно обновить вашу схему. Это просто временное решение: 

 (SELECT customer_counselor, customer_university, COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students', SUM(wll_invoice.total_payable) AS payable, SUM(final_pay) AS paid, SUM(wll_invoice.total_payable - final_pay) AS balance FROM wll_customer LEFT JOIN (SELECT MAX(id) max_id, customer_id, SUM(total_pay) final_pay FROM `wll_invoice` GROUP BY customer_id, `total_payable`) AS wll_unique ON wll_unique.customer_id = wll_customer.`customer_id` LEFT JOIN wll_invoice ON wll_invoice.customer_id = wll_unique.customer_id AND `wll_invoice`.id = wll_unique.max_id GROUP BY customer_counselor, customer_university ORDER BY `customer_counselor`, `customer_name`) UNION (SELECT customer_counselor, "CUMULATIVE TOTAL" AS university, COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students', SUM(wll_invoice.total_payable) AS payable, SUM(final_pay) AS paid, SUM(wll_invoice.total_payable - final_pay) AS balance FROM wll_customer LEFT JOIN (SELECT MAX(id) max_id, customer_id, SUM(total_pay) final_pay FROM `wll_invoice` GROUP BY customer_id, `total_payable`) AS wll_unique ON wll_unique.customer_id = wll_customer.`customer_id` LEFT JOIN wll_invoice ON wll_invoice.customer_id = wll_unique.customer_id AND `wll_invoice`.id = wll_unique.max_id GROUP BY customer_counselor ORDER BY `customer_counselor`)

Ваш SQL должен группироваться customer_university, а также customer_counselor: 

 SELECT customer_counselor, customer_university, COUNT(customer_name) AS \'no of students\', SUM(wll_invoice.total_payable) AS payable, SUM(wll_invoice.total_pay) AS paid, SUM(wll_invoice.due) AS balance FROM wll_customer LEFT JOIN wll_invoice ON wll_invoice.customer_id = wll_customer.customer_id GROUP BY customer_counselor, customer_university