DAMNIT! This method copies the data, but doesn't copy 'Extra' stuff like "auto_increment". UGH!!! > -----Original Message----- > From: Daevid Vincent [mailto:daevid@daevid.com] > Sent: Tuesday, December 17, 2002 2:33 AM > To: php-db@lists.php.net > Subject: RE: How can I use PHP to duplicate a mysql > template database? [hack] > > > Seems to me there should be a built in SQL or PHP 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, so most > likely leave it > empty > > $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@daevid.com] > > Sent: Monday, December 16, 2002 8:00 PM > > To: php-db@lists.php.net > > Subject: How can I use PHP to duplicate a mysql > > template database? > > > > > > 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. > > > > 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? > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
