Re: How can I use PHP to duplicate a mysql template database?

At 07:59 PM 12/16/02 -0800, Daevid Vincent wrote:

I need to use PHP to duplicate the schema of a database. This seems like it should be a simple task -- taking a 'template' db and cloning it with a new name.

The easiest way I know of is at the command line:

mysqldump -p -u root name_of_existing_database >schemafile

mysql -p -u root mysql> create database newdatabase;

mysql> grant whatever on newdatabase.* to whoever mysql> exit

mysql -p -u root newdatabase < schemafile

The commands typed into the mysql interpreter must be done by a user with rights to create databases, the final mysql command where you import the schemafile must be done with a user having rights to create tables within the new database, the mysqldump can be done by any user with select rights to all tables in the existing database.

And

                $sql .= "CREATE TABLE IP_Dept (";
                $sql .= "  IP_Addr int(10) unsigned NOT NULL default

'0',";

                $sql .= "  DeptID int(10) unsigned NOT NULL default

'0'";

                $sql .= ");";

With MySQL, you only get one sql statement per call to mysql_query, so call it here, empty the $sql variable and start on the next table. Also, a trailing semicolon is not allowed in the $sql variable, so end with $sql .= ")"; instead of $sql .= ");"; Don't complain, this is a feature that prevents a common database attack where someone adds ;drop table users; at the end of one of your queries by poisoning user input.