Fix the SQL Error 1054: Unknown Column (How I fixed it in my PHP Project)
Last week I uploaded a new form to my cPanel hosting and got the error:
Nothing had changed in the code. The page simply wouldn’t load. Here are all of my causes to this error in my PHP projects.
1. Column Doesn’t Exist in Database
This was my issue. When I created the column staff_name in my local database, I forgot to add it to my live database.
$stmt = $pdo->prepare("INSERT INTO reports (staff_name, work_done) VALUES (?, ?)");
The table in my live database didn’t have the column staff_name. Always check your table to make sure. Run:
DESCRIBE reports;
And add the column:
ALTER TABLE reports ADD COLUMN staff_name VARCHAR(100) NOT NULL AFTER id;
Now when I make changes to my local tables, I’ll add the ALTER statement to a text document and run it live that day. Not very exciting but it works.
2. Typo
I once had a typo where I had called the column emp_id in the PHP, however the column was named employee_id. I stared at the line for 10 minutes and still couldn’t spot the issue.
-- Incorrect
SELECT emp_id, name FROM employees;
-- Correct
SELECT employee_id, name FROM employees;
Always copy the column names directly from phpMyAdmin to avoid this.
3. Using Variables without Quotes
This was the most confusing to me. If you add a variable into your query and forget to add quotes, MySQL will think you’re trying to select a column.
$name = "admin";
$sql = "SELECT * FROM users WHERE username = $name";
That runs as WHERE username = admin, so MySQL goes looking for a column called admin and throws Unknown column 'admin' in 'where clause'. Adding quotes would stop the error, but the real fix is a prepared statement:
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->execute([':username' => $name]);
By using quotes there are no issues with the quotes and you also prevent SQL Injection.
4. Using Alias in WHERE Clause
SELECT salary * 12 AS yearly_salary
FROM employees
WHERE yearly_salary > 500000;
This won’t work as the WHERE clause will execute before the alias is created. Either repeat the column or use HAVING.
SELECT salary * 12 AS yearly_salary
FROM employees
WHERE salary * 12 > 500000;
-- OR --
SELECT salary * 12 AS yearly_salary
FROM employees
HAVING yearly_salary > 500000;
5. Incorrect Alias in JOIN
SELECT a.name, b.department_name
FROM employees a
JOIN departments b ON a.dept_id = d.id;
I had created the table with alias b however I used d.id. This will throw an error: Unknown column 'd.id' in 'on clause'. This is common if you copy a query and change some of the names.
JOIN departments b ON a.dept_id = b.id;
6. Space in Column Name
I once imported a CSV into phpMyAdmin and one of the columns had a space at the end: 'name '. Whenever I tried to select the column name, I would get an error. The column appeared fine in the table. However, spaces cannot be seen.
ALTER TABLE customers CHANGE `name ` name VARCHAR(100);
7. Using Keywords
If you have a column named order, group or desc you’ll get errors. You need to put backticks around the column names.
SELECT `order`, `group` FROM items;
Just don’t call your columns these names. Use order_no and group_name.
8. Renamed Column in UPDATE/INSERT
UPDATE products SET price = 500, stock_qty = 10 WHERE id = 3;
If you renamed stock_qty to stock last month this will fail. After renaming any columns always search your entire project for the previous name. I use Ctrl + Shift + F in VS Code and this has caught me out a few times.
What I do now
- Read the error. The error will tell you the column name and where it failed.
- Run
DESCRIBE table_name;on the same database as the error. - Compare the names character by character.
- Check for unquoted values.
- Check aliases in JOINs.
- Compare the table structure between local and live.
Most of the time it will be either 1 or 3. The error seems daunting but it just means that MySQL couldn’t find a name you gave it.
Post a Comment