Concatenate my php variable to csv data when importing LOAD DATA INFILE

Question

I need to import some csv files to mysql.

I recently found about the LOAD DATA INFILE, I checked the manual, as far as I understand, to imports each line of .csv file to the database.

I was wondering if there's an option to concatenate an extra value to what the csv file contains.

For example: my table has 3 columns.

MYTABLE(#name,#email,company_adrs);

The csv file has data for "name,email" only, and I have the address in a php variable.

With a normal loop, I would achieve this as follows:

//open file
while (/* not end of file, read eachline*/)
{
  $current_line_of_csv_file; // contains: "John,john@john.com" without the 3rd column
  $myAddrsVar= mysqli_real_escape_string($db, $_POST['adrs']); //"44 company st" 
  $insert = $db->query("INSERT MYTABLE (name,email,company_adrs) VALUES ( $current_line_of_csv_file,'".$myAddrsVar."')");

}

With this method I have to loop through each line the csv and do a query in each iteration.

I was wondering if LOAD DATA INFILE has a method to concatenate my data to the csv file? if so, how can I achieve it?

Thanks in advance

@zedfoxus, yes, they all have the same company, but the address is a user input that comes from a form, that's why it's stored in a php variable — user206904, Apr 27 '19 at 23:36
~100 per file, but we will have multiple files every couple of days. it's a page that allows a non-techy-person to import data to my database by only entering the company name once in a form, select the file and the script imports the data. I though about concatenating ",$myAddrsVar" at the end of each line of the csv file, but this will be the same as looping through the file in the example I gave. The only difference is that we insert in the file instead of inserting into the database. — user206904, Apr 27 '19 at 23:49

score 2 · Accepted Answer · answered Apr 28 '19 at 00:08

Given your situation, I would recommend using PHP all-in-all and insert data into MySQL that way.

https://www.php.net/manual/en/function.str-getcsv.php can help you parse CSV into an array. Add to the array the address. Now, you'd have the dataset in an array.

Use prepared statements like shown here https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection and insert data one after the other.

Use transactions so that all data is inserted or no data is insert. See How to start and end transaction in mysqli?.

I'd avoid using LOAD DATA INLINE because the volume of data is not that big. If you really want to use it and add another column, you can use SET statement. There's an example of that here: Add extra column of data when using LOAD DATA LOCAL INFILE

Thanks for your suggestions! I gave the 1st option a try, and it worked. But in the end I went with the LOAD DATA INLINE option. Thanks a lot, I didn't know about the set option. Btw, the transactions things seems so useful, thanks for the tip. — user206904, Apr 29 '19 at 14:39

Concatenate my php variable to csv data when importing LOAD DATA INFILE

1 Answers1