- MySQL Error: Unknown column in ‘field list’ PHP/MySQL [closed]
- PHP SQL query error — Unknown array column in WHERE clause
- PHP/MySQL — Error —> Unknown column » in ‘field list’
- How to fix MySQL database ERROR 1054: Unknown column
- Fix ERROR 1054 on a SELECT statement
- Fix ERROR 1054 on an INSERT statement
- Fix ERROR 1054 on an UPDATE statement
- Fix ERROR 1054 on an ALTER TABLE statement
- Conclusion
- Take your skills to the next level ⚡️
- About
- Search
- Tags
- How to fix MySQL unknown column in field list error
- Summary
- Take your skills to the next level ⚡️
- About
- Search
MySQL Error: Unknown column in ‘field list’ PHP/MySQL [closed]
It’s difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.
Hey guys I have a problem with MySQL, i’m trying to insert data in a table but it’s returning this error message
Fatal error: SQL: SELECT e.idtarea AS ‘idTarea’, e.detalle AS ‘detalle’, e.precio AS ‘precio’, e.idor AS ‘idOrdenReparacion’, e.fecha AS ‘fecha’, concat( ‘Editar ‘, ‘Eliminar’ ) AS Opciones FROM Tarea e WHERE e.idtarea like ‘%%’ ORDER BY e.idtarea;, Error: Unknown column ‘e.idor’ in ‘field list’ in /opt/lampp/htdocs/scep/tareas.php on line 76
$SQL=" SELECT e.idtarea AS 'idTarea', e.detalle AS 'detalle', e.precio AS 'precio', e.idor AS 'idOrdenReparacion', e.fecha AS 'fecha', concat('Editar ','\')>Eliminar ') AS Opciones FROM Tarea e ".$FILTRAR_POR." ORDER BY e.idtarea;"; $RESULT = mysql_query($SQL) or trigger_error("SQL: $SQL, Error: " . mysql_error(), E_USER_ERROR);
CREATE TABLE IF NOT EXISTS `Tarea` ( `idTarea` int(11) NOT NULL AUTO_INCREMENT, `detalle` varchar(45) COLLATE latin1_danish_ci DEFAULT NULL, `precio` varchar(45) COLLATE latin1_danish_ci DEFAULT NULL, `idOrdenReparacion` int(11) NOT NULL DEFAULT '0', `fecha` date DEFAULT NULL, PRIMARY KEY (`idTarea`,`idOrdenReparacion`), KEY `fk_Tarea_OrdenReparacion1_idx` (`idOrdenReparacion`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci AUTO_INCREMENT=2 ; . ALTER TABLE `Tarea` ADD CONSTRAINT `fk_Tarea_OrdenReparacion1` FOREIGN KEY (`idOrdenReparacion`) REFERENCES `OrdenReparacion` (`idOrdenReparacion`) ON DELETE NO ACTION ON UPDATE NO ACTION;
PHP SQL query error — Unknown array column in WHERE clause
My page displays the name of players of a certain sports team using drop down menus. The coach can log in to pick his team and select the opposition which his team will play against. When user has selected the team and opposition he clicks submit and the isset function is triggered. Now I capture the values from the drop down menus and upload it to the correct table in the DB. Everything is pretty straight forward however when I click submit I get the error in the tittle. Any help would be appreciated
if ( isset($_POST['submit']) ) < $player_ids = array_map('intval', $_REQUEST['players']); $opponents_id = $_REQUEST['players']; var_dump($player_ids); var_dump($opponents_id); $query = 'SELECT `name`, `position` FROM `player_info` WHERE `player_id` IN (' . implode(',', $player_ids) . ')'; $return_names = mysql_query($query) or die(mysql_error()); while ( $row = mysql_fetch_assoc($return_names) ) < $selected[] = $row['name']; $position[] = $row['position']; >$query = ("SELECT `fixture_id` FROM `fixtures` WHERE `fixture_id` = $opponents_id") or die (mysql_error()); $result = mysql_query($query) or die(mysql_error()); while ($row = mysql_fetch_array($query)) < $fixture_id[] = $row['fixture_id']; >for ($i=0; sizeof($selected) > $i; $i++)< $sql = mysql_query("INSERT INTO `team` (`selection_id`, `fixture_id`, `player_position`,`player_name`) VALUES ('$fixture_id[$i]','$position[$i]','$selected[$i]')") or die(mysql_error()); echo $selected[$i]; echo $position[$i]; echo $fixture_id[$i]; echo'
'; >
PHP/MySQL — Error —> Unknown column » in ‘field list’
I have read many of the posts regarding this «Unknown column » in ‘field list'» error I am getting when I try to INSERT INTO my database. All the posts I read, the error includes the column name that seems to have the problem. In my case it is just ». nothing! I hand code everything because I am not a «real» programmer and I use free stuff such as Notepad++. I don’t have any debugging tools. Here is my code below. I have many repetive lines so I cut some out.
I wrote to the database
"; $query = "INSERT INTO `invoicedata_table` (`InvoiceNo`, `InvoiceDate`, `ClientName`, `ClientAddress`, `ClientPhone`, `ClientEmail`, `STotalTaxable`, `TPS`, `TVQ`, `STotalNonTaxable`, `TotalInvoice`, `DescLine_1`, ---I cut lines 2 though 14. They are all the same --- `DescLine_15`, `TotalLine_1`, ---I cut lines 2 though 14. They are all the same --- `TotalLine_15`) VALUES ('$InvoiceNo', '$InvoiceDate', '$ClientName', '$ClientAddress', '$ClientPhone', '$ClientEmail', '$STotalTaxable', '$TPS', '$TVQ', '$STotalNonTaxable', '$TotalInvoice', `$DescLine_1`, ---I cut lines 2 though 14. They are all the same --- `$DescLine_15`, `$TotalLine_1`, ---I cut lines 2 though 14. They are all the same --- `$TotalLine_15`);"; $mysqli->query($query) or die($query.'
'.$mysqli->error); //Close the DB connection $mysqli->close();
You are switching quotes half way through your VALUES list: it should be ‘$DescLine_1’ (and probably all others after it), not the backticks that are around it now. Also: most debugging tools are free. And good.
Wrikken, the backticks to single quotes was the issue. Please submit your comment as an answer so I can accept it. Thanks. And. off topic, what would you recommend for an easy debugger to deal with?
Okay Flyer, Wrikken did not answer yet. You had the same answer. Go ahead if you want the answer and I will accept the first one that comes through.
How to fix MySQL database ERROR 1054: Unknown column
When you execute a MySQL statement, you may sometimes encounter ERROR 1054 as shown below:
The ERROR 1054 in MySQL occurs because MySQL can’t find the column or field you specified in your statement.
This error can happen when you execute any valid MySQL statements like a SELECT , INSERT , UPDATE , or ALTER TABLE statement.
This tutorial will help you fix the error by adjusting your SQL statements.
Let’s start with the SELECT statement.
Fix ERROR 1054 on a SELECT statement
To fix the error in your SELECT statement, you need to make sure that the column(s) you specified in your SQL statement actually exists in your database table.
Because the error above says that user_name column is unknown, let’s check the users table and see if the column exists or not.
To help you check the table in question, you can use the DESCRIBE or EXPLAIN statement to show your table information.
The example below shows the output of EXPLAIN statement for the users table:
From the result above, you can see that the users table has no user_name field (column)
Instead, it has the username column without the underscore.
Knowing this, I can adjust my previous SQL query to fix the error:
That should fix the error and your SQL query should show the result set.
Fix ERROR 1054 on an INSERT statement
When you specify column names in an INSERT statement, then the error can be triggered on an INSERT statement because of a wrong column name, just like in the SELECT statement.
First, you need to check that you have the right column names in your statement.
Once you are sure, the next step is to look at the VALUES() you specified in the statement.
For example, when I ran the following statement, I triggered the 1054 error:
The column names above are correct, and the error itself comes from the last entry in the VALUES() function.
The display_name column is of VARCHAR type, so MySQL expects you to insert a VARCHAR value into the column.
But Jack is not a VARCHAR value because it’s not enclosed in a quotation mark. MySQL considers the value to be a column name.
To fix the error above, simply add a quotation mark around the value. You can use both single quotes or double quotes as shown below:
Now the INSERT statement should run without any error.
Fix ERROR 1054 on an UPDATE statement
To fix the 1054 error caused by an UPDATE statement, you need to look into the SET and WHERE clauses of your statement and make sure that the column names are all correct.
You can look at the error message that MySQL gave you to identify where the error is happening.
For example, the following SQL statement:
Produces the following error:
The error clearly points toward the user_name column in the WHERE clause, so you only need to change that.
If the error points toward the field_list as shown below:
- You have the right column names
- Any string type values are enclosed in a quotation mark
You can also check on the table name that you specified in the UPDATE statement and make sure that you’re operating on the right table.
Next, let’s look at how to fix the error on an ALTER TABLE statement
Fix ERROR 1054 on an ALTER TABLE statement
The error 1054 can also happen on an ALTER TABLE statement.
For example, the following statement tries to rename the displayname column to realname :
Because there’s no displayname column name in the table, MySQL will respond with the ERROR 1054 message.
Conclusion
In short, ERROR 1054 means that MySQL can’t find the column name that you specified in your SQL statements.
It doesn’t matter if you’re writing an INSERT , SELECT , or UPDATE statement.
- Make sure you’ve specified the right column name in your statement
- Make sure that any value of string type in your statement is surrounded by a quotation mark
You can check on your table structure using the DESCRIBE or EXPLAIN statement to help you match the column name and type with your statement.
And that’s how you fix the MySQL ERROR 1054 caused by your SQL statements.
I hope this tutorial has been useful for you 🙏
Take your skills to the next level ⚡️
I’m sending out an occasional email with the latest tutorials on programming, web development, and statistics. Drop your email in the box below and I’ll send new stuff straight into your inbox!
About
Hello! This website is dedicated to help you learn tech and data science skills with its step-by-step, beginner-friendly tutorials.
Learn statistics, JavaScript and other programming languages using clear examples written for people.
Search
Type the keyword below and hit enter
Tags
Click to see all tutorials tagged with:
How to fix MySQL unknown column in field list error
The MySQL unknown column in field list error happens when you put a column name in your SQL script that can’t be found by MySQL.
For example, suppose you have a table named students with the following data:
When you try to insert into a column that doesn’t exist in the table, MySQL will throw the said error:
The error above is because there’s no student_name column in the students table.
Next, the error can also be triggered because you didn’t use quotes for the string values as follows:
The value Michael must be wrapped in quotations ( » or «» ) or MySQL will think you are trying to insert values from another column to the target column.
The error can also be triggered by other things that make MySQL think that your column doesn’t exist.
One example is that you may trigger the error when calling a variable without the @ symbol as shown below:
Another thing that could trigger the error is that you’re using backticks to wrap literal values, as in the following UPDATE statement:
The English value for the subject field above should be wrapped by quotations (single or double) instead of backticks.
The error can also be triggered when you have a SELECT query that has different tables between the SELECT clause and the FROM clause as shown below:
This is because you are trying to query a column owned by students table from the cities table, which doesn’t match.
Finally, the error can also be caused by invisible characters lurking in your script that can’t be seen when you copy and paste it from other sources.
The only way to remove invisible characters is by rewriting the statements manually.
Summary
The tutorial above has listed the most common cause for MySQL unknown column in field list error.
There are many variations of SQL statements that can cause this error, so this error doesn’t really help you find the cause without understanding how MySQL syntax works.
I hope this tutorial has given you some guidelines on fixing the error. Good luck! 👍
Take your skills to the next level ⚡️
I’m sending out an occasional email with the latest tutorials on programming, web development, and statistics. Drop your email in the box below and I’ll send new stuff straight into your inbox!
About
Hello! This website is dedicated to help you learn tech and data science skills with its step-by-step, beginner-friendly tutorials.
Learn statistics, JavaScript and other programming languages using clear examples written for people.
Search
Type the keyword below and hit enter