What is the context of @var in load local infile

Question

PhP

// $numberOption = the number of fields in TableA.
// $csvHeaders   = a single dimention array with all the names of the fields in a CSV file.
// $tableHeaders   = a single dimention array with all the names of the fields in TableA.

for ($a=0; $a < $numberOption; $a++) {
            if ($a == 0) {
                $toVars .= "@var$a";
                $setCols .= $tableHeaders[$a] . " = @var" . $csvHeaders[$a];
            } else {
                $toVars .= ", @var$a";
                $setCols .= ", " . $tableHeaders[$a] . " = @var" . $csvHeaders[$a];
            }
        }

        $sql    = "LOAD DATA LOCAL INFILE '".addslashes($current_file)."' REPLACE INTO TABLE $current_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '" . '"' . "' ESCAPED BY " . "'\\\\'" . " LINES TERMINATED BY '\n' IGNORE 1 LINES ($toVars) SET $setCols";

The returned value of $sql

"LOAD DATA LOCAL INFILE '\/var\/www\/html\/test_lms\/include\/files\/1349237011-2fb46cd0c360464ebf471755cc9580de-AUTO_INSURANCE.csv' REPLACE INTO TABLE auto FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (@var0, @var1, @var2, @var3, @var4, @var5, @var6, @var7, @var8, @var9, @var10, @var11, @var12, @var13, @var14, @var15, @var16, @var17, @var18, @var19, @var20, @var21, @var22, @var23, @var24, @var25, @var26, @var27, @var28, @var29) SET callcenter = @var, agent = @varEMPTY, generation_date = @varEMPTY, first_name = @varEMPTY, last_name = @varEMPTY, email = @var3, phone = @var4, address = @var5, city = @var6, state = @var7, dob = @varEMPTY, gender = @varEMPTY, marital_status = @varEMPTY, rented = @varEMPTY, year = @varEMPTY, make = @varEMPTY, model = @varEMPTY, trim = @varEMPTY, vin = @varEMPTY, primary_use = @varEMPTY, miles_oneway = @varEMPTY, mileage = @varEMPTY, license_num = @varEMPTY, license_state = @varEMPTY, education = @varEMPTY, job_title = @varEMPTY, vendors = @var9, license_status = @varEMPTY, zip = @var8, dupe = @varEMPTY

Okay clearly in the php line that says $setCols .= $tableHeaders[$a] . " = @var" . $csvHeaders[$a]; it is initializing $setCols with a field name from tableA and then the equal sign, then this thing @var (which I guess is a mysql variable?), then a csv field name. But the result shows a number attached to the @var sign and not a csv field name! Some @var have EMPTY attached to them and that just means there was no field name from the $csvHeaders array to use so EMPTY was returned.

Anyway, it works. I am able to load in a csv file just fine and perfectly actually. But I dont understand how it is able to SET with @var# and not @varCSVNAME. Another question is why use @var at all? Wouldn't IGNORE LINES 1 (table field names) SET (csv header names) work just fine? Why the @var's?

can you show the contents of `$csvHeaders`, for which the given `sql` is returned. — air4x, Oct 03 '12 at 05:49
`["","EMPTY","EMPTY","EMPTY","EMPTY","3","4","5","6","7","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","EMPTY","9","EMPTY","8","EMPTY"]` — , Oct 03 '12 at 05:58
i think it gives the position of a CSV column relative to a table column. So the 6th field in `$tableHeaders` is present in the 3rd column of the CSV. — air4x, Oct 03 '12 at 06:01
yup that is exactly what it does. I knew that little tid bit, but I was under the impression that the way load local infile worked is that you give it table field names in a certain order and then you give those fields values by using `SET` with those values occuring in the same order that their respective field names occured. — , Oct 03 '12 at 06:09

score 1 · Accepted Answer · answered Oct 03 '12 at 06:59

The format used in your LOAD DATA INFILE statement is

LOAD DATA LOCAL INFILE 'file.txt'
  REPLACE INTO TABLE t1
  ...
  (@var0, @var1, ...)
  SET column0 = @var0,
  SET column1 = @var1,
  ...
  ;

Here @var0, @var1 etc are mysql user variables and column0, column1 etc are mysql table columns. The fields from the csv file would be read into the given mysql user variables @var0, @var1, ... in the order of the fields in the csv file.

I don't think there is any syntax which allows you to map the csv header field names to the table columns. So you wouldn't be able to use IGNORE LINES 1 (table field names) SET (csv header names).

Your script uses $numberOption to create that many user variables, to which the fields in the csv file will be read into. $csvHeaders stores the position of a CSV field relative to the corresponding table column. So $tableHeaders[6] = 'email'; and $csvHeaders[6] = 3; since the 3rd field of the csv file is the email field. In the LOAD DAT INFILE statement, the third field from the csv file would be read into the user variable @var3 and then SET email = @var3 will set the value of the email column with this variable. Since nothing is being read into @varEMPTY, columns set with them will not get any data from the file. Similarly data read into variables like @var29 which is not used in any set will be ignored.

Alternatively you could use LOAD DATA ... (COL3, COL1, @dummy, COL2, @dummy);, if the fields in the csv file were col3, col1, notInTable, col2, notInTable;. If the columns in the table and fields in the csv file differed in number and order this could be used.

If the columns in the table and fields in the csv file matched in number and order, then LOAD DATA ... INTO TABLE t1; would be enough.

If the columns in the table and fields in the csv file matched in number but differed in order, then LOAD DATA ... INTO TABLE t1 (COL3, COL1, COL2);.

score 0 · Answer 2 · edited May 23 '17 at 12:12

0

the @ symbol is only used for error suppression in PHP. What is the use of the @ symbol in PHP?

var is only used when declearing variables inside of a class.

When declaring a variable in PHP you do not have to declare the variable before hand (although it is good practice if you are used to using other languages). You do however have to express that it is a variable by using a prefix of $, ex: $x = 0;

Using DATA LOCAL INFILE through mysql you are able to pass "dummy" parameters to ensure that they are set, even if they don't exist.

Using this line: $setCols .= $tableHeaders[$a] . " = @var" . $csvHeaders[$a]; seems a little strange to me, as @var would be considered a constant of var where @ suppresses the error that the constant doesn't exits.

This then concatenates the rest of the string (in your case $tableHeaders[$a]."= ".$csvHeaders[$a];).

edited May 23 '17 at 12:12

Community

1
1

answered Oct 03 '12 at 04:51

Samuel Cook

16,620
7
50
62

So.. how it is able to SET with @var# and not @varCSVNAME? – Oct 03 '12 at 05:22
I don't see any instances of "@varCSVNAME" in the code that you supplied. – Samuel Cook Oct 03 '12 at 05:28
`$setCols .= $tableHeaders[$a] . " = @var" . $csvHeaders[$a];` @var is concen to $csvHeaders. – Oct 03 '12 at 05:35

What is the context of @var in load local infile

2 Answers2