Displaying csv content in key value format and storing in a database

Question

Hi,

I have used following code for retrieving content from a csv file.

<?php
 $fo = fopen("records.csv", "r+");
 while(!feof($fo)) {
      $contents[] = fgetcsv($fo,0,',');
 }
 print_r($contents);
 fclose($fo);

?>

It returns me following:

Array ( [0] => Array ( [0] => Search Transactions Results ) [1] => Array ( [0] => Transaction ID [1] => Reference Transaction ID [2] => Date [3] => Type [4] => Subject [5] => Item Number [6] => Item Name [7] => Invoice ID [8] => Name [9] => Email [10] => Shipping Name [11] => Shipping Address Line 1 [12] => Shipping Address Line 2 [13] => Shipping Address City [14] => Shipping State/Province [15] => Shipping Zip/Postal Code [16] => Shipping Address Country [17] => Shipping Method [18] => Address Status [19] => Contact Phone Number [20] => Gross Amount [21] => Receipt ID [22] => Custom Field [23] => Option 1 Name [24] => Option 1 Value [25] => Option 2 Name [26] => Option 2 Value [27] => Note [28] => Auction Site [29] => Auction User ID [30] => Item URL [31] => Auction Closing Date [32] => Insurance Amount [33] => Currency [34] => Fees [35] => Net Amount [36] => Shipping & Handling Amount [37] => Sales Tax Amount [38] => To Email [39] => Time [40] => Time Zone ) [2] => Array ( [0] => 1T [1] => [2] => 5/5/2010 2:10:44 PM [3] => Payment Processed [4] => CFP Self Study Kit [5] => 1 [6] => CFP Self Study Kit [7] => [8] => User1 [9] => [email protected] [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => N [19] => [20] => 68.18 [21] => R1 [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => USD [34] => -2.62 [35] => 65.56 [36] => 0 [37] => 0 [38] => [email protected] [39] => 01:40 [40] => Asia/Calcutta ) [3] => Array ( [0] => 2T [1] => [2] => 5/19/2010 4:04:08 PM [3] => Payment Processed [4] => CFP Self Study Kit [5] => 1 [6] => CFP Self Study Kit [7] => [8] => User2 [9] => [email protected] [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => N [19] => [20] => 68.18 [21] => R2 [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => USD [34] => -2.62 [35] => 65.56 [36] => 0 [37] => 0 [38] => [email protected] [39] => 03:34 [40] => Asia/Calcutta ) [4] => Array ( [0] => 3T [1] => 1RT [2] => 5/19/2010 5:28:45 PM [3] => Currency Conversion Completed [4] => [5] => [6] => [7] => [8] => [9] => [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => N [19] => [20] => 17492.6 [21] => [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => INR [34] => 0 [35] => 17492.6 [36] => 0 [37] => 0 [38] => [39] => 04:58 [40] => Asia/Calcutta ) [5] => Array ( [0] => 4T [1] => 2RT [2] => 5/19/2010 5:28:45 PM [3] => Currency Conversion Completed [4] => [5] => [6] => [7] => [8] => [9] => [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => N [19] => [20] => -393.36 [21] => [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => USD [34] => 0 [35] => -393.36 [36] => 0 [37] => 0 [38] => [39] => 04:58 [40] => Asia/Calcutta ) [6] => Array ( [0] => 5T [1] => [2] => 5/19/2010 5:28:45 PM [3] => Transfer to Bank Initiated [4] => P1006 [5] => [6] => P1006 [7] => [8] => [9] => [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => N [19] => [20] => -17492.6 [21] => [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => INR [34] => 0 [35] => -17492.6 [36] => 0 [37] => 0 [38] => [39] => 04:58 [40] => Asia/Calcutta ) [7] => Array ( [0] => 6T [1] => [2] => 5/20/2010 5:38:02 PM [3] => Transfer to Bank Completed [4] => P1006 [5] => [6] => P1006 [7] => [8] => [9] => [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => N [19] => [20] => -17492.6 [21] => [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => INR [34] => 0 [35] => -17492.6 [36] => 0 [37] => 0 [38] => [39] => 05:08 [40] => Asia/Calcutta ) [8] => Array ( [0] => 7T [1] => [2] => 5/21/2010 12:32:37 PM [3] => Payment Processed [4] => FP - LVC Plus [5] => [6] => FP - LVC Plus [7] => [8] => User3 [9] => [email protected] [10] => User3 [11] => NEW DELHI [12] => BEHIND KARNATAKA BANK LD [13] => SOUTH [14] => NEW DELHI [15] => 110023 [16] => IN [17] => [18] => N [19] => [20] => 283.96 [21] => [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => USD [34] => -9.95 [35] => 274.01 [36] => 0 [37] => 0 [38] => [email protected] [39] => 00:02 [40] => Asia/Calcutta ) [9] => Array ( [0] => 8T [1] => [2] => 5/25/2010 4:40:48 PM [3] => Transfer to Bank Initiated [4] => P1006 [5] => [6] => P1006 [7] => [8] => [9] => [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => N [19] => [20] => -12569.85 [21] => [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => INR [34] => 0 [35] => -12569.85 [36] => 0 [37] => 0 [38] => [39] => 04:10 [40] => Asia/Calcutta ) [10] => Array ( [0] => 9T [1] => 3RT [2] => 5/25/2010 4:40:48 PM [3] => Currency Conversion Completed [4] => [5] => [6] => [7] => [8] => [9] => [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => N [19] => [20] => -274.01 [21] => [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => USD [34] => 0 [35] => -274.01 [36] => 0 [37] => 0 [38] => [39] => 04:10 [40] => Asia/Calcutta ) [11] => Array ( [0] => 10T [1] => 4RT [2] => 5/25/2010 4:40:48 PM [3] => Currency Conversion Completed [4] => [5] => [6] => [7] => [8] => [9] => [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => N [19] => [20] => 12569.85 [21] => [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => INR [34] => 0 [35] => 12569.85 [36] => 0 [37] => 0 [38] => [39] => 04:10 [40] => Asia/Calcutta ) [12] => Array ( [0] => 11T [1] => [2] => 5/26/2010 4:57:39 PM [3] => Transfer to Bank Completed [4] => P1006 [5] => [6] => P1006 [7] => [8] => [9] => [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => N [19] => [20] => -12569.85 [21] => [22] => [23] => [24] => [25] => [26] => [27] => [28] => [29] => [30] => [31] => [32] => [33] => INR [34] => 0 [35] => -12569.85 [36] => 0 [37] => 0 [38] => [39] => 04:27 [40] => Asia/Calcutta ) [13] => Array ( [0] => Total [1] => -247.05 USD [2] => -15.19 [3] => -262.24 ) [14] => Array ( [0] => Total [1] => 0.00 INR [2] => 0.00 [3] => 0.00 ) [15] => ) 

But the problem is i wanted it in a following key value format for e.g.

Transaction ID=>1T where Transaction ID is heading and 1T is the value & that value i have to store in a database.One more thing i have to retrieve only those records whose Type is Payment Processed as can be seen below from the csv file:

My csv file :

"Search Transactions Results"
"Transaction ID","Reference Transaction ID","Date","Type","Subject","Item Number","Item Name","Invoice ID","Name","Email","Shipping Name","Shipping Address Line 1","Shipping Address Line 2","Shipping Address City","Shipping State/Province","Shipping Zip/Postal Code","Shipping Address Country","Shipping Method","Address Status","Contact Phone Number","Gross Amount","Receipt ID","Custom Field","Option 1 Name","Option 1 Value","Option 2 Name","Option 2 Value","Note","Auction Site","Auction User ID","Item URL","Auction Closing Date","Insurance Amount","Currency","Fees","Net Amount","Shipping & Handling Amount","Sales Tax Amount","To Email","Time","Time Zone"
"1T","",5/5/2010 2:10:44 PM,"Payment Processed","CFP Self Study Kit","1","CFP Self Study Kit","","User1","[email protected]","","","","","","","","","N","","68.18","R1","","","","","","","","","",,"","USD","-2.62","65.56","0","0","[email protected]","01:40","Asia/Calcutta"
"2T","",5/19/2010 4:04:08 PM,"Payment Processed","CFP Self Study Kit","1","CFP Self Study Kit","","User2","[email protected]","","","","","","","","","N","","68.18","R2","","","","","","","","","",,"","USD","-2.62","65.56","0","0","[email protected]","03:34","Asia/Calcutta"
"3T","1RT",5/19/2010 5:28:45 PM,"Currency Conversion Completed","","","",""," ","","","","","","","","","","N","","17492.6","","","","","","","","","","",,"","INR","0","17492.6","0","0","","04:58","Asia/Calcutta"
"4T","2RT",5/19/2010 5:28:45 PM,"Currency Conversion Completed","","","",""," ","","","","","","","","","","N","","-393.36","","","","","","","","","","",,"","USD","0","-393.36","0","0","","04:58","Asia/Calcutta"
"5T","",5/19/2010 5:28:45 PM,"Transfer to Bank Initiated","P1006","","P1006",""," ","","","","","","","","","","N","","-17492.6","","","","","","","","","","",,"","INR","0","-17492.6","0","0","","04:58","Asia/Calcutta"
"6T","",5/20/2010 5:38:02 PM,"Transfer to Bank Completed","P1006","","P1006",""," ","","","","","","","","","","N","","-17492.6","","","","","","","","","","",,"","INR","0","-17492.6","0","0","","05:08","Asia/Calcutta"
"7T","",5/21/2010 12:32:37 PM,"Payment Processed","FP - LVC Plus","","FP - LVC Plus","","User3","[email protected]","User3","NEW DELHI","BEHIND KARNATAKA BANK LD","SOUTH","NEW DELHI","110023","IN","","N","","283.96","","","","","","","","","","",,"","USD","-9.95","274.01","0","0","[email protected]","00:02","Asia/Calcutta"
"8T","",5/25/2010 4:40:48 PM,"Transfer to Bank Initiated","P1006","","P1006",""," ","","","","","","","","","","N","","-12569.85","","","","","","","","","","",,"","INR","0","-12569.85","0","0","","04:10","Asia/Calcutta"
"9T","3RT",5/25/2010 4:40:48 PM,"Currency Conversion Completed","","","",""," ","","","","","","","","","","N","","-274.01","","","","","","","","","","",,"","USD","0","-274.01","0","0","","04:10","Asia/Calcutta"
"10T","4RT",5/25/2010 4:40:48 PM,"Currency Conversion Completed","","","",""," ","","","","","","","","","","N","","12569.85","","","","","","","","","","",,"","INR","0","12569.85","0","0","","04:10","Asia/Calcutta"
"11T","",5/26/2010 4:57:39 PM,"Transfer to Bank Completed","P1006","","P1006",""," ","","","","","","","","","","N","","-12569.85","","","","","","","","","","",,"","INR","0","-12569.85","0","0","","04:27","Asia/Calcutta"
"Total","-247.05 USD","-15.19","-262.24"
"Total","0.00 INR","0.00","0.00"

I am completely stuck how to accomplish this.Please help me on this

Thanks

Answer 1

You can use array_map and array_combine to turn all of your entries into a nice associative array:

 $headers = $contents[1];
 function mapper($arr) { global $headers; return array_combine($headers, $arr); }
 $contents = array_map('mapper', array_slice($contents, 2, -3));

This uses the second entry in the array (your column headers, as it were) as the keys. It also knocks off the first two rows (the two header lines) and the last three (the two total lines and the empty element at the end).

You can then filter by type by iterating over the array or using array_filter.

Alternatively, if you're using PHP >= 5.3 you can do the mapping function as a closure instead, which is a bit nicer:

 $headers = $contents[1];
 $contents = array_map(function ($arr) use ($headers) {
  return array_combine($headers, $arr);
 }, array_slice($contents, 2, -3));