Implode Uploaded CSV Data for an Update Query

Question

I have the following code to insert records into a database via a csv file

        $get_columns = $db_website->prepare("SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = 'mytable' AND TABLE_NAME = 'products'");
        $get_columns->execute();

        while ($row = $get_columns->fetch(PDO::FETCH_ASSOC)) {

            $want[] = $row['COLUMN_NAME'];

        }

        $file = fopen($_FILES['filename']['tmp_name'], "r");

        $counter = 0;

        while (!feof($file)) {

            if ($counter === 1)
                break;

            $have = fgetcsv ($file, 5000); 
            ++$counter;

        }

        fclose ($file);

        $map = array_intersect($have, $want);
        $num_feilds = implode($map);
        $fields = "`".implode("`,`",$map)."`";

        if ($num_feilds != '') {

            $file = fopen($_FILES['filename']['tmp_name'], "r");
            $line = fgetcsv($file, 1000, ",");
            while (($line = fgetcsv($file)) !== FALSE) {

                $data = array_intersect_key($line, $map);

                $implode = str_replace("'", '&#39;', $data);
                $implode = str_replace("£", '&pound;', $implode);

                $implode = "'".implode("','",$implode)."'";

                $query = $db_website->prepare("SELECT p.stock_id
                FROM products AS p
                WHERE p.stock_id = :data");
                $query->bindValue(':data', $data[0], PDO::PARAM_INT);
                $query->execute();
                $product_exists = $query->rowCount();

                if ($product_exists == 0) {

                    $product_import = "INSERT INTO products ($fields, token, date_created) VALUES ($implode, :token, :date_created)";
                    $product_import = $db_website->prepare($product_import);
                    $product_import->execute(array(':token'=>$token, ':date_created'=>$todays_date_time));

                    $update_slug = "UPDATE products SET slug = LOWER(title),
                    slug = replace(slug, char(128), '')
                    WHERE token = :token";
                    $update_slug = $db_website->prepare($update_slug);
                    $update_slug->execute(array(':token'=>$token));

                } else {

                    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {

                        $stock_id = $row['stock_id'];

                        $product_import = "UPDATE products SET $this_is_the_variable_i_need_to_create_from_the_implode, token = :token, date_updated = :date_updated
                        WHERE stock_id = :stock_id";
                        $product_import = $db_website->prepare($product_import);
                        $product_import->execute(array(':stock_id'=>$stock_id, ':token'=>$token, ':date_updated'=>$todays_date_time));

                    }

                    $update_slug = "UPDATE products SET slug = LOWER(title),
                    slug = replace(slug, char(128), '')
                    WHERE token = :token";
                    $update_slug = $db_website->prepare($update_slug);
                    $update_slug->execute(array(':token'=>$token));

                }

            }

            fclose($file);

        }

My problems lies in that I want it to update existing products as well as create new ones.

In the code above I have begun by doing a query to check whether the stock id exists and if it doesn't insert the record with an else to say update if it does.

The part I am struggling on is how do I make it implode the COLUMN_NAME and the data that is sent in the csv file.

Any tip in the right direction would be greatly appreciated.

Thank you Dan

oliakaoil · Accepted Answer · 2014-07-08T14:05:20.890

If I'm understanding you correctly, you need to create a series of set clauses based on what's in the $data array (which is an array containing the values from a single line of your CSV). Excluding any kind of validation (either of the columns in your import file, or the data in your import file) you could do something like this:

$sets = array();
$update_values = array();
foreach( $data as $index => $val )
{
  if(empty($have[ $index ]))
    continue;
  $field_name = $have[ $index ];
  $update_values[] = $val;
  $sets[] = "{$field_name} = ':val{$index}'";
}

if( $sets )
{
  $update_values[] = $stock_id;
  $set_clause = implode(',',$sets);  
  $product_import = $db_website->prepare("UPDATE products SET {$set_clause} WHERE stock_id = :stock_id");
  $product_import->execute( $update_values );
}

Again, you're going to want validate your input, but this should give you the idea.

score 0 · Answer 2 · answered Jul 09 '14 at 01:21

Thank you oliakaoil,

This is the code I used in the end for anybody else who may need it in the future

                    $sets = array();
                    $update_values = array();

                    foreach ($data as $index => $val) {

                        if (empty($have[$index]))
                            continue;
                        $field_name = $have[$index];
                        $update_values[] = $val;
                        $sets[] = "{$field_name} = '{$val}'";

                    }

                    if ($sets) {

                        $update_values[] = $stock_id;
                        $set_clause = implode(',',$sets);  

                        $product_import = "UPDATE products SET {$set_clause}, token = :token
                        WHERE stock_id = :stock_id";
                        $product_import = $db_website->prepare($product_import);
                        $product_import->execute(array(':stock_id'=>$update_values[0], ':token'=>$token));

                    }

Implode Uploaded CSV Data for an Update Query

2 Answers2