From: Daevid Vincent
Date: December 17 2002 10:31am
Subject: RE: How can I duplicate a mysql template database? [hack]
List-Archive: http://lists.mysql.com/mysql/127673
Message-Id: <002001c2a5b7$70ff4150$0201a8c0@telecom>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Seems to me there should be a built in SQL command to duplicate a
database. Jeepers. Or to read in a .sql file from PHP and create a
database out of it (which was the original intent).

Anyways, here's a 'hack'. I'd still love to hear anyone else's more
elegant solution.

$V2DB = "V2_SL".$CompanyID;

$result = mysql_create_db($V2DB, $linkI);
if (!$result) $errorstring .= "Error creating ".$V2DB."
database<BR>\n".mysql_errno($linkI).": ".mysql_error($linkI)."<BR>\n"; 

mysql_select_db ($V2DB, $linkI) or die ("Could not select ".$V2DB."
Database");
/*
//TODO: None of these below here work. Ugh! so frustrating!!
//$filename = "/mypath/todb/V2_DB.sql";
//$fd = fopen ($filename, "r");
//$sql = fread ($fd, filesize ($filename));
//fclose ($fd);

//$lines = file($filename);
//foreach ($lines as $line_num => $line) { $sql .= $line; }

//$sqlTables = explode(";",$sql);
//foreach ($sqlTables as $table)
//{
//	echo "<PRE>$table</PRE><p><hr>\n";
//	$result = mysql_query($sql,$linkI);
//	if (!$result) $errorstring .= "Error creating ".$V2DB."
".$table." table<BR>\n".mysql_errno($linkI).":
".mysql_error($linkI)."<BR>\n"; 
//}
*/

//You must have already created the "V2_Template" database. 
//This will make a clone of it, including data.

$tableResult = mysql_list_tables ("V2_Template");
while ($row = mysql_fetch_row($tableResult)) 
{
	$tsql = "CREATE TABLE ".$V2DB.".".$row[0]." AS SELECT * FROM
V2_Template.".$row[0];
	echo $tsql."<BR>\n";
	$tresult = mysql_query($tsql,$linkI);
	if (!$tresult) $errorstring .= "Error creating
".$V2DB.".".$row[0]." table<BR>\n".mysql_errno($linkI).":
".mysql_error($linkI)."<BR>\n"; 
}


> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped] 
> Sent: Tuesday, December 17, 2002 1:16 AM
> To: mysql@stripped
> Cc: 'Bill Lovett'
> Subject: RE: How can I duplicate a mysql template database?
> 
> 
> Thanks for the reply Bill, however this did not solve the problem. I
> still receive the same error with your method as well.
> 
> 1064: You have an error in your SQL syntax near ';
> CREATE TABLE Departments (
>   DeptID int(10) unsigned NOT NULL auto_increment,' at line 4
> 
> > Are you first creating a new database first with mysql_create_db() 
> > before trying to run your schema? What error message do you 
> get? The 
> > code you've posted is only creating a table.
> 
> Yes. I am:
> 
> $V2DB = "V2_SL".$CompanyID;
> $result = mysql_create_db($V2DB, $linkI);
> mysql_select_db ($V2DB, $linkI) or die ("Could not select ".$V2DB."
> Database");
> 
> > Also, rather than opening and reading in an external file or 
> > doing all 
> > those $sql .= lines, you might have an easier time using 
> > heredoc syntax, 
> > which would let you do something like
> > 
> > $sql = <<<SQL
> > CREATE TABLE IP_Dept (
> >    IP_Addr int(10) unsigned NOT NULL default
> >    DeptID int(10) unsigned NOT NULL default
> > 
> >    ...etc
> > 
> > SQL;
> > 
> > $result = mysql_query($sql,$linkI);
> 
> Thanks for that tip, however it fails the same as opening the file and
> same as "$sql .="
> 
> > (hopefully $linkI is your mysql connection) This way you 
> > don't have to bother with all that quoting.
> 
> Yes, $linkI is my connection identifier.
> 
> I just have an awful feeling that PHP/mySQL won't let me 
> stack commands
> like that because if I just do one table, like this: 
> 
> $sql = <<<SQL
> CREATE TABLE Schedule (
>   ScheduleID int(10) unsigned NOT NULL auto_increment,
>   TargetRange char(255) default NULL,
>   ScannerID int(10) unsigned NOT NULL default '0',
>   RunEvery char(50) default NULL,
>   NextRun datetime default NULL,
>   LastRun datetime default NULL,
>   PRIMARY KEY  (ScheduleID)
> ) TYPE=MyISAM;
> SQL;
> 
> It "works". However I'm dreading doing this one table at a time. Grr.
> 
> > Daevid Vincent wrote:
> > > I need to use PHP to duplicate the schema of a mysql 
> database. This
> > > seems like it should be a simple task -- taking a 
> 'template' db and
> > > cloning it with a new name. 
> > > 
> > > I've tried things like:
> > > 
> > > 		$filename = "myDB.sql";
> > > 		$fd = fopen ($filename, "r");
> > > 		$sql = fread ($fd, filesize ($filename));
> > > 		fclose ($fd);
> > > 
> > > And 
> > > 
> > > 		$lines = file($filename);
> > > 		foreach ($lines as $line_num => $line) { $sql .= $line;
> > > }
> > > 
> > > 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 .= ");";
> > > 		
> > > 		$sql .= "CREATE TABLE ResolveTable (";
> > > 		$sql .= "  IP_Addr int(10) unsigned NOT NULL default
> > > '0',";
> > > 		$sql .= "  Name char(255) NOT NULL default '',";
> > > 		$sql .= "  Custom char(1) default NULL,";
> > > 		$sql .= "  Global char(1) default 'Y',";
> > > 		$sql .= "  OSVersion char(255) default NULL,";
> > > 		$sql .= "  RowID int(10) unsigned NOT NULL
> > > auto_increment,";
> > > 		$sql .= "  Display enum('Yes','No') NOT NULL default
> > > 'Yes',";
> > > 		$sql .= "  PRIMARY KEY  (RowID)";
> > > 		$sql .= ");";
> > > 		
> > > 	echo "<PRE>".$sql."</PRE><P>";
> > > 	$result = mysql_query($sql,$linkI);
> > > 
> > > But ALL of them fail! Ugh!!! Can I not stack commands 
> like that? Is
> > > there some way to read in a .sql file via PHP?  The problem 
> > is that my
> > > web pages are on a web server and the db is on a mysql 
> > server which are
> > > different machines, so calling a system() or other execute style
> > > function won't work for me.
> > > 
> > > I figured, "Ah! Why don't I just make a template db on the 
> > server and
> > > issue some SQL command to 'clone' that and rename it". 
> > You'd think that
> > > was pretty straight forward, but I can't find any examples 
> > or commands
> > > to do this seemingly trivial task. Ideas?
> > > 
> > > 
> > > 
> > 
> ---------------------------------------------------------------------
> > > Before posting, please check:
> > >    http://www.mysql.com/manual.php   (the manual)
> > >    http://lists.mysql.com/           (the list archive)
> > > 
> > > To request this thread, e-mail 
> <mysql-thread127657@stripped>
> > > To unsubscribe, e-mail 
> > <mysql-unsubscribe-blovett1=rcn.com@stripped>
> > > Trouble unsubscribing? Try: 
> > http://lists.mysql.com/php/unsubscribe.php
> > 
> > 
> > 
> > 
> > 
> ---------------------------------------------------------------------
> > Before posting, please check:
> >    http://www.mysql.com/manual.php   (the manual)
> >    http://lists.mysql.com/           (the list archive)
> > 
> > To request this thread, e-mail <mysql-thread127660@stripped>
> > To unsubscribe, e-mail 
> > <mysql-unsubscribe-daevid=daevid.com@stripped>
> > Trouble unsubscribing? Try: 
> http://lists.mysql.com/php/unsubscribe.php
> > 
> 
> 
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread127669@stripped>
> To unsubscribe, e-mail 
> <mysql-unsubscribe-daevid=daevid.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>