A hash at the end of a fieldname does not work

[MARTIN DAVIES]

I hope you can help.

My Linux MYSQL 3.23.52 database server is working OK generally.

However, in a database I have a field name that ends with a # (hash UK as
opposed to £ which is a pound UK) i.e. [empnumber#]

If I "select * from emptable" it works fine and the empnumber# field is
listed correctly with all the other fields.

If I "select empnumber# from emptable" I get returned "unknown column
'empnumber' in 'field list'" (#1054) from Access 2000.

If I use Mysql on the server I get similar results.

I cannot change the field names as these are constrained by the fact that
this is not a new design of database.

Any Ideas?

Martin.

[Chris Hope]

MARTIN DAVIES wrote:

I hope you can help.

My Linux MYSQL 3.23.52 database server is working OK generally.

However, in a database I have a field name that ends with a # (hash UK as
opposed to £ which is a pound UK) i.e. [empnumber#]

If I "select * from emptable" it works fine and the empnumber# field is
listed correctly with all the other fields.

If I "select empnumber# from emptable" I get returned "unknown column
'empnumber' in 'field list'" (#1054) from Access 2000.

If I use Mysql on the server I get similar results.

I cannot change the field names as these are constrained by the fact that
this is not a new design of database.

Any Ideas?

Use backticks ` around the fieldnames eg
SELECT `empnumber#` FROM emptable

I just tested it and it works with backticks if the fieldname ends in a
hash. Using square brackets around the fieldnames is what you do with MSSQL
Server and it doesn't work with MySQL (at least it didn't when I just tried
now).

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/

[MARTIN DAVIES]

Chris,

Thanks for the suggestion.
I have tried it and the error message goes away, but for each record
returned, the field seems to be the string : "employee#" rather than the
actual data from the table column headed employee#.

What did you get?

If you have any further suggestions I will be grateful.

Thanks,

Martin.

cc rest of group : Any (polite) suggsestions welcome


Chris Hope <ch***@electrictoolbox.com> wrote in message
news:10*************@news.athenanews.com...

MARTIN DAVIES wrote:

I hope you can help.

My Linux MYSQL 3.23.52 database server is working OK generally.

However, in a database I have a field name that ends with a # (hash UK as opposed to £ which is a pound UK) i.e. [empnumber#]

If I "select * from emptable" it works fine and the empnumber# field is
listed correctly with all the other fields.

If I "select empnumber# from emptable" I get returned "unknown column
'empnumber' in 'field list'" (#1054) from Access 2000.

If I use Mysql on the server I get similar results.

I cannot change the field names as these are constrained by the fact that this is not a new design of database.

Any Ideas?
Use backticks ` around the fieldnames eg
SELECT `empnumber#` FROM emptable

I just tested it and it works with backticks if the fieldname ends in a
hash. Using square brackets around the fieldnames is what you do with

MSSQL Server and it doesn't work with MySQL (at least it didn't when I just tried now).

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/

[MARTIN DAVIES]

Oops, a slight mistake in the last request for help.
I used ' (single quote) and not ` (backticks) as advised.

It did not fix the problem anyhow, but the symptom I described is not
aligned with back ticks.

With back ticks I still get "unknown column 'employee"

I still need help though!

Regards,

Martin.
MARTIN DAVIES <ma***********@tesco.net> wrote in message
news:X4**************@newsfe3-win.server.ntli.net...

Chris,

Thanks for the suggestion.
I have tried it and the error message goes away, but for each record
returned, the field seems to be the string : "employee#" rather than the
actual data from the table column headed employee#.

What did you get?

If you have any further suggestions I will be grateful.

Thanks,

Martin.

cc rest of group : Any (polite) suggsestions welcome


Chris Hope <ch***@electrictoolbox.com> wrote in message
news:10*************@news.athenanews.com...

MARTIN DAVIES wrote:

I hope you can help.

My Linux MYSQL 3.23.52 database server is working OK generally.

However, in a database I have a field name that ends with a # (hash UK as opposed to £ which is a pound UK) i.e. [empnumber#]

If I "select * from emptable" it works fine and the empnumber# field is listed correctly with all the other fields.

If I "select empnumber# from emptable" I get returned "unknown column
'empnumber' in 'field list'" (#1054) from Access 2000.

If I use Mysql on the server I get similar results.

I cannot change the field names as these are constrained by the fact that this is not a new design of database.

Any Ideas?

Use backticks ` around the fieldnames eg
SELECT `empnumber#` FROM emptable

I just tested it and it works with backticks if the fieldname ends in a
hash. Using square brackets around the fieldnames is what you do with

MSSQL

Server and it doesn't work with MySQL (at least it didn't when I just

tried

now).

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/

[Chris Hope]

MARTIN DAVIES wrote:

Oops, a slight mistake in the last request for help.
I used ' (single quote) and not ` (backticks) as advised.

It did not fix the problem anyhow, but the symptom I described is not
aligned with back ticks.

With back ticks I still get "unknown column 'employee"

I still need help though!

Re-reading your original post, I saw you'd also been using Access. Remember
that the query in Access will need [square brackets] and the query in MySQL
will need `backticks`. You won't be able to use the same query for both.

And from the example you have supplied in this post you say the error
message is "unknown column 'employee" - did you copy and paste that error
or type it in? If copy and paste, then you don't have the # at the end of
the fieldname (if you typed it in then I'd assume you just forgot to type
it in).

Assuming the query is successful, and the data is loaded into an array using
mysql_fetch_assoc() or mysql_fetch_array() you need to make sure you use
quotes to reference the value eg $foo['employee#'] or $foo["employee#"]
where $foo is the array returned by the aforementioned functions (it's good
practice to always use quotes for associative array indexes anyway).

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/