import csv update current or insert if not exist?

[marksie1988]

Hi,

 

i have the below code which deletes the products with supp_id 1 and then reads the csv file and reimports all of the lines, i want to change this so that rather than deleting everything it just updates a match (Where supp_id = 1 and db.Manf_code = csv.Manf_code) i also want it to delete any rows were supp_id = 1 that arent in the CSV file anymore and then insert any new rows that are in the CSV.

 

i know there is a lot there that i want to do but i need to update the database every hour wilst it is live which i cant do at the moment as it deletes the whole products for the supp_id.

 

normally i would do something like this

 

SELECT * from products WHERE supp_id = 1 and db.manf_code = csv.manf_code
if ($rows == 1 && $csvrow == 1){
update $quantity, $cost_price, $description and $RRP 
}
elseif ($rows == 1 && $csvrow == 0){
DELETE From DB where Manf_code == fromselectcommand AND supp_id = 1 
elseif ($rows == 0){
INSERT into products table all info required
}

 

the only issue i have is getting it to work with the foreach in the csv import script below as im not 100% familiar with this

<?php
// define table name and csv file location and name

$supp_id = "1";
$pricecode = "1";
$csvupload = "../advent/pricelist.csv";

// delete all old data
	$sql2 = "DELETE FROM ".$table." WHERE supp_id = ".$supp_id."";

mssql_query($sql2) or die("Failed to insert to db ");

// Define DB mapping (Fill in rest of the mapping in order of appearance in CSV)
$fields = array(
"supp_part_no",
"manf_part_no",
"description",
"avail_qty",
"cost_price",
"ignore",
"ignore"
);
// Open the CSV file
$handle = fopen($csvupload, "r") or die("Unable to open $csvupload for reading");

// Get each row's values to an array 
$i = 1;
// define tab delimited or comma
while (($data = fgetcsv($handle, 512, ",")) !== FALSE) {
	// First row's headers and not included, otherwise row field count must match wanted field count
	if(count($data)==count($fields) && $i!=1){
		$rows[] = $data;
	}
	elseif(count($data)!=count($fields)) {			
		die("Erroneus CSV file: incorrect field count on row $i");
	}
	$i++;
}

// Close file handle
fclose($handle);

// Create SQL
if(count($rows)==0) die("No rows found in user file");


 // Remove last comma
foreach($rows as $i => $row){
	$sql = "INSERT INTO ".$table." (";
foreach($fields as $field)
	if($field!="ignore")
		$sql .= "$field,";
$sql = substr($sql,0,-1).",supp_id, date_added, PriceCode) VALUES ";
	$sql .= "(";
	foreach($row as $j => $value){
		if($fields[$j]!="ignore"){
			if(empty($value))
				$value = "NULL";
			// Quote strings, try to remove existing quotes
			elseif(!is_numeric($value))
				$value = "\"".str_replace("\"","",$value)."\"";
			$sql .= "$value,";
		}
	}

	$sql = substr($sql,0,-1).",".$supp_id.", getdate(),".$pricecode." )"; // Remove last comma
	//echo "SQL became: $sql";

	mssql_query($sql) or die("Failed to insert to db ");

}
?>

[marksie1988]

dont suppose anyone can help with this? even wit a different view at executing it

[marksie1988]

Hi,

 

i have now managed to get this code problem is when i have my select statement for the query i get an echo of resource id #1resource id #2resource id #3 etc how can i fix this?

 

<?php

define ('GALTEC'      , 1);
define ('COMPANY_NAME', 2);
define ('OTHER_COMP'  , 3);

// create DB connection
$host = "localhost";
$user = "user";
$pass = "pass";
$mydb = "database";
$table = "products";

	$db = mssql_connect($host,$user,$pass);
	mssql_select_db($mydb);

function process_csv( $csvupload, $from_company, $callback )
{
    $handle = fopen($csvupload, 'r') or die("Unable to open $csvupload for reading");
    $head = array();

    //  You said that each company always sends you files with the same columns.
    //  Describe the differenct columns here, for each company separately.  If
    //  the first row of the CSV file already contains the field names, you can
    //  also simply read them, as I did with GALTEC.
    switch( $from_company )
    {
        case GALTEC:        $head = fgetcsv( $handle, 512, ',' );   //  Don't forget to catch any errors.
                            break;

        case COMPANY_NAME:  //  I don't know what the content of each column is, so I'm making the names up.
                            $head[] = 'supp_part_no';    // e.g., REMANG-BT350
                            $head[] = 'manf_part_no';       // e.g., ADG-BT350
                            $head[] = 'description';        // e.g., Qfax BT350/370 ALTERNATE TTR
                            $head[] = 'avail_qty';       // e.g., .000
                            $head[] = 'cost_price';      // e.g., 6.27
                            $head[] = 'SUPPLIER';   // e.g., AD
                            $head[] = 'COMMENT';    // everything after the last comma
                            break;
                            
        case OTHER_COMP:    //  This company supplies us with a CSV file that contains a header, but we
                            //  use different field names, so we read the first lnie, then override the
                            //  field names.
                            $flush = fgetcsv( $handle, 512, ',' );   //  Don't forget to catch any errors.
                            $head[] = 'PRODUCT';    // e.g., REMANG-BT350
                            $head[] = 'SUPPLIER';   // e.g., AD
                            $head[] = 'TYPE';       // e.g., ADG-BT350
                            $head[] = 'SIZE';       // e.g., .000
                            $head[] = 'AKA';        // e.g., Qfax BT350/370 ALTERNATE TTR
                            $head[] = 'PRICE';      // e.g., 6.27
                            $head[] = 'COMMENT';    // everything after the last comma
                            break;
                            
    }

    //  At tihs point, $head should always contain the same column names,
    //  regardless of the company that sent the CSV file.  Only the order
    //  may differ from company to company

    $i = 1;
    while ( false !== ( $data = fgetcsv( $handle, 512, ',' ) ) )
    {
        if( count( $data ) == count( $head ) )
        {
            $callback( array_combine( $head, $data ) );
        }
        else
        {
            die("Erroneus CSV file: incorrect field count on row $i");
        }
    }
}


function process_record($row)
{
$select = "SELECT * FROM FindMeStock.dbo.products WHERE manf_part_no = '".$row['manf_part_no']."' AND supp_id = '1'";
    $qry = mssql_query($select) or die("Failed to select from DB");
    //  At this point, $row is an array with the following structure:
    //  $row = Array ( [PRODUCT] => REMANG-BT350 [TYPE] => ADG-BT350 [AKA] => Qfax BT350/370 ALTERNATE TTR [size] => .000 [PRICE] => 6.27 [sUPPLIER] => AD [COMMENT] => )
    //  Note that you can now access each item using standard indexes:

    //echo $row['PRODUCT'], ' ';

}


process_csv( 'pricelist.csv', COMPANY_NAME, 'process_record');
?>