Dot in the username is creating problems.

[Kavitha Butchi]

Hello all,

I have this strange problem.
During registration if user chooses to have '.' in his username, eg: sam.leo then i am getting this wierd problem .

-respective tables with the given username are not getting created in the Database.

Everything is working fine if usernames are having no special characters.
Other than asking users not to use any special characters, how can I solve this!!

Can someone tell me why is this happening and how to get rid off this :(

Thankyou all in advance.

[Dani]

We would need to see your code / mysql queries where the rows are being added to the table.

[Kavitha Butchi]

Thankyou for the quick reply.

Here is the related code.

//Code for creating a table --[not wokring when dot is used in username]

mysql_connect(" ---") ;
mysql_select_db("---");

mysql_query("CREATE TABLE $username(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
 name VARCHAR(30), 
 order VARCHAR(990),
date datetime NOT NULL default '0000-00-00 00:00:00')");  


mysql_connect("---") ;
mysql_select_db("---");



//creating table another table[-not working when dot is used in username--]

mysql_connect("------") ;
mysql_select_db("-----");

mysql_query("CREATE TABLE $displayname(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
likeditem VARCHAR(30))")
;

-> Tables in a database are not getting created when a dot is used in username.
-> But records are getting created in a table(already created tables which are same for all users)

Everything is working fine when dot is not used.

using=> Godaddy hosting connection, mysql databases.

Please let me know if i need to post anyother details.

[somedude3488]

i personally don't recommend making a new table for every user. it may be organized but is difficult to deal with. i would make a users table, then any other tables you would need and you ids to reference info in different tables.

[buddylee17]

I agree with keith. What happens when you have 100 users? You are going to have 100 user tables with one row of data in each. Bad design. All of this could be done with 1-3 properly normalized tables.

[Kavitha Butchi]

I agree with keith. What happens when you have 100 users? You are going to have 100 user tables with one row of data in each. Bad design. All of this could be done with 1-3 properly normalized tables.

Thankyou fr your time buddylee17.
The individual tables created in my database are used to keep track of all the chat conversations of the user.
I considered that option long before while creating tables with the usernames but was taken back with the idea that it may be well organised rather than creating feilds or records in a single table.
But now seems like there is no other option left and hence shall think abt modifying the structure.

Can someone tell me is there any other solution for this problem?!
Also,
Which one is better?I mean which one is of low burden to the database.

-100 feilds in a table.
-100 records in a table.
-100 tables in a database.

Thankyou all in advance.

[aparnesh]

Having a dot in the username will not work because most SQL languages use the dot as a relation. So an username like "Jesus.Christ" will try to create a table called 'Christ" in the schema "Jesus". As there is a very low probability of the schema being actually "Jesus", the table creation will fail.
To check, try using your schema.something as username. For example, if your schema is called "MySchema", create an user as "MySchema.NewUser". This should work and create a table called "NewUser" (NOT MySchema.NewUser) in the schema "MySchema".
As for the rest of your question, having a table for each user is not really a good idea. Having a table with 100 fields is even worse. If you need to look up a non-indexed field, the DB Engine will have to read through all 100 fields.
Best solution = 100 records in 1 table, properly indexed. As far as practicable, try normalising the tables so that data-scans are faster.

[Kavitha Butchi]

Got it!! :)
Thanks a Ton Aparnesh for the detailed explaination.