Sha Sha - 3 months ago 15
PHP Question

Multidimensional array and aggregate functions in MySQL using PHP?

UPDATED


How to display 1 more column next to that u2 called CUMULATIVE TOTAL

it should display total number of students total payable total paid and total due based on counsellors.


Consider i have c1,c2,c3,c4 as counsellors and u1,u2 as universities
say c1 has 5 student in each university in that case CUMULATIVE TOTAL column should show total number of students column as [c1][No of students]=10, [c1][Payable]=some value, [c1][Paid]=some value, [c1][Balence]=some value

enter image description here



Please check the following code and let me know is there any way to write select query inside that SUM aggregate function or any alternate solution because i want wll_invoice.total_payable should group by 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>

Answer

I hope this is the code you are looking after:

<?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>

I have used following query where I am using Union to append overall data by counsellor as well that you are looking for. Also if you have noticed in code, there I have appended a cumulative university object to the university list to process it same loop.

(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`)

Try using this query for distinct value, but you really need to update your schema. This is just a temporary solution:

(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`)