By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

creating table - can't

P: n/a

I've read around, especially at mysql.com to see if there are any
limitations to tablenames, one is 64 characters and the other one seems to
be that a tablename can't exist only numeric.

So, within PHP I try to create a table with name "12345-name" and it fails
entirely.

It get's me rather puzzled as it should be possible according to the mysql
manual.
Of course, the table definition follows as it should. If i change the
tablename to only "name" .. then I can create the table under Mysql
4.0.11a.
But I want my tablenames to consist of faxnumber-name, nothing else.

Is there anything I missed somewhere that prevents me from doing what I
want?

I'm sorta confused here ...

/Andreas

--
Registeret Linux user #292411
Jul 17 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Works fine for me... just tried it with phpMyAdmin... try not using quotes,
or single quotes? Then again I do believe im on MySQL 3.X... maybe thats
it?
-Eric Kincl

Andreas Paasch wrote:

I've read around, especially at mysql.com to see if there are any
limitations to tablenames, one is 64 characters and the other one seems to
be that a tablename can't exist only numeric.

So, within PHP I try to create a table with name "12345-name" and it fails
entirely.

It get's me rather puzzled as it should be possible according to the mysql
manual.
Of course, the table definition follows as it should. If i change the
tablename to only "name" .. then I can create the table under Mysql
4.0.11a.
But I want my tablenames to consist of faxnumber-name, nothing else.

Is there anything I missed somewhere that prevents me from doing what I
want?

I'm sorta confused here ...

/Andreas


Jul 17 '05 #2

P: n/a
On Mon, 10 Nov 2003 23:39:34 +0100, Andreas Paasch <An*****@Paasch.Net> wrote:
I've read around, especially at mysql.com to see if there are any
limitations to tablenames, one is 64 characters and the other one seems to
be that a tablename can't exist only numeric.

So, within PHP I try to create a table with name "12345-name" and it fails
entirely.
Post error messages and example code.

To create a table with a bad name like that requires quotes around the name.
The type of quotes depends on with what options the MySQL server was started.
If you ever need to quote identifiers, it almost always means you chose a bad
name for the identifier...
It get's me rather puzzled as it should be possible according to the mysql
manual.
Of course, the table definition follows as it should. If i change the
tablename to only "name" .. then I can create the table under Mysql
4.0.11a.
But I want my tablenames to consist of faxnumber-name, nothing else.


Why are you encoding data in the table name?

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 17 '05 #3

P: n/a
Andy Hassall wrote:
On Mon, 10 Nov 2003 23:39:34 +0100, Andreas Paasch <An*****@Paasch.Net>
wrote:
[snip]
Post error messages and example code.
Sorry, I decided not to post those as I didn't think they were of value as I
know the problem lies in the naming structure of the table to create ..
just showing it here anyways, just in case ....

Code part:
$query = "CREATE TABLE '$folder'
... then follows the table definition ...
in this instance is exactly $folder = "38747117-robertos"

[errormsg]
right syntax to use near ''38747117-robertos'
[/errormsg]

As mentioned, if I replace '$folder' with petersen, the table is created
perfectly, so it's gotta be the name of the table.

To create a table with a bad name like that requires quotes around the
name.
The type of quotes depends on with what options the MySQL server was
started. If you ever need to quote identifiers, it almost always means you
chose a bad name for the identifier...

[snip]

It's actually not data, it's a faxnumber to distinguish several conflicting
names. No workaround for the name ...

--
Registeret Linux user #292411
Jul 17 '05 #4

P: n/a
Semicolon?? Can't forget those little POSes now, can we?
-Eric Kincl

Andreas Paasch wrote:
Andy Hassall wrote:
On Mon, 10 Nov 2003 23:39:34 +0100, Andreas Paasch <An*****@Paasch.Net>
wrote:

[snip]

Post error messages and example code.


Sorry, I decided not to post those as I didn't think they were of value as
I know the problem lies in the naming structure of the table to create ..
just showing it here anyways, just in case ....

Code part:
> $query = "CREATE TABLE '$folder'
... then follows the table definition ...

in this instance is exactly $folder = "38747117-robertos"

[errormsg]
right syntax to use near ''38747117-robertos'
[/errormsg]

As mentioned, if I replace '$folder' with petersen, the table is created
perfectly, so it's gotta be the name of the table.

To create a table with a bad name like that requires quotes around the
name.
The type of quotes depends on with what options the MySQL server was
started. If you ever need to quote identifiers, it almost always means
you chose a bad name for the identifier...

[snip]

It's actually not data, it's a faxnumber to distinguish several
conflicting names. No workaround for the name ...


Jul 17 '05 #5

P: n/a
On Tue, 11 Nov 2003 00:02:09 +0100, Andreas Paasch <An*****@Paasch.Net> wrote:
Andy Hassall wrote:
On Mon, 10 Nov 2003 23:39:34 +0100, Andreas Paasch <An*****@Paasch.Net>
wrote:
[snip]

Post error messages and example code.


