Compare data of two .csv file

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
xkevin
Forum Newbie
Posts: 14
Joined: Thu Jun 09, 2016 9:01 pm

Compare data of two .csv file

Post 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.
Attachments
Capture.JPG
Last edited by xkevin on Thu Jun 09, 2016 11:05 pm, edited 1 time in total.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Compare data of two .csv file

Post 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
			)
		)
	)
)
xkevin
Forum Newbie
Posts: 14
Joined: Thu Jun 09, 2016 9:01 pm

Re: Compare data of two .csv file

Post 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!
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Compare data of two .csv file

Post 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.
xkevin
Forum Newbie
Posts: 14
Joined: Thu Jun 09, 2016 9:01 pm

Re: Compare data of two .csv file

Post 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 :D
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Compare data of two .csv file

Post 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.
(#10850)
xkevin
Forum Newbie
Posts: 14
Joined: Thu Jun 09, 2016 9:01 pm

Re: Compare data of two .csv file

Post 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.
shoulddisplay.JPG
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Compare data of two .csv file

Post 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.
(#10850)
xkevin
Forum Newbie
Posts: 14
Joined: Thu Jun 09, 2016 9:01 pm

Re: Compare data of two .csv file

Post 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");

User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Compare data of two .csv file

Post by Christopher »

Use usort() to sort an array based on your special criteria.
(#10850)
xkevin
Forum Newbie
Posts: 14
Joined: Thu Jun 09, 2016 9:01 pm

Re: Compare data of two .csv file

Post 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..
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Compare data of two .csv file

Post 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().
(#10850)
xkevin
Forum Newbie
Posts: 14
Joined: Thu Jun 09, 2016 9:01 pm

Re: Compare data of two .csv file

Post by xkevin »

ok thank you. after a couple of hours I made it worked.
Post Reply