take a tab delimited file and update a MySQL field based on matching rows to a unique field

Question

Trying to piece together fgetcsv questions and answers to get where I need to be but do not grasp the basics enough to get the result I need. The Tab delimited file sample....

╔══════════════╦════════════╦═══════╦══════════╗
║     sku      ║    asin    ║ price ║ quantity ║
╠══════════════╬════════════╬═══════╬══════════╣
║ 00-1IAB-H5E9 ║ B008S0TEFQ ║ 5.00  ║        1 ║
║ 00-BOXP-HDX4 ║ B00BP9N4JO ║ 20.00 ║        1 ║
╚══════════════╩════════════╩═══════╩══════════╝

I need to update the database with a new quantity, based on the sku. Found a lot of similar answers, but not exactly what I need. Price and ASIN are ignored. Here is my code so far of the relevant portion.

$fin = fopen('qtyme.txt','r') or die('cant open file');
$link = mysql_connect('xxxx.com', 'xxxx', 'xxxx');
If (!$link) {
    die ('Could not connect: ' . mysql_error());
}
@mysql_select_db('xxxx') or die ('Unable to select database');
echo "Connection succeeded, starting processing..... <br />\n";
while (($data=fgetcsv($fin,r,"/t"))!==FALSE) {
    $query = "UPDATE products SET products_quantity = '".$data[3]."' WHERE products_sku = '".$data[0]."'";
    mysql_query($query);
    echo ($query) . "<br />\n";
    }
fclose($fin);
mysql_close();

Obviously I do not know what I am doing, but it appears my issue is in defining the columns to pull out the proper data for the UPDATE.

help?

FINAL Solution: Thanks a lot ofr the help!

$link = mysql_connect('xxxx', 'xxxx', 'xxxx'); If (!$link) {
    die ('Could not connect: ' . mysql_error()); } @mysql_select_db('xxxx') or die ('Unable to select database');

$sql = "TRUNCATE TABLE `tmpStock`"; mysql_query($sql); echo "Temporary Table Deleted...<br>";

$result = mysql_query("LOAD DATA LOCAL INFILE 'qtyme.txt' INTO TABLE tmpStock FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' LINES TERMINATED BY '\n'")or die ('Error: '.mysql_error ()); echo "Temporary Data Uploaded and Inserted...<br>";

$result = mysql_query("UPDATE products a INNER JOIN tmpStock b ON a.products_sku = b.sku SET a.products_quantity = b.quantity")or die ('Error: '.mysql_error ()); echo "All Quantity's Updated...";

mysql_close($link);

The problem appears to be the fgetcsv. You have a 2nd parameter of r when it should be the max line size (or 0 to default it), while the 3rd parameter has the wrong slash to be used with a t to denote a tab. — Kickstart, May 13 '13 at 16:28

Kickstart · Accepted Answer · 2013-05-13T15:15:38.960

I would be tempted to create a temp table, load your delimited file into the temp table and then do a single update using a JOIN between your existing table and the temp table.

Something like this (not tested so please excuse any typos)

<?php

$link = mysql_connect('localhost', 'root', '');
If (!$link) 
{
    die ('Could not connect: ' . mysql_error());
}
@mysql_select_db('testarea') or die ('Unable to select database');

echo "Connection succeeded, starting processing..... <br />\n";

$result = mysql_query("CREATE TEMPORARY TABLE tmpStock
(sku varchar(50),
asin varchar(50),
price double(16,2),
quantity int(11),
KEY sku (sku )
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ");

$result = mysql_query("LOAD DATA INFILE 'C:/wamp/www/TestArea/qtyme.txt' INTO TABLE tmpStock FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' LINES TERMINATED BY '\n'")or die ('Error: '.mysql_error ());

$result = mysql_query("UPDATE products a INNER JOIN tmpStock b ON a.products_sku = b.sku SET a.products_quantity = b.quantity")or die ('Error: '.mysql_error ());

?>

PS - probably should use mysqli, but I have stuck with mysql as that is what you are already using.

With what little I know I should have come up with that. Brilliant solution. Due to security restrictions I had to give up on a temp table, and just truncate it when done and leave it in place, no big deal. Also had to add LOCAL to LOAD DATA for security reasons on the server. — northofnowhere, May 13 '13 at 16:50
Thank you, final code that got around a few minor security issues on the host server... — northofnowhere, May 13 '13 at 16:52

take a tab delimited file and update a MySQL field based on matching rows to a unique field

1 Answers1