Table and Field Name with Spaces 1

[romh]

hi. I just converted an Acces 2000 database to mysql by exporting all of the tables from Access to mysql via ODBC and myODBC connection. Everything went well.
Now I am having trouble using select queries in mysql with table and field names that have spaces. I have read that field and table names with spaces should never be used. Do I have to remove all the table and field spaces? I have like 50 tables with spaces and columns in them. Can I surround these name with brackets [] or something else like in Access VBA.
Thanks alot.

 

[cLFlaVA]

You can use single quotes.

[tt]
SELECT 'column name'
FROM 'table name'
[/tt]

Just to state the obvious - table names and column names with spaces is not a good idea!

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[romh]

That didn;t work. The results just had the name of the column and not the actual data. Any more ideas? Thanks alot.

 

[cLFlaVA]

Yeah...sorry I wasted your time!

This will work:

[tt]
SELECT `column name`
FROM `table name`
[/tt]

Notice, this is not a single quote, but rather, a back-tick mark (usually under the escape button).

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[romh]

That worked like a charm. One more question:
Putting this back tick mark seems very annoying. Do all database engines support table or field names with spaces? For example, lets say that a year from now, I wanted to migrate from Mysql to Postgresql or MS SQL server, do they all support this spaces?

Do you think its best if I eliminate all spaces from my tables in Access and then export them again?

Thanks alot

 

[cLFlaVA]

I definitely think it's a good idea if this database will be around for a while. You could write a script of some sort using MySQL and PHP that will loop through all tables and columns and replace spaces with underscores. There would most likely be some research involved, but if you're interested, I'd be willing to help, along with, I'm sure, many other people on this forum.

Let us know.



*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[romh]

Alright. Thanks alot. I appreciate your help. Let me go through the database and see how many fields and columns I have with spaces exactly and also decide if its best to replace it with an underscore or simply eliminate the space all together. I am definately keeping the database for a very long time, so I want to make the best decision.
Thanks again

 

[snowboardr]

I would agree, spaces are bad in table names, room for errors I would suggest this GUI: mysql front 2.5 which is what I use to do all my MySQL work, for a few years now.

http://www.vzio.com/MySQL-Front_2.5_Setup.exe

www.sitesd.com
ASP WEB DEVELOPMENT

 

[snowboardr]

btw: its 1.02MBs

www.sitesd.com
ASP WEB DEVELOPMENT

 

[romh]

Thanks alot. I'll download it tomorrow. Its 4:14am down here.
I'll reply tomorrow and give you my comments
Thank you