Jump to content
xisto Community
Sign in to follow this  
suicide1405241470

Generating Many Tables

Recommended Posts

I have a database that will contain 43 tables (among others). For these particular tables, they will each contain the same type of information, each representing a particular week in the NASCAR season. I used the following script to create one table:DROP TABLE IF EXISTS gulfOwnerDriversWeek1;CREATE TABLE gulfOwnerDriversWeek1 (ownerIndex int primary key,driverIndex1 int,driverIndex2 int,driverIndex3 int,driverIndex4 int);This table represents a draft sheet for one particular week (Week 1) for a particular team. My first question is, should try to put all weeks (43 of them) into one database, or should I keep each week in its own table (seems to be the smartest method). The only way I know to create a table right now is either through the mysql command line or a simple script. To create every table I need for my league in this manner would be very difficult. Is there an easy way to create 43 identical tables where the only discrepency between the table names would be the number on the end: gulfOwnerDriversWeek'X'? I have lots of tables like this for stats & what not and can't bring myself to create every one separetly at the command line. Thanks.

Share this post


Link to post
Share on other sites

use looping w/ php

$starting_count = ???;$howmany_loop = ???;$key = "ownerIndex int primary key, driverIndex1 int, driverIndex2 int, driverIndex3 int, driverIndex4 int";for($var = $starting_count; $var <= $howmany_loop; $var++){    mysql_query("CREATE TABLE gulfOwnerDriversWeek".$var."(".$key.")");}

Share this post


Link to post
Share on other sites

Another approach to this problem is to "merge" week info into your drivers table, instead of spreading it all over 43 tables, requiring in this way a script language to manage it on an higher abstraction level.

If you add and ID_WEEK column to gulfOwnerDrivers table, setting ID_WEEK from 1 to 43 for every set of drivers per week, you'll be able to store all your data in one table.

All queries concerning this table will be constrained by a WHERE ID_WEEK = <week> clausole in order to select every time only one particular week results.

prototype of query for gulfOwnerDrivers:

SELECT ownerIndex,driverIndex1 int,driverIndex2 int,driverIndex3 int,driverIndex4 intFROM gulfOwnerDriversWHERE ID_WEEK = <week>
<week> is an alias for a certain number of week.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.