Unfortunately, command line is not an option for me. As I said before, the php scripts are on a web server and the database is on another server.
> -----Original Message----- > From: Joseph Bueno [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, December 17, 2002 2:47 AM > To: [EMAIL PROTECTED] > Subject: Re: How can I duplicate a mysql template database? [hack] > > > Hi, > > There is an easier way to duplicate a database; from the comand > line, you can run: > > mysqladmin create db2 > mysqldump db1 | mysql db2 > > If you want to duplicate the schema only (without the data): > mysqladmin create db2 > mysqldump --no-data db1 | mysql db2 > > (You may have to add host/user/password options but I just wanted to > show the idea). > > Hope this helps, > Joseph Bueno > > Daevid Vincent wrote: > > 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:[EMAIL PROTECTED]] > >>Sent: Tuesday, December 17, 2002 1:16 AM > >>To: [EMAIL PROTECTED] > >>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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
