CODEWITHSUNDEEP
phparraysmultidimensional-array
xitas
xitas

Reputation: 1164

Combine two values of a single array

How I can combine to records of a multidimensional array. I have merge two multidimensional arrays through array_merge after array merge I got a single array. In this array I have for elements receivable , sale_date , payablesand purchase_date.

This is my array:

Array
(
    [0] => Array
        ( [receivable] => 20 [sale_date] => 2014-06-24 )
    [1] => Array
        ( [receivable] => 400 [sale_date] => 2014-06-28 )
    [2] => Array
        ( [receivable] => 3410 [sale_date] => 2014-07-04 )
    [3] => Array
        ( [receivable] => 923 [sale_date] => 2014-07-07 )
    [4] => Array
        ( [receivable] => 1203 [sale_date] => 2014-08-15 )
    [5] => Array
        ( [payables] => 9800 [purchase_date] => 2014-06-23 )
    [6] => Array
        ([payables] => 0 [purchase_date] => 2014-06-24 )
    [7] => Array
        ( [payables] => 16155 [purchase_date] => 2014-06-28 )
    [8] => Array
        ( [payables] => 993 [purchase_date] => 2014-08-18 )
)

Now I want to check if sale_date and purchase_date are same then i get receivable and payables in a array. if they are not same then i get as it is. I have heve tryed many thing now I have hit a wall an d don't know what to do.

This is my desire result:

  Array
(
    [0] => Array
        ( [purchase_date] => 2014-06-24 [sale_date] => 2014-06-24 [receivable] => 20 [payables] => 0 )
    [1] => Array
        ( [sale_date] => 2014-06-28 [purchase_date] => 2014-06-28 [payables] => 16155 [receivable] => 400 )
    [2] => Array
        ( [receivable] => 3410 [sale_date] => 2014-07-04 )
    [3] => Array
        ( [receivable] => 923 [sale_date] => 2014-07-07 )
    [4] => Array
        ( [receivable] => 1203 [sale_date] => 2014-08-15 )
    [5] => Array
        ( [payables] => 9800 [purchase_date] => 2014-06-23 )
    [6] => Array
        ( [payables] => 993 [purchase_date] => 2014-08-18 )    
)

this is my php code:

$query_sale = "SELECT sum( `sale_received` ) AS receivable, `sale_date`
               FROM `sale`
               GROUP BY `sale_date`
               ORDER BY `sale_date` ";
$result_sale = mysqli_query($con,$query_sale);

$query_purchase = "SELECT sum( `purchase_remaining` ) AS payables, `date`
                   FROM `purchase`
                   GROUP BY `date`
                   ORDER BY `date`";
$result_purchase = mysqli_query($con,$query_purchase);

$array_receivable = array();
$array_payables = array();

while($sale = mysqli_fetch_array($result_sale)){
$receivable = $sale['receivable'];
$sale_date  = $sale['sale_date'];

$array_receivable[] = array('receivable' => $receivable , 'sale_date' => $sale_date);
}
while($purchase = mysqli_fetch_array($result_purchase)){
$payables   = $purchase['payables'];
$purchase_date = $purchase['date'];

$array_payables[] = array('payables' => $payables , 'purchase_date' => $purchase_date);
}
$both_array = array_merge($array_receivable , $array_payables);

Upvotes: 0

Views: 78

Answers (1)

Kez
Kez

Reputation: 760

It may be easier to write a SQL query that returns the data in this way..

for example:

SELECT (CASE WHEN B.date IS NULL THEN A.sale_date ELSE B.date END) As date, (CASE WHEN payables IS NOT NULL THEN payables ELSE 0 END) As payables,
    CASE WHEN receivable IS NOT NULL THEN receivable ELSE 0 END) As receivable
FROM (SELECT sum( sale_received ) AS receivable, sale_date
           FROM sale
           GROUP BY sale_date) A
LEFT JOIN    ( SELECT sum( purchase_remaining ) AS payables, date
               FROM purchase
               GROUP BY date ) B
ON A.sale_date = B.date
UNION
SELECT (CASE WHEN B.date IS NULL THEN A.sale_date ELSE B.date END) As date, (CASE WHEN payables IS NOT NULL THEN payables ELSE 0 END) As payables,
    CASE WHEN receivable IS NOT NULL THEN receivable ELSE 0 END) As receivable
FROM (SELECT sum( sale_received ) AS receivable, sale_date
           FROM sale
           GROUP BY sale_date) A
RIGHT JOIN    ( SELECT sum( purchase_remaining ) AS payables, date
               FROM purchase
               GROUP BY date ) B
ON A.sale_date = B.date

This should return a value for both payables and receivables for every date in both tables, and where there is only one value for a particular date, the other one will be a 0.

Maybe something like this in PHP:

$query =     "SELECT (CASE WHEN B.date IS NULL THEN A.sale_date ELSE B.date END) As date, (CASE WHEN payables IS NOT NULL THEN payables ELSE 0 END) As payables, ".
"    CASE WHEN receivable IS NOT NULL THEN receivable ELSE 0 END) As receivable ".
"FROM (SELECT sum( sale_received ) AS receivable, sale_date ".
"           FROM sale ".
"           GROUP BY sale_date) A ".
"LEFT JOIN    ( SELECT sum( purchase_remaining ) AS payables, date ".
"               FROM purchase ".
"               GROUP BY date ) B ".
"ON A.sale_date = B.date ".
"UNION ".
"SELECT (CASE WHEN B.date IS NULL THEN A.sale_date ELSE B.date END) As date, (CASE WHEN payables IS NOT NULL THEN payables ELSE 0 END) As payables, ".
"    CASE WHEN receivable IS NOT NULL THEN receivable ELSE 0 END) As receivable ".
"FROM (SELECT sum( sale_received ) AS receivable, sale_date ".
"           FROM sale ".
"           GROUP BY sale_date) A ".
"RIGHT JOIN    ( SELECT sum( purchase_remaining ) AS payables, date ".
"               FROM purchase ".
"               GROUP BY date ) B ".
"ON A.sale_date = B.date";

$result = mysqli_query($con,$query);

$both_array = [];

while($row = mysqli_fetch_array($result)){
    $date = $row['date'];
    $receivable = $row['receivable'];
    $payable  = $row['payables'];

    $both_array[] = array('date' => $date , 'receivable' => $receivable, 'payable' =>     $payables);
}

Upvotes: 2

Related Questions