I am not a PHP expert but I think you can run external commands from PHP, so it should be possible to run mysqladmin, mysqldump and mysql from PHP. This will work even if you database is on a remote server; just specify the hostname (ex: "mysqladmin -h mysqlhost create db2")
Daevid Vincent wrote: > 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
