CODEWITHSUNDEEP
mysqlselect
John Bull
John Bull

Reputation: 103

MySQL select statement not finding the column by name, what am I missing?

I have an MYSQL database with one table in it and I am trying to look up records by one of the columns. I thought this would be straight forward, but I am getting an error when I try and look up my data with a select statement.

Here is an image of my database columns. database columns

I thought the issue was with my code but then decided to run the query from the command line interface. Here are three examples of slightly different queries I have run. Where I am trying to return an item by its PLU number. select statments

I have renamed the column, tried changing the data type to INT and Text, and had the same result. Not really sure what I am missing.

Upvotes: 1

Views: 2622

Answers (3)

Uueerdo
Uueerdo

Reputation: 15941

You can use a query like this to help identify hidden character and leading/trailing spaces.

SELECT CHAR_LENGTH(COLUMN_NAME) AS nameLen, CONCAT('[',COLUMN_NAME,']') AS name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'myTableName'
;

If such problems are found, you can try to rename the problem field(s) via MySQL Workbench (or PhpMyAdmin, or whatever RDBMS tool you use).

However, if that fails (perhaps it is at the beginning or end and the text control used for the field name can't "grab" the character), you can create an empty copy of the table, with the correct field names in the same order, and then use INSERT INTO copy SELECT * FROM original (explicitly omitting the field names to avoid potential problems, which is why "same order" is important). Then just drop the original and rename the copy.

If there are foreign keys referencing the original, they will also need dropped before the rename, and recreated after.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108390

Looks to me like there's a "backspace" character (or some other non-ASCII or non-printable character) in the column name.

We can inspect a hexadecimal representation of the column name. We will assume the garfed charcacter is before of after the plu characters 

SELECT c.column_name
     , HEX(c.column_name)
  FROM information_schema.columns c 
 WHERE c.table_schema     = DATABASE()
   AND c.table_name       = 'pluDataSeed' 
   AND c.column_name   LIKE '%plu%'

We could leave the last condition off, to get all of the columns in the table. Or just add another condition to pick out the column, e.g. 

   AND c.ordinal_position = 1

We could also try using

 SHOW CREATE TABLE `pluDataSeed`

That should get us back a usable column name enclosed in backticks. (With non-ASCII characters, the character sets of the database and the client are going to come into play with characterset translations.)

We could do an ALTER TABLE statement to rename the column, but we are going to have to get the current name of the column into the statement. Copying from the CREATE TABLE statement might do the trick.

Upvotes: 0

NightDice
NightDice

Reputation: 126

SELECT * from pluDataSeed WHERE plu = '3345' is the correct syntax. Have you checked that you are using the apostrophe marks and not some other form of upticks? It looks like there's some mangled character messing up your query. (That mangled character might also be in the database column name)

Upvotes: 0

Related Questions