I have an INSERT statement in a PHP-file wherein at-signs (@) are occurring in front of the column name.
@field1, @field2,
It is a MySQL database. What does the at-sign mean?
Edit:
There is no SET @field1 := 'test'
in the PHP script. The PHP script reads a csv and puts the data into the table. Can it be misused as a commenting out feature?
<?php
$typo_db_username = 'xyz'; // Modified or inserted by TYPO3 Install Tool.
$typo_db_password = 'xyz'; // Modified or inserted by TYPO3 Install Tool.
// login
$_SESSION['host'] = "localhost";
$_SESSION['port'] = "3306";
$_SESSION['user'] = $typo_db_username;
$_SESSION['password'] = $typo_db_password;
$_SESSION['dbname'] = "database";
$cxn = mysqli_connect($_SESSION['host'], $_SESSION['user'], $_SESSION['password'], $_SESSION['dbname'], $_SESSION['port']) or die ("SQL Error:" . mysqli_connect_error() );
mysqli_query($cxn, "SET NAMES utf8");
$sqltrunc = "TRUNCATE TABLE tablename";
$resulttrunc = mysqli_query($cxn,$sqltrunc) or die ("Couldn’t execute query: ".mysqli_error($cxn));
$sql1 = "
LOAD DATA LOCAL
INFILE 'import.csv'
REPLACE
INTO TABLE tablename
FIELDS
TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(
`normalField`,
@field1,
@field2,
`normalField2`,
@field3,
@field4
)";
$result1 = mysqli_query($cxn,$sql1) or die ("Couldn’t execute query: " . mysqli_error($cxn));
?>'
SOLUTION:
Finally, I found it out! The @ field is used as dummy to miss out a column in a csv-file. See http://www.php-resource.de/forum/showthread/t-97082.html http://dev.mysql.com/doc/refman/5.0/en/load-data.html
The @
sign is a variable in SQL.
In MySQL it is used to store a value between consecutive runs of a query, or to transfer data between two different queries.
An example
Transfer data between two queries
SELECT @biggest:= MAX(field1) FROM atable;
SELECT * FROM bigger_table WHERE field1 > @biggest;
Another usage is in ranking, which MySQL doesn't have native support for.
Store a value for consecutive runs of a query
INSERT INTO table2
SELECT @rank := @rank + 1, table1.* FROM table1
JOIN( SELECT @rank := 0 ) AS init
ORDER BY number_of_users DESC
Note that in order for this to work, the order in which the rows get processed in the query must be fixed, it's easy to get this wrong.
See:
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
mysql sorting and ranking statement
http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/
UPDATE
This code will never work.
You've just opened the connection before and nowhere are the @fields set.
So currently they hold null
values.
To top that, you cannot use @vars to denote fieldnames, you can only use @vars for values.
$sql1 = "
LOAD DATA LOCAL INFILE 'import.csv'
REPLACE INTO TABLE tablename
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(`normalField`, @field1, @field2, `normalField2`, @field3, @field4)";