How to count a specific data/variable in a txt file while comparing it to another txt or csv using PHP?

Question

I would like to ask for your help. I'd like to create a PHP script that would count the number of sales each affiliate has made and the total amount of their sales. There are two files involved (csv and txt). Here's the sample data of the CSV and TXT:

//Sales.csv
Customer Id,Name,Surname,ID Number,Type,Member Type,Amount,Lead Supplier,Type,Outcome
2759,Hlompho,Molakalaka,8305066090080,Debit Order,Subscriber,69,Company,New Sale,Sucessful
6884,Ndanganen,Tshidavhu,7902065808081,Debit Order,Subscriber,69,Company,New Sale,Sucessful
11583,Shoaib,Solomon,8308015072084,Debit Order,Subscriber,59,Company,Revenue,Sucessful
15507,Rathanambal,Govender,7312190242083,Debit Order,Subscriber,69,Company,Revenue,Sucessful
17456,Dumisani,Tshabalala,8406275675081,Debit Order,Subscriber,59,Company,Revenue,Sucessful
17640,Nomadlozi,Magagula,9201110277089,Debit Order,Subscriber,59,Company,Revenue,Sucessful
21545,Jason,Goosen,9003105124080,Debit Order,Subscriber,59,Company,Revenue,Sucessful
22001,Jacques,Kok,8503025107082,Debit Order,Subscriber,59,Company,Revenue,Sucessful
25137,Natelly,Erasmus,8803210085089,Debit Order,Subscriber,59,Company,Revenue,Sucessful
25431,Mfanufikile,Nala,8211085667082,Debit Order,Subscriber,59,Company,Revenue,Sucessful

//Report.txt
Date,Affiliate ID,Unique ID,Firstname,Lastname,ID Number,Phone,Email,Status,Reference Number
2013-05-01 0:05,1017,u104101722ne3001hj,Shoaib,Solomon,8308015072084,083-651-9877,mxoalarm.alarm@gmail.com,success=true;,146213
2013-05-01 1:58,1009,kf04100923fr3053vp,Hlompho,Molakalaka,8305066090080,0781257255,nkoanat@yahoo.com,success=true;,0
2013-05-01 1:58,1018,dv04101823c730573u,nasiera,hardy,8810160145086,082-346-6104,nasierahardy@yahoo.com,success=true;,146216
2013-05-01 2:00,1013,v04101323ks3057gp,Hlompho,Molakalaka,8305066090080,0763283541,pimpmybodytattoos@gmail.com,success=true;,146217
2013-05-01 2:01,1018,n504101823mh3059w2,zaid,slamdien,8409245240085,071-232-7059,zaid.slamdien@yahoo.com,success=true;,146218
2013-05-01 2:17,1013,pr0510130qy0116j4,zaid,slamdien,8409245240085,071-232-7059,zaid.slamdien@yahoo.com,success=true;,0
2013-05-01 2:18,1013,gd0510130pj0117rm,Dumisani,Tshabalala,8406275675081,082-346-6104,nasierahardy@yahoo.com,success=true;,0
2013-05-01 3:11,1013,oh05101312x0104gf,Lucky Magnificent,Mabena,8104165398084,072-758-4097,lucky.mabena@sasol.com,success=true;,0
2013-05-01 3:34,1001,8c0510011180132t,Nomadlozi,Magagula,9201110277089,084-028-7128,chaddo913@gmail.com,success=true

Basically, what the script would do is to get the ID number in every line of the csv file and then it will search for the first affiliate in the txt with the same ID number. (In case an ID number appears multiple times in the file, it will only be counted/credited to the first affiliate with that ID number) It will then count the number of sales that affiliate has made and the total of its sales. The affiliate will be determined by its Affiliate ID. In case an ID number does not match any affiliate in the txt, 1 will be used as the affiliate ID. Here's the sample output:

/* AffiliateID,NumberOfSales,TotalAmount */
1001,24,678
1009,72,328
1,3,144 //Sales with no match in the txt uses 1 as aff ID.

