Is it possible to have numbers for field names?

[JohnnyT]

Is it possible to have a number as a field name?

ie. a table with the fields : id date description 1 4 etc etc

I'm having problems with the syntax for this query...
"update uploads set 1='Y' where id='3'"

Any ideas ?

Thanks in advance for your help

JT

 

[JohnnyT]

Its okay, I've sussed it...

The answer was to use back ticks around the field name so...

update uploads set 1='Y' where id='3'

becomes...

update uploads set `1`='Y' where id='3'

Just in case anyone ever has the same problem.

Cheers

JT

I don't make mistakes, I'm merely beta-testing life.

 

[sleipnir214]

I strongly recommend against this.

Although it is legal (through the use of backticks), MySQL gives you the ability to use descriptive column names so that you can make your database schema easier to understand.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[JohnnyT]

Thanks for the advice, although I think in this instance it should be ok.

The MySQL database is only viewed through the browser over the internet. The database should never be too big.

Its for my companies website. The idea is to have a list of registered users (ie. customers) who can log in to part of the website and download files pertaining to their company (ie. jobs we've done for them, software/drawings etc)

When I add a customer to the list, the script uses their unique id number to start a new field in the downloads table.

Via the admin area of the website you can then assign each customer with what files they are allowed to download.

It is working fine. Although I see your point entirely about it being hard to understand if you were looking through the database with the naked eye. However, in my case, the browser takes the information and puts it into forms that are easily readable for the admin to use via the website. Fingers crossed it should be ok.

Thanks for the advice,

Cheers

JT

I don't make mistakes, I'm merely beta-testing life.

 

[sleipnir214]

What you are doing is a monstrously bad habit to get into and extremely short-sighted.

The capability to name objects meaningfully does not exist simply to help you develop an application. The capability exists to help others maintain the application.

What's going to happen when someone else is tasked 6 months from now to modify the applicaton? Wouldn't meaningful column names be helpful to that person? Or yourself?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[JohnnyT]

No, it will be fine. The website is my company website, I'll be the only one maintaining it. As I said earlier, all the interfacing is done via the browser and so it doesn't really matter what the table looks like because the script takes the customers unique id and username upon logging in.

In then compares that unique id to the downloads table to ascertain which files they are eligible to download.

It then shows them a webpage of the files that they can download.

I've written an Administration section to the website (obviously only available if you log in and your account has admin permissions) and from here there is just a form and check boxes against each file on the system to allow myself to enable certain customers to be able to see certain files for download.

I do take your point about bad habits etc but in this exception, I really think it will be fine. There should be no reason to have to look at the database in the flesh as it were because all the additions and deletions to it can be done via the user-friendly admin section online.

Thanks again

JT

I don't make mistakes, I'm merely beta-testing life.

 

[sleipnir214]

It is wise to keep in mind the fact that you could be run over by a bus tomorrow. And should that unfortunate event transpire, your replacement will the day after tomorrow be calling you everything but the son of God.

Also, I don't know what you're storing in these fields, but you're also certainly using the database server inefficiently.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[JohnnyT]

I think if I was to be run over by a bus then the company would probably fold. There's only two of us in our company and I'm the MD.

As for using the database server ineffeciently, you are probably right. I'm not an expert at all this by any means. I'm self taught and when I get stuck I rely on tek-tips and similar forums and the good people that frequent them to help me out.

There may very well be a much better way to do this, but I wracked my brains and this was the idea that popped out. (it doesn't help that I haven't explained it very well)

As always, thanks for your help.

JT



I don't make mistakes, I'm merely beta-testing life.

 

[sleipnir214]

Cute. But that does not validate your use of numbers as column names. It is always and regardless of circumstances a bad programming practice.


As to using the database efficiently.

I would have had two tables: A client table and a download table.

The client table, of course, has a list of clients an information pertinent to your client and you application.

The download table would have a column to match the id of the client, and another column with the name of a single file available to that client. The table would then have a separate row for every file to which that client has access.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[JohnnyT]

What I have at present is two tables. One for customers and one for files to download.

The one for customers has the usual fields: Unique ID, Name, Company, Address etc

The one for files to download has: Unique ID, Filename, Description, Filetype

Because several customers may have permissions to access the same file. The way I have done it is this...

When a new customer is added to the database, the script adds a field to the downloads table using that customers unique ID. For instance, if I added a customer called Joe Bloggs and his Unique ID was 11, then this would add a field named '11' to the downloads table.

Then, when you come to setting the file permissions (ie. who can download which file), it lists all the files in the database and checkboxes. If you tick the checkbox it puts 'Y' in the field '11' or 'N' if you don't tick the checkbox.

This way, multiple customers can download the same file.

Hope that explains it well. If you have any ideas on improving this system and still achieving the same functionality then please let me know.

Thanks

JT

I don't make mistakes, I'm merely beta-testing life.

 

[sleipnir214]

I can definately improve on that. As a general rule, if you have to reserve lots of empty columns for an indefinate number of values or modify a table to add columns ad hoc to store an indefinate number of values, then that data should be stored in a separate table. I think of it as storing the data vertically rather than horizontally.

Here's what I would do:

Keep "customers" as it is.

Keep "downloads" as it is when you first create it. Keep only the ID, filename, description, filetype columns.

Then create a third relating table. It will have two columns: one for the unique customer ID, another for the unique file ID. An entry in this table will relate a customer to a file. Multiple files can be related to multiple customers -- just add records to this table as necessary to record the relation.

In case I'm not clear, please let me show an explicit example. Assume you have three customers and three files. Customer1 has access only to FileA, Customer2 has acess to FileB and FileC, and Customer3 has access only to FileC.

"Customers" would look something like:

CustomerID   CustomerName    ...
    1        Adam's Ales     ...
    2        Bill's Beers    ...
    3        Chuck's Cognacs ...

"files" would look something like:

FileId   Filename     Filedescription  ...
   1     aims.txt     a contract spec  ...
   2     beasts.gif   an image         ...
   3     coopers.pdf  an Acrobat doc   ...

Then the "customers2files" relating table would look something like:

Customer   File
  1         1
  2         2
  2         3
  3         3

Then to fetch all the files accessible by a single customer, perform a select query of the form:

SELECT *
FROM customers c, customers2files c2f, files f
WHERE c.CustomerID = c2f.Customer and
c2f.File = f.FileID and
c.CustomerID = <customer's ID>



Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[JohnnyT]

A superb idea. I'll change the database to reflect your way of doing it. A big thanks for all your help.

By the way, what does &quot;TANSTAAFL!!&quot; stand for at the bottom of your posts ?

Thanks again

JT

I don't make mistakes, I'm merely beta-testing life.

 

[sleipnir214]

&quot;There Ain't No Such Thing As A Free Lunch&quot;

It's from Robert Heinlein's The Moon is a Harsh Mistress

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[JohnnyT]

Nice one !

;-))

JT

I don't make mistakes, I'm merely beta-testing life.