Page 1 of 1
Compare data of two .csv file
Posted: Thu Jun 09, 2016 9:17 pm
by xkevin
As of now this is my code. I got stuck on how to look up/compare the data of payment.csv to transactions.csv.
Code: Select all
<?php
$masterData = array();
//this shows the name, email, deposit date and amount from payment.csv
$data = file("datas/payment.csv");
foreach ($data as $deposit){
$depositarray = explode(",", $deposit);
$depositlist = $depositarray;
$key = md5($depositlist[9] . $depositlist[10]); //date + amount
$masterData[$key]['payment'] = array(
'name' => $depositlist[0],
'email' => $depositlist[1],
'depositdate' => $depositlist[9],
'depositamount' => $depositlist[10]
);
}
//this shows the payment date and amount from transaction.csv
$databank = file("datas/transactions.csv");
foreach ($databank as $payment){
$paymentarray = explode(",", $payment);
$paymentlist = $paymentarray;
$key = md5($paymentlist[0] . $paymentlist[5]); //date + amount
$masterData[$key]['transaction'] = array(
'paymentdate' => $paymentlist[0],
'paymentamount' => $paymentlist[5]
}
Would it be possible to display/store in arrays all transaction that have multiple matched? Example: Payment A match to Transaction 1, 3, 5.. so on. Purpose is to view all possible match of a payment in transaction.
Re: Compare data of two .csv file
Posted: Thu Jun 09, 2016 11:03 pm
by requinix
Right now you're only allowing for one transaction per $key. Make ['transaction'] be an array, and add each matching transaction to it. So your data will look like
Code: Select all
array(
md5("3-May-20162000") => array(
"payment" => array(
"name" => "A",
"email" => "a@y.com",
"depositdate" => "3-May-2016",
"depositamount" => 2000
),
"transaction" => array(
array(
"paymentdate" => "3-May-2016",
"paymentamount" => 2000,
...
"transaction" => 1
),
array(
"paymentdate" => "3-May-2016",
"paymentamount" => 2000,
...
"transaction" => 3
)
)
)
)
Re: Compare data of two .csv file
Posted: Thu Jun 09, 2016 11:10 pm
by xkevin
requinix wrote:Right now you're only allowing for one transaction per $key. Make ['transaction'] be an array, and add each matching transaction to it. So your data will look like
Code: Select all
array(
md5("3-May-20162000") => array(
"payment" => array(
"name" => "A",
"email" => "a@y.com",
"depositdate" => "3-May-2016",
"depositamount" => 2000
),
"transaction" => array(
array(
"paymentdate" => "3-May-2016",
"paymentamount" => 2000,
...
"transaction" => 1
),
array(
"paymentdate" => "3-May-2016",
"paymentamount" => 2000,
...
"transaction" => 3
)
)
)
)
Yes, that should be the output. But where I can put that code? Inside the loop? Would you mind giving me reference. Arrays and loop is headache for me. Thank you!
Re: Compare data of two .csv file
Posted: Fri Jun 10, 2016 12:17 am
by requinix
Code: Select all
$masterData[$key]['transaction'] = array(
'paymentdate' => $paymentlist[0],
'paymentamount' => $paymentlist[5]
That is where you are putting the transaction data array into the main array. Instead of going into ['transaction'] directly, make ['transaction'] a plain array and then put the transaction data array into that.
Try writing something and see what happens. If you have problems, post your code and a description of what is going wrong.
Re: Compare data of two .csv file
Posted: Fri Jun 10, 2016 12:42 am
by xkevin
requinix wrote:Code: Select all
$masterData[$key]['transaction'] = array(
'paymentdate' => $paymentlist[0],
'paymentamount' => $paymentlist[5]
That is where you are putting the transaction data array into the main array. Instead of going into ['transaction'] directly, make ['transaction'] a plain array and then put the transaction data array into that.
Try writing something and see what happens. If you have problems, post your code and a description of what is going wrong.
Thank you for that. I just added []. $masterData[$key]['transaction'] [] = array

Re: Compare data of two .csv file
Posted: Fri Jun 10, 2016 12:18 pm
by Christopher
Yes, that is probably the way to do it. I noticed a couple things about your code. First, md5(date+amount) is not a very unique value -- you should probably use md5(name+email+date+amount). Otherwise you will have transactions that had the same amounts on the same day overwritten. Second, the date is redundant in the transaction data. It just takes up memory if you will have large arrays.
Re: Compare data of two .csv file
Posted: Sun Jun 12, 2016 7:20 pm
by xkevin
Christopher wrote:Yes, that is probably the way to do it. I noticed a couple things about your code. First, md5(date+amount) is not a very unique value -- you should probably use md5(name+email+date+amount). Otherwise you will have transactions that had the same amounts on the same day overwritten. Second, the date is redundant in the transaction data. It just takes up memory if you will have large arrays.
-I intended to do it like that because my primary purpose is to display all possible match (date and amount) of transaction list. So all transaction that has match in payment will display. But my last problem is on how to display the all the matched array on top. Or be separated from no matched.
Re: Compare data of two .csv file
Posted: Mon Jun 13, 2016 7:51 pm
by Christopher
There are several ways to do what you want. Perhaps the simplest would be to put the values into two different arrays in your loop -- one array for matched and one array for unmatched. Is there a reason they need to be in on array. If so then use the array sort method with a callback.
Re: Compare data of two .csv file
Posted: Mon Jun 13, 2016 10:52 pm
by xkevin
Christopher wrote:There are several ways to do what you want. Perhaps the simplest would be to put the values into two different arrays in your loop -- one array for matched and one array for unmatched. Is there a reason they need to be in on array. If so then use the array sort method with a callback.
I already fixed it. But I have bottleneck now. I want to sort the array by date, so that when the data was display n table it is sorted by date ascending.
Try adding this but no success. It just display base on the order data of the payment.csv file.
Code: Select all
function cmp($date, $date)
{
if ($date == $date) {
return 0;
}
return ($date < $date) ? -1 : 1;
}
$masterData = array();
//this shows the name, email, deposit date and amount from payment.csv
$data = file("datas/payments.csv");
foreach ($data as $deposit){
$depositarray = explode(",", $deposit);
$depositlist = $depositarray;
$key = md5($depositlist[9] . number_format($depositlist[10],2)); //date + amount
$masterData[$key]['payment'] = array(
'name' => $depositlist[0],
'email' => $depositlist[1],
'depositdate' => $depositlist[9],
'depositamount' => number_format($depositlist[10],2)
);
}
$date = $masterData[$key]['payment']['depositdate'];
usort($date,"cmp");
Re: Compare data of two .csv file
Posted: Tue Jun 14, 2016 7:05 pm
by Christopher
Use usort() to sort an array based on your special criteria.
Re: Compare data of two .csv file
Posted: Tue Jun 14, 2016 7:44 pm
by xkevin
Christopher wrote:Use usort() to sort an array based on your special criteria.
I'm using it on my code. But seems like it doesn't work..
Re: Compare data of two .csv file
Posted: Wed Jun 15, 2016 3:41 pm
by Christopher
You have to compare the sub-array elements you want to sort on -- not just array vs array. See the documentation for usort().
Re: Compare data of two .csv file
Posted: Wed Jun 15, 2016 6:45 pm
by xkevin
ok thank you. after a couple of hours I made it worked.