I've made several attempts to create the script but to no avail. I hope someone could help me out here. Thanks!

Update: I've decided to create two tables in the database: commissions and log in order to save the csv and txt data respectively. From here, I executed a MySQL query to get the output I wanted. Here's the code:

<?php

//Convert Spreadsheet (XLSX) to CSV
    require_once '../classes/PHPExcel/IOFactory.php';

    //Set maximum execution time to 1 hour.
    ini_set('max_execution_time', 3600);

    $excel = PHPExcel_IOFactory::load("Kudough Commision Report.xlsx");
    $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
    $writer->setDelimiter(",");
    $writer->setEnclosure("");
    $writer->setSheetIndex(1);
    $writer->save("commissions".date('Ym').".csv");

//Establish a MySQL Database Connection.
$con = mysqli_connect("localhost","root","","kudough");

    if (mysqli_connect_errno($con)) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

//Save the CSV to the database.
$file1 = "commissions".date('Ym').".csv"; //CSV filename

$handle1 = @fopen($file1, "r");
    if ($handle1) {
    while (($buffer1 = fgets($handle1, 4096)) !== false) {

        //Get Variables.
        $var1 = explode(",", $buffer1);
        $cid = @$var1[0];
        $fname = @str_replace("'","",$var1[1]);
        $lname = @str_replace("'","",$var1[2]);
        $idnumber = @$var1[3];
        $type = @$var1[4];
        $membertype = @$var1[5];
        $amount = @$var1[6];
        $leadsupplier = @$var1[7];
        $saletype = @$var1[8];
        $outcome = @$var1[9];

        $sql = "INSERT IGNORE INTO commissions (customer_id, name, surname, idnumber, type, member_type, amount, leadsupplier, saletype, outcome) VALUES ('$cid', '$fname', '$lname', '$idnumber', '$type', '$membertype', '$amount', '$leadsupplier', '$saletype', '$outcome')";

        if (!mysqli_query($con,$sql)) {
            die('Error: ' . mysqli_error($con));
        }
    }
    if (!feof($handle1)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle1);
    }

//Save the TXT to the database.
$file2 = "kudough201305.txt"; //TXT filename

$handle2 = @fopen($file2, "r");
    if ($handle2) {
    while (($buffer2 = fgets($handle2, 4096)) !== false) {

        //Get Variables.
        $var2 = explode(",", $buffer2);
        $logdate = @$var2[0];
        $aff_id = @$var2[1];
        $unique_id = @$var2[2];
        $fname = @$var2[3];
        $lname = @$var2[4];
        $idnumber = @$var2[5];
        $phone = @$var2[6];
        $email = @$var2[7];
        $trax = @$var2[8];
        $ref = @$var2[9];

        $sql = "INSERT IGNORE INTO log (logdate, aff_id, unique_id, fname, lname, idnumber, phone, email, trax, ref) VALUES ('$logdate', '$aff_id', '$unique_id', '$fname', '$lname', '$idnumber', '$phone', '$email', '$trax', '$ref')";

        if (!mysqli_query($con,$sql)) {
            die('Error: ' . mysqli_error($con));
        }
    }
    if (!feof($handle2)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle2);
    }

    //Count the number of sales and total amount of sales each affiliate has made.
$sql = "SELECT T2.aff_id AS affID, COUNT(T2.idnumber) AS numofsales, SUM(amount) AS totalsales FROM (SELECT aff_id, T1.idnumber, T1.amount FROM (SELECT * FROM commissions WHERE leadsupplier = 'IMUpstart' GROUP BY commissions.idnumber) AS T1 INNER JOIN log WHERE T1.idnumber = log.idnumber GROUP BY T1.idnumber) AS T2 GROUP BY T2.aff_id";

$rs = mysqli_query($con,$sql);

    while($row = mysqli_fetch_array($rs)) { 

        $affID = $row['affID'];
        $numofsales = $row['numofsales'];
        $totalsales = $row['totalsales'];

        //Write to CSV
        $entrydetails = $affID.", ".$numofsales.", ".$totalsales;
        WriteCSVFile($entrydetails);
    }

