CSV File import into the database with preg_match query for 400x400 contact url

Question

My import script is working correctly. In $field_csv['imageUrlStr'] there are some different sized image urls, I need to fetch 400x400 string that contains the url for my image url, then insert that into the database, but when I am using my current code the same row gets inserted many times.

Please help me.

<?php

    set_time_limit(2400);  // time out limit

    // Database Connection

    // Check connection
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $table_name = "table";
    $csv_file   = "import.csv"; // Name of your CSV file
    $csvfile    = fopen($csv_file, 'r');
    $field_csv  = array();

    $i = 0;
    while (($csv_data = fgetcsv($csvfile, 0, ",")) !== FALSE) {
        if($i==0) { 
            $i++;
            continue;
        }  // to exclude first line in the csv file.

    $field_csv['productId']     =mysqli_real_escape_string( $mysqli, $csv_data[0] );  // 1
    $field_csv['title']         = mysqli_real_escape_string( $mysqli, $csv_data[1] ); // 2
    $field_csv['description']   = mysqli_real_escape_string( $mysqli, $csv_data[2] ); // 3
    $field_csv['imageUrlStr']   = mysqli_real_escape_string( $mysqli, $csv_data[3] ); // 4
    $field_csv['mrp']           = mysqli_real_escape_string( $mysqli, $csv_data[4] ); // 5
    $field_csv['price']         = mysqli_real_escape_string( $mysqli, $csv_data[5] ); // 6
    $field_csv['productUrl']    = mysqli_real_escape_string( $mysqli, $csv_data[6] ); // 7
    $field_csv['categories']    = mysqli_real_escape_string( $mysqli, $csv_data[7] ); // 8
    $field_csv['productBrand']  = mysqli_real_escape_string( $mysqli, $csv_data[8] ); // 9
    $field_csv['deliveryTime']  = mysqli_real_escape_string( $mysqli, $csv_data[9] ); // 10
    $field_csv['inStock']       = mysqli_real_escape_string( $mysqli, $csv_data[10] ); // 11
    $field_csv['codAvailable']  = mysqli_real_escape_string( $mysqli, $csv_data[11] ); // 12
    $field_csv['emiAvailable']  = mysqli_real_escape_string( $mysqli, $csv_data[12] ); // 13
    $field_csv['offers']        = mysqli_real_escape_string( $mysqli, $csv_data[13] ); // 14
    $field_csv['discount']      = mysqli_real_escape_string( $mysqli, $csv_data[14] ); // 15
    $field_csv['cashBack']      = mysqli_real_escape_string( $mysqli, $csv_data[15] ); // 16
    $field_csv['size']          = mysqli_real_escape_string( $mysqli, $csv_data[16] ); // 17
    $field_csv['color']         = mysqli_real_escape_string( $mysqli, $csv_data[17] ); // 18
    $field_csv['sizeUnit']      = mysqli_real_escape_string( $mysqli, $csv_data[18] ); // 19
    $field_csv['sizeVariants']  = mysqli_real_escape_string( $mysqli, $csv_data[19] ); // 20
    $field_csv['colorVariants'] = mysqli_real_escape_string( $mysqli, $csv_data[20] ); // 21
    $field_csv['styleCode']     = mysqli_real_escape_string( $mysqli, $csv_data[21] ); // 22

    // Error Part
    $array   = explode(",", $field_csv['imageUrlStr']);
    $pattern = "/400x400/";

    foreach($array as $value) {
        $productNewImage = (preg_match($pattern,$value)) ? $value : "";
        echo $productNewImage;

    $query = "INSERT INTO $table_name 
        SET productId = '" . $field_csv['productId'] 
        . "', title = '" . $field_csv['title'] 
        . "', description = '" . $field_csv['description'] 
        . "',imageUrlStr = '" . $productNewImage . "',mrp = '" . $field_csv['mrp'] 
        . "',price = '" . $field_csv['price'] 
        . "',productUrl = '" . $field_csv['productUrl'] 
        . "',categories = '" . $field_csv['categories'] 
        . "',productBrand = '" . $field_csv['productBrand'] 
        . "',deliveryTime = '" . $field_csv['deliveryTime'] 
        . "',inStock = '" . $field_csv['inStock'] 
        . "',codAvailable = '" . $field_csv['codAvailable'] 
        . "',emiAvailable = '" . $field_csv['emiAvailable'] 
        . "',offers = '" . $field_csv['offers'] 
        . "',discount = '" . $field_csv['discount'] 
        . "',cashBack = '" . $field_csv['cashBack'] 
        . "',size = '" . $field_csv['size'] 
        . "',color = '" . $field_csv['color'] 
        . "',sizeUnit = '" . $field_csv['sizeUnit'] 
        . "',sizeVariants = '" . $field_csv['sizeVariants'] 
        . "',colorVariants = '" . $field_csv['colorVariants'] 
        . "',styleCode = '" . $field_csv['styleCode'] 
        . "' ";

        mysqli_query($mysqli,$query);

        } 
    }

    error_reporting(E_ALL);

    fclose($csvfile);

    echo "CSV data successfully imported to table!!";

    // close connection
    $mysqli->close();

Answer 1

You are currently inserting one row per image URL extracted from the imageUrlStr value, whether it matches the 400x400 pattern or not:

$array=explode(",", $field_csv['imageUrlStr'] );
$pattern = "/400x400/";

// Loop through all extracted image URLs
foreach($array as $value) {
    // This would be a 400x400 matching URL or an empty string
    $productNewImage = (preg_match($pattern,$value)) ? $value : "";

    // Insert a row with image URL or an empty string
    $query = "INSERT INTO ... " // Truncated for brevity
    mysqli_query($mysqli,$query);  
}

If, for example, imageUrlStr was http://foo.bar/200x200/image1.png,http://foo.bar/400x400/image2.png you would get two rows inserted into the database. One with an empty image URL and another with the 400x400 URL, all other column values the same.

You would probably be better off getting the $productNewImage using a preg_grep call on the exploded imageUrlStr value:

$array = explode(",", $field_csv["imageUrlStr"]);
$pattern = "/400x400/";

// Get (first) URL matching pattern, or an empty string
@list($productNewImage) = array_values(preg_grep($pattern, $array));
$productNewImage = mysqli_real_escape_string($mysqli, $productNewImage);

// Insert one row with the image URL or empty string
$query = "INSERT INTO ... " // Truncated for brevity
mysqli_query($mysqli, $query);