create database with table in one click?

[techker]

hey guys i have a web app that i did for a school and i ran into a snag... cause every year they have fiscal years and different students...

 

so i was wondering.in the admin i wan't to predetermined the fiscal years.so the year after they click on the fiscal year and it creates a database (expl:2011-2012) with all the tables that i need for the app..

 

is that possible?

[Barand]

You could write a script containing a CREATE DATABASE query followed by the required CREATE TABLE queries.

[techker]

so i can export in phpmyadmin the structure then make a script.but it the name of the databse that needs to change every year..

[Barand]

something like

 

<?php
include("testDBconnect.php");

if (isset($_POST['dbname']) && $_POST['dbname'] != '')  {

    mysql_query("CREATE DATABASE $dbname");
    
    mysql_query ("CREATE TABLE a (yada yada)");
    mysql_query ("CREATE TABLE b (blah blah)");
    
    // etc
}

$year = date('Y');
?>

<form>
    Database name <input type="text" name="dbname" value="<?php echo 'dbname_'.$year?>">
    <br />
    <input type="submit" name="btnSub" value="Create database">
</form>

[techker]

ok so a variable would be need..ok i got it thx

[awjudd]

Is there really a need to spawn off a new database for each year instead of just relating everything to a specific year in the code (i.e. have a year code or something in the database?).

 

~awjudd

[techker]

ya now i have it stored with a fiscal year..me to i was thinking of that..think i will leave as is..so now in my admin he selects the fiscal year and it shows only that..

 

i was just debating if it would be more safe or practicle to have different databases every year..instead of loading all the same database every year...cause there's like over 1000 students..

[awjudd]

It is much more practical to have 1 database for every year and then you could even archive after X number of years.  You can get a fair bit more information out of the database if you had it all in one ...

 

~awjudd

[techker]

ok so yes it's more practical to have 1 db for every year but your saying i can get more out of one?

 

i can always use join to get more info..

[PFMaBiSmAd]

I believe there is a word-form issue in awjudd's post. He means - your application should have one database that holds all the data for your application. Each table within your database would hold all the same meaning data for that table, regardless of the date, so that it would be possible to easily access any/all of the same meaning data. You would have a date (or datetime) column in any table that holds the relevant date (or datetime) of the data in the row.

 

You should not be dynamically adding databases or tables within a database. That creates a data management nightmare. To prevent query errors, you must perform a query to check if a database/table exists, before you can query for the actual data or you must hard code some data that lists the database/tables that do exist and then have logic to check if a query is not trying to access data outside of the valid range.

 

Database tables with 10+ million rows are not a problem. In fact, the most common use of an unsigned integer as a primary key would allow for 4,294,967,295 rows in one table.

[The Little Guy]

To do this, you could do something like this(untested):

 

<?php
require_once "db_connection.php";
require_once "MysqlClass.php";
$database_name = "test";
$new_database_name = "test2";

$create_table = array();
$tables = array();

// User, password, host, database
$db = new Mysql("user", "password", "localhost", "test");

$db->query("show tables");
while($row = $db->row()){
$tables[] = $row["Tables_in_".$database_name];
}
foreach($tables as $table){
$db->query("show create table $table");
$row = $db->row();
$create_table[$table] = $row["Create Table"];
}

// Create the new database and tables
$db->query("create database $new_database_name");
$db->query("use $new_database_name");
foreach($create_table as $table => $create){
$db->query($create);
$db->query("alter table $table auto_increment = 0");
}

echo "\n\nNew Database Created!\n\n";
?>