Sorry, I decided not to post those as I didn't think they were of value as I
know the problem lies in the naming structure of the table to create ..
just showing it here anyways, just in case ....

Code part:
> $query = "CREATE TABLE '$folder'
... then follows the table definition ...


Single quotes '' are for string literals, not identifiers.

In ANSI mode, quoted identifiers are surrounded by double quotes "" (as in
many other databases).

MySQL tends to use backticks `` though.

See the manual.
http://www.mysql.com/documentation/m...ml#Legal_names
in this instance is exactly $folder = "38747117-robertos"

[errormsg]
right syntax to use near ''38747117-robertos'
[/errormsg]
Doesn't look like a MySQL 3.x error, what version are you running? You're also
missing the start of the error message.
It's actually not data, it's a faxnumber to distinguish several conflicting
names. No workaround for the name ...


You could always put the number at the end and drop the '-' so it's a normal
name, e.g. robertos38747117.

Looks like 'robertos' is a case of encoding data in a name too, then?

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 17 '05 #6

P: n/a
Andy Hassall wrote:
On Tue, 11 Nov 2003 00:02:09 +0100, Andreas Paasch <An*****@Paasch.Net>
wrote:

[snip]

Single quotes '' are for string literals, not identifiers.

In ANSI mode, quoted identifiers are surrounded by double quotes "" (as
in
many other databases).

MySQL tends to use backticks `` though.

See the manual.
http://www.mysql.com/documentation/m...ml#Legal_names


The backtic did the trick ... first time I realized that a single quote
isn't a single quote, it's actually a backtick.

Thanks a lot.

/Andreas

--
Registeret Linux user #292411
Jul 17 '05 #7

P: n/a
Andreas Paasch wrote:
So, within PHP I try to create a table with name "12345-name" and it fails
entirely.


Can I assume you are creating tables at runtime?
Unless you are explicityly doing this to handle bulk ware-house type
stuff, then you'r probably going about things the wrong way. You should
be able to scale your data without need to add entities (ie. modify your
data structure).

What is it that you are really trying to do?
Jul 17 '05 #8

P: n/a
Terence wrote:
Andreas Paasch wrote:
So, within PHP I try to create a table with name "12345-name" and it
fails entirely.


Can I assume you are creating tables at runtime?
Unless you are explicityly doing this to handle bulk ware-house type
stuff, then you'r probably going about things the wrong way. You should
be able to scale your data without need to add entities (ie. modify your
data structure).

What is it that you are really trying to do?

What I'm really trying to do is write some administrative pages that handle
my import and selection of data based on a cvs-file.

As I assume to get more and more customer into my system, it's a lot easier
to automate the procedure.
So I create a customer and import that customers cvs-file according to a
very special structure which is written into the cvs-filename.
That file name in turn is split and creates then the folder structure for
easy access later on and in order to keep consensus.

I doubt I go about it the wrong way, just lack some in-depth understanding
....

/Andreas

--
Registeret Linux user #292411
Jul 17 '05 #9

P: n/a
On Tue, 11 Nov 2003 10:44:55 +0100, Andreas Paasch
<An*****@Paasch.Net> wrote:
What I'm really trying to do is write some administrative pages that handle
my import and selection of data based on a cvs-file.

As I assume to get more and more customer into my system, it's a lot easier
to automate the procedure.
So I create a customer and import that customers cvs-file according to a
very special structure which is written into the cvs-filename.
That file name in turn is split and creates then the folder structure for
easy access later on and in order to keep consensus.


I think you're equating database tables to filesystem folders. A
relational database isn't a filesystem. If you are creating customers,
you should have a single `customer` entity (table).

This table would have fields such as customer id, name, etc.

As you create customers, add records to this table.

Your database design should reflect the logical view of the data, as
opposed to how it is physically stored.

--
David ( @priz.co.uk )
The Internet Prisoner Database: http://www.priz.co.uk/ipdb/
The Tarbrax Chronicle: http://www.tarbraxchronicle.com/
Jul 17 '05 #10

P: n/a
Andreas Paasch wrote:
Terence wrote:

Andreas Paasch wrote:

So, within PHP I try to create a table with name "12345-name" and it
fails entirely.


Can I assume you are creating tables at runtime?
Unless you are explicityly doing this to handle bulk ware-house type
stuff, then you'r probably going about things the wrong way. You should
be able to scale your data without need to add entities (ie. modify your
data structure).

What is it that you are really trying to do?


What I'm really trying to do is write some administrative pages that handle
my import and selection of data based on a cvs-file.

As I assume to get more and more customer into my system, it's a lot easier
to automate the procedure.
So I create a customer and import that customers cvs-file according to a
very special structure which is written into the cvs-filename.
That file name in turn is split and creates then the folder structure for
easy access later on and in order to keep consensus.

I doubt I go about it the wrong way, just lack some in-depth understanding
...

/Andreas


You don't want a relational database system, you want a native XML
database system.

http://xml.apache.org/xindice/
or
http://exist.sourceforge.net/

There are commercial ones if you need hardcore scalability. Tamino is
the best one I've seen -- it's not cheap.
Jul 17 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.