Create a new table via script?

[WATRD]

I am relatively new to coding for MySQL and need a bit of help getting started.

What I want to do, is to run a script that creates a new table with the table name being incremented by one from any existing tables.

That is, if table "events1" exists, running the script will create table "events2" with several standard rows. The primary key for the table will be "eventid" and the value of that will be the same as the incremented table name.

Can you help me out here?

[Pig]

I would rethink how you are setting this up. There is really no need to do this, and depending on how you are doing it, it could get really ugly. You are better of creating one table, and using a column to track which group those records belong to. (or creating a many-to-many table to store the relationship, it depends on what you are doing.)

If you are determined to do it your way, you can set up a table they way you want in phpmyadmin, then copy the dump structure of hte table. Use this as the base for your table. When you want to create the table, use the show tables command to get all the table names. do a little string manipulation to find the last table in the sequence, increment it by one, and alter the dump text using the new table name. Then you can run the command with mysql_query.

[BerksWebGuy]

Just to add...

Having one table could be a better solution...and if you ever have to...joining tables can be painful.

Anyway...the mysql_query would look like this...but you would get this from doing what Pig explained above:

mysql_query("CREATE TABLE table_name (ID mediumint(7) unsigned NOT NULL auto_increment, fieldone tinytext NOT NULL, field2 tinytext NOT NULL, fieldthree mediumtext NOT NULL, fieldfour tinytext NOT NULL, UNIQUE KEY ID (ID))");

[WATRD]

Thanks for the replies guys.

What I am trying to build is an RSVP system for club events. My thinking was that if I had a seperate table for each event, I could then have all the event details and the RSVP details isolated for each event. Then I could go in and archive off old tables and drop them when they were no longer needed.

However, I am a noob at designing SQL DB's, so I may be coming at this from completely the wrong direction.

Whatever the final solution, it will need to contain event details, including event id, description, location, meet time, etc... Then there will be another script that users would use to RSVP. That would include their name, some vehicle details and a "PIN" of sorts that would allow them to un-rsvp. So, there will be a block of standard information for every event, the event details, then an unknown number of RSVP's for each event, each of which contains a known number of variables.

Does that still sound like I am barking up the wrong tree? Should all of this be in one table and keyed off the event id? I have a half-dozen MySQL "How To" books open around me and am finding that you definately want to design the DB right from the beginning because it's no fun to change it later on...

[Pig]

You should do all of this with one table for all events. eg: have a table called event with all of it's pertinent information, and have a table called reservations with all the user information and the event table key to relate the two

[mjp]

And you can dig out the relevant RSVP info with a subquery (http://dev.mysql.com/doc/mysql/en/subqueries.html) if you find joins to be a pain...

SELECT * from RSVP
WHERE EventID = (SELECT EventID from Events
WHERE Event [= or like] [some match]);

But then I'm a bit of a subquery evangelist since replacing a few lengthy joins with much easier-to-follow subqueries. As always, your results may vary.

[WATRD]

Thanks folks, your were right on track with this. I finally conceded that I didn't know what I was doing and chased down one of the SQL gurus at work. I presented him with flow charts and specifications for what I was trying to do and he agreed with you. His reaction was;

"Are you smoking crack? You shouldn't be creating new tables on the fly for this project, that should be static. It's bad DB design to need to create new tables on the fly."

Okay, I get the picture... it's unanimous.

So, with his help, I designed a better system with three tables. One for user data, one for event data and one he called the "join table" where it maps users to events. This addresses all my needs, including being able to clean up the DB when it starts to get large, as well as allowing me to easily persist user data for future use and persist event data for future events at the same location.

Thank you for setting me straight :)