mysqli_close($con);
?>

I'm not quite sure of the MySQL statement if it's correct. My problem now is to find the total of all the sales of those ID numbers found in the csv but not in the txt. If you have any suggestions for improving the code, Please let me know. Thanks! :)

While it is unlikely that someone is going to write the whole thing for you, can you post what you have so far so we might offer suggestions? — adear11, Jun 11 '13 at 16:44
I forgot to include my code. I'll not include it anymore 'coz I've made another script using ZorleQ's suggestion below. — maikelsabido, Jun 13 '13 at 14:11

score 0 · Answer 1 · answered Jun 11 '13 at 16:46

Not going to write the whole code for you but basically you would have to go through each file line by line and split all the data into a multidimensional array (array[lineNumber][columnNumber]). Here is the code for that

$file = 'sales.csv'; //make this point to the file
$sales = array();
$file_handle = fopen($file, "r");
while (!feof($file_handle)) {
    $line = rtrim(fgets($file_handle));

}
fclose($file_handle);

This will create a $sales array of all the data in the sales. Then do the same for report.txt.

Then you want to do a loop in a loop to go through both arrays and check for all the stuff you need. Maybe someone will be nice and write out more of the code

for($i=0;$i<count($sales);$i++){
 for($j=0;$j<count($report);$j++){
  $saleID = $sales[$i][3]; // sales id number of current row
  $reportID = $report[$j][5]; //report id number of current row

  if($salesID == $reportID){
   //sales id and report id match

  }

 }
}

something like that. the rest you can fill in

Thanks. Your answer is very helpful. I'll try to complete the script using your solution. I've currently made a script using ZorleQ's suggestion above. — maikelsabido, Jun 13 '13 at 14:09

score 0 · Accepted Answer · answered Jun 11 '13 at 16:57

0

I'd like to suggest a different approach. You can map your CVS files directly to a MySQL table (docs). Once it's mapped, you can get all the numbers out in a single query. Something like:

SELECT  `AffiliateID` , COUNT( * ) 
FROM  `sales` 
GROUP BY  `AffiliateID` 
LIMIT 0 , 3000

The entire process should take so much less than trying to iterate through all the records int he file manually.

answered Jun 11 '13 at 16:57

ZorleQ

1,417
13
23

Thanks for your suggestion. I've actually thought of this before but I did not push this through because I thought it would take so much time inserting all of the records in the database. But since I can't seem to find other solutions, I've given this a try. Please see the code I've included above. – maikelsabido Jun 13 '13 at 14:16
Anything you can come up with yourself in PHP will most likely be slower than a heavily optimized database system, which has been designed for exactly such things. + Doing loops after loops of loops in low level C is definitely faster than PHP, so a database query should be a winner here. – ZorleQ Jun 13 '13 at 14:21
Thanks for your insights. Yes I've actually noticed that querying is much faster. The table for the txt file that I've made contains about 70,000 records but the query result returned fast. I've written a code above. Maybe you could help improve it. :) – maikelsabido Jun 13 '13 at 14:31
My only suggestion would be to drop completely the file parsing stage and load your CSV directly to mysql. Have a look at this: http://dev.mysql.com/doc/refman/5.1/en/load-data.html and: http://stackoverflow.com/a/11432767/486780 for details. That would save you so much time! – ZorleQ Jun 13 '13 at 14:36
Or, if you want to stick with your own parsing system, use transactions / prepared statements and batch your inserts into groups of 100 / 1000 etc to gain even more speed. – ZorleQ Jun 13 '13 at 14:37
I want to try the things you've suggested but I'm still a beginner and I definitely have to learn more about MySQL. I'll definitely try to incorporate your suggestions in my code. :) Can you give me a link on how to use transactions? – maikelsabido Jun 13 '13 at 14:45
Have a look at this link: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers That should give ou enough background into PDO and proper ways of connecting to MySQL. – ZorleQ Jun 13 '13 at 14:50

How to count a specific data/variable in a txt file while comparing it to another txt or csv using PHP?

2 Answers2