DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`purchase_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `transactions` (`purchase_date`) VALUES (NULL)
I've isolated my problem in this code. When I run it, I get the error:
[ERROR in query 3] Unknown column 'purchase_date' in 'field list'
Anyone an idea?
There is an unprintable character 30 (RecordSeparator) inserted between purchase_date and the ' in the INSERT statement. Just remove the text ('purchase_date') and rewrite it by hand it should be fine.
Nery niche solution when I got this error.
I had a BEFORE INSERT trigger on my table that did something with
NEW.`field_mysql_doesnt_think_exists`
and if I didn't pass that field to an insert statement then I would get
[ERROR in query 3] Unknown column 'field_mysql_doesnt_think_exists' in 'field list'
I just spent the better part of a day figuring this out. My problem was the same: invisible characters kiboshing the query and returning the "unknown column" error.
I solved it by wading back into Windows and removing the garbage using NotePad++.
How did the garbage get in there in the first place? I think it was because I made the mistake of copying some long complex queries into LibreOffice Writer (my functional specs document) instead of just bookmarking them in phpMyAdmin or saving them in a text editor. Pasting them from LibreOffice into the query window is where (I think) the garbage originated.
Once there, it persisted like Malaria. I couldn't even get rid of it by hand-retyping the whole query -- I had to put it into NotePad++ (Encoding menu) and show ANSI and the UTF8 combos and then remove the garbage by hand.
Once that was done, the query worked.
This can also happen if you paste a column name when building the table structure. Same error - but the unprintable/invisible characters are in the table structure, not the query.
This might not help anyone else, but adding this "just in case" it helps someone.
I receive large datasets as Excel CSV files and use a (WIL) script to convert the .csv file into an importable .sql file.
I had an error in my conversion script whereby these two lines did not reference the same table name (I had hard-coded the first location and forgot to update it):
* "INSERT INTO `old_table_name` (`cid`, `date`, etc etc"
* "CREATE TABLE IF NOT EXISTS `":_dbName:"` (etc etc "
I just changed the first line to also get the table name from the variable, and voila!
* "INSERT INTO `":_dbName:"` (`cid`, `date`, etc etc"
So check those two lines in your import SQL file.
I have had the same issue this morning and I didn't find my answer. But I found my problem when I changed the single quotes around my query to double quotes. Something so small and an oversight can cause a real headache.
Unknown column x in "field list" - Code below wrapped in single quotes - Non working.
$likepost='INSERT INTO reaction(reaction_num,userreaction_id,timereacted,
streamitem_id,comment_posted_on)
VALUES ($reaction,$target,NOW(),$streamid,$comment_id)';
Code below wrapped in double quotes. working
$likepost="INSERT INTO reaction(reaction_num,userreaction_id,timereacted,
streamitem_id,comment_posted_on)
VALUES ($reaction,$target,NOW(),$streamid,$comment_id)";
Same error in a different scenario:
This also happens when you miss @ symbol for a variable.
SET @myVar1=1; SELECT @myVar1; -- GOOD, correctly prints: 1
SET @myVar1=1; SELECT myVar1; -- BAD, prints: Unknown column 'myVar1' in 'field list'
when you want to work with mysql using a function like this:
function insert($table, $info_array){
// implode keys as columns by [`,`] glue string
$columns = implode("`,`", array_keys($info_array));
// implode values as sql ready values by [','] glue string
$values = implode("','", array_values($info_array));
// make query(careful about [`] for columns name and ['] for values)
$sql = "INSERT INTO ".$table." (`".$columns."`) VALUES ('".$values."');";
return $sql;
}
you should be careful about [ ` ] for table columns names and [ ' ] or [ " ] for values. for example, I used above function this way:
try{
$db_insert_sample_user = $connection->query(insert(TABLE_PREFIX."users", [
"username" => "my_name_2",
"password" => md5("how457fty")
]));
echo '<pre>';
print_r($db_insert_sample_user);
echo '</pre>';
}catch (PDOException $exc){
echo '<pre>';
print_r($exc);
echo '</pre>';
}
the query string is this:
INSERT INTO php_pdo_users (`username`,`password`) VALUES ('my_name_2','ee04708d313adf4ff8ba321acf3eb568');
and the result was like : (for two users) PHPMyAdmin Result
if you want functions based on prepared statements, test this : (placeholders, params and values, don't need [ ' ] or [ " ] at all!!!)
function insert_prepared(PDO $connection, $table, $info_array){
// columns
$columns = implode("`,`", array_keys($info_array));
// placeholders
$place_holders = [];
for ( $i = 0; count(array_keys($info_array)) > $i; $i++){
$place_holders[] = '?';
}
// convert placeholders to query string
$place_holders_str = implode(",", $place_holders);
$prepared_stmt = "INSERT INTO ".$table." (`".$columns."`) VALUES (".$place_holders_str.");";
// prepare statement
$stmt = $connection->prepare($prepared_stmt);
// values
$values = array_values($info_array);
// bind all params to values
for($i = 0; count($values) > $i; $i++){
$stmt->bindParam($i + 1, $values[$i]);
}
// execute and return results
return $stmt->execute();
}
after code execution this way :
try {
$db_insert_sample_user = insert_prepared(
$connection,
TABLE_PREFIX . "users",
[
"username" => "my_name_4",
"password" => md5( "HelloDolly#__3" )
]
);
} catch ( PDOException $exc ) {
echo "Failed : " . $exc->getMessage();
}
results is :
Results with insert_prepared function
I was using a mysql procedure and in the procedure parameter I used phone with an extra space instead of phone with no extra space, due to this when ever I called the function. It just throw error no such column as phone . Until I miraculously spotted it and corrected it using phpMyAdmin, Error went off.
I had this same PYMYSQL error in my Python program when I found this answer.
I probably violated a Pandas rule when I created a new dataframe ...
This created same the error above: A phantom column
inddata = dat[['ind_id','iso3c','date','data']]
dat.to_sql(name='inddata', con=engine, if_exists='append', index=False, chunksize=200)
This fixed the error:
dat2 = dat[['ind_id','iso3c','date','data']]
inddata = dat2.copy()
dat.to_sql(name='inddata', con=engine, if_exists='append', index=False, chunksize=200)
Sometimes this is caused by an unprintable character in a column name I used a query like this to find which column was bad. I regularly import csv data using a python script in Linux. The csv was created from converting a windows xl spreadsheet. There was something at the very beginning of the file, preprocessing it with dos2unix appears to have removed whatever was there.
mysql> select id from yourtablename;
ERROR 1054 (42S22): Unknown column 'id' in 'field list'
mysql> SELECT CONVERT(COLUMN_NAME USING ASCII) as name
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='databasename' AND `TABLE_NAME`='yourtablename';
+----------------------------------+
| name |
+----------------------------------+
| ?id |
| first |
| last |
| name |
| costcenter |
+----------------------------------+
Look at the name of the table you are handling
