CODEWITHSUNDEEP
phpmysqldatabasecsvimport
Loko Web Design
Loko Web Design

Reputation: 491

PHP and MySQL: Import CSV using Column Value as table

I've put my current code (not much) on this page for your reference (minus the real password, of course): http://mafgiftshop.org/MAF/queue/MP/for-reference.php

    $delimiter = ',';
$db = new mysqli('localhost', 'mafgifts_mp', '******', 'mafgifts_mp');

if (($handle = fopen("mpdata.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
        foreach($data as $i => $content) {
            $data[$i] = $db->real_escape_string($content);
        }
        echo $data[$i]."";
        $db->query("INSERT INTO `MP` 
                      (GiftAmount,GoalAmount,GiftDate,FundID,FundDescription) 
                      VALUES('" . implode("','", $data) . "');");
    }
    fclose($handle);
}

//THE ABOVE CODE PROPERLY PUTS ALL THE DATA INTO THE MySQL DATABASE, BUT I'M NOT 
//ABLE TO DO THE CALCULATIONS I WANT AND INSERT ONLY THE RESULTING DATA.

So I have a CSV file with 5 columns of information. There are no blank values. For example:

GiftAmount,GoalAmount,GiftDate,FundID,FundDescription (this line is not in the CSV; it is for reference)

What I need to do is add the 'GiftAmount' column while the FundIDs are the same and place the result into a MySQL database. So, for the above data, here would be the resulting MySQL table:

I'm using PHP to accomplish this and the PHP file will be run in a Cron job daily because the CSV file will change daily based on new gifts to the different funds.

By the way, the CSV file currently has 8,794 lines and many, many FundIDs.

Upvotes: 0

Views: 1860

Answers (1)

Michael Marr
Michael Marr

Reputation: 2099

I would make the FundID a unique key in MYSQL, then use the INSERT INTO ... ON DUPLICATE KEY UPDATE query.

    $db->query("INSERT INTO `MP` (GiftAmount,GoalAmount,GiftDate,FundID,FundDescription) VALUES('" . implode("','", $data) . "') ON DUPLICATE KEY UPDATE GiftAmount=GiftAmount+".intval($data[0]).";");

This assumes GiftAmount is a numeric column, and you're not storing that as a string.

Upvotes: 1

Related Questions