Member Avatar for jrotunda85

I keep getting the following error whenever I try to run this query on my "spot" table:

#1054 - Unknown column 'a.Longitude' in 'field list'

The weird thing is though that the column DOES in fact exist, is spelled the same way, and has thousands of rows of data. Even weirder, a similar query runs on my "retail" table and has no problem getting the longitude, the only difference is 3 column names change (longitude and latitude stay the same). I also checked the datatypes for both tables and once again, exactly the same. If it matters, my mySQL version is 5.0.92-community. Anybody know what's going on here???? :@

Query

SELECT a.SpotID, a.CreatedBy, a.CreatedOn, a.SName, a.Addr_Street, a.Addr_City, b.Description, a.Addr_Zip, a.PhoneNbr, a.WebAddr, a.Latitude, a.Longitude, a.NoReviews, a.LastReview, a.AverageReview FROM spot a
LEFT JOIN cd_states b on a.Addr_State = b.Code
WHERE SpotID = 1
sql.png 12.37 KB
Edited by jrotunda85 because: n/a
  • 2 Contributors
  • 4 Replies
  • 125 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by jrotunda85

Dani AI

Generated

A concise troubleshooting checklist that complements 's advice and explains what helped : when a column appears in a full-row result but a targeted SELECT of that column fails, the cause is usually metadata, client-side rewriting, or a server bug. The steps below are ordered so the least intrusive checks come first.

Confirm the connection and the table DDL from the server (not just the GUI). Useful SQL checks:

SELECT DATABASE();
SHOW CREATE TABLE `your_table`;
SHOW FULL COLUMNS FROM `your_table`;

Look at the actual stored column names (not the GUI display). To detect hidden bytes or weird characters in the column name:

SELECT COLUMN_NAME, CHAR_LENGTH(COLUMN_NAME) AS len, HEX(COLUMN_NAME) AS hexname
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

Compare behavior from multiple clients. Run the same targeted SELECT from the mysql CLI, from your app, and from phpMyAdmin to see where it breaks. Capture the exact SQL the server receives by turning on the general log (requires SUPER):

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
SELECT event_time, argument FROM mysql.general_log WHERE command_type = 'Query' ORDER BY event_time DESC LIMIT 50;

Add simple PHP-level checks to get server errors and inspect returned field metadata:

if (! $result = $mysqli->query($sql)) { echo $mysqli->error; }
foreach ($result->fetch_fields() as $f) { echo $f->name . "\n"; }

If metadata looks correct but the problem only occurs in one client or one server build, collect the failing SQL from the general log and compare it to the working SQL. If everything else checks out, a server bug is likely; applying a recent patch or upgrading the server is a pragmatic fix (as reported by the original poster).

Recommended Answers

All 4 Replies

Member Avatar for jrotunda85

On another weird note, when I run the following it returns all fields (including Longitude) but when I run the second statement, it gives me the same #1054 error

Statement 1 (Returns Everything)

SELECT * FROM spot WHERE SpotID = 1

Statement 2 (Returns #1054 Error)

SELECT Latitude FROM spot WHERE SpotID = 1
Member Avatar for smantscheff

Try another interface than phpMyAdmin, e.g. command line mysql. I assume that phpMyAdmin twiddles your query. Look in the server log what the actual query is which the server complains about.

Member Avatar for jrotunda85

Try another interface than phpMyAdmin, e.g. command line mysql. I assume that phpMyAdmin twiddles your query. Look in the server log what the actual query is which the server complains about.

Well the whole reason I even asked was because when I'm trying to use this query from PHP it's not returning the value. So it's not working in PHP either - I'll check the logs though and see if I can find anything else.

Member Avatar for jrotunda85

Well, after fooling around with this last night, I just went ahead and upgraded to the newest version of mySQL (5.1.55) and it's working with the same query above. So I'm guessing it was a bug possibly in the earlier version that was fixed later in development.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.