[SOLVED] Creating New MySQL Tables With an Auto Incrementing Name

[Akenatehm]

Hey Guys,

 

What I need to do is create a table that has a name auto incrementing. For example:

 

Creates Table "table1"

 

Creates Table "table2"

 

Creates Table "table3"

 

The script is querying the database to see what the next number is

 

OR

 

Another Script Somewhere Else Tries to Create "table2"

 

It finds out that it already exists.

 

and then it keeps going up the numbers. "table4" etc. till it finds one that isn't taken up.

 

Any help will be greatly appreciated.

[trq]

This smells of poor design, why exactly would you be needing to do this?

[Akenatehm]

We are creating an instant messaging client. This would be creating the chat rooms, where all the conversations are stored. Messages and Senders will be listed in here.

Here is how I mean:

 

chatroom1

chatroom2

chatroom3

to

chatroom40 etc etc

[trq]

You would still be better off using one table. You'd just create a field to store the chat room number in.

[Akenatehm]

But wouldn't that create huge server load if there are say.... 100 conversations at once all connecting to the same database?

[trq]

But wouldn't that create huge server load if there are say.... 100 conversations at once all connecting to the same database?

 

All tables are going to be within the same database on the same server anyway.

[Akenatehm]

Ok. So how do you suggest I lay the table out, what field types?

 

Going to have following fields:

 

chatid, username, date

[Akenatehm]

Anyone?

[corbin]

Well, you'll have your users table, then you'll have your chatrooms table, then you'll have your messages table:

 

CREATE TABLE msgs (

    user_id INT NOT NULL, --author

    chatroom_id INT NOT NULL,

    message_content text

);

 

 

Edit:  Google "database normalization" and you might develop a better approach of how to store data.

[Akenatehm]

Thanks for your help.