PHP and MySQL: Import CSV using Column Value as table

Question

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)

"$10","$500","1/1/2012","8008","Description of 8008 Fund"
"$20","$500","2/10/2012","8008","Description of 8008 Fund"
"$62","$500","1/12/2012","8008","Description of 8008 Fund"
"$38","$1,000","3/31/2012","9102","Description of 9102 Fund"
"$21","$1,000","4/6/2012","9102","Description of 9102 Fund"

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:

"$92" | "$500" | "8008" | "Description of 8008 Fund" (notice the GiftDate is no longer necessary)
"$59" | "$1,000" | "9102" | "Description of 9102 Fund"

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.

Looks like all you need is a SUM(GiftAmount) with a GROUP BY. What query are you using? — Kermit
– Kermit, Commented Feb 4, 2013 at 16:36
You can either do one of two things. On the PHP side, setup logic that will add if it's the same Fund. This will only work if it's sequential, otherwise you'll need an array. On the SQL side, you create a staging table, perform the calculations there and insert into a production table. — Kermit
– Kermit, Commented Feb 4, 2013 at 16:39

Michael Marr · Accepted Answer · 2013-02-04 16:36:33Z

1

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.

answered Feb 4, 2013 at 16:36

Michael Marr

2,09914 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Loko Web Design Over a year ago

Thanks so much for the quick reply. You mentioned the assumption that GiftAmount is numeric. In the CSV, it is just a string. How would I convert it to a decimal (right?) in the loop?

Michael Marr Over a year ago

Change intval($data[0]) to sscanf($data[0], "\$%f").

Loko Web Design Over a year ago

Here's what my query looks like so far:

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

This does not put the data into the database. I noticed in my text editor that the ON DUPLICATE KEY UPDATE seems to be color-coded as though it's inside quotes. I'm not sure if that's the problem or not, though.

Loko Web Design Over a year ago

Here is a screenshot of my PHPMyAdmin, in case it will help: mafgiftshop.org/MAF/queue/MP/phpmyadmin.jpg

Loko Web Design Over a year ago

OK, I'm getting closer:

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

Unfortunately, the calculation seems to be doubling the first number in the group (in my example above, the 8008 FundID group would total 102 because it adds the first $10 to itself before adding the rest. Any thoughts on why that's happening?

|

Collectives™ on Stack Overflow

PHP and MySQL: Import CSV using Column Value as table

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related