Re: How can I duplicate a mysql template database? [hack]
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\n".mysql_errno($linkI).": >> >>".mysql_error($linkI)."\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 "$table\n"; >>>// $result = mysql_query($sql,$linkI); >>>// if (!$result) $errorstring .= "Error creating ".$V2DB." >>>".$table." table\n".mysql_errno($linkI).": >>>".mysql_error($linkI)."\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."\n"; >>> $tresult = mysql_query($tsql,$linkI); >>> if (!$tresult) $errorstring .= "Error creating >>>".$V2DB.".".$row[0]." table\n".mysql_errno($linkI).": >>>".mysql_error($linkI)."\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. >>>> >>>&
RE: How can I duplicate a mysql template database? [hack]
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\n".mysql_errno($linkI).": > ".mysql_error($linkI)."\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 "$table\n"; > > // $result = mysql_query($sql,$linkI); > > // if (!$result) $errorstring .= "Error creating ".$V2DB." > > ".$table." table\n".mysql_errno($linkI).": > > ".mysql_error($linkI)."\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."\n"; > > $tresult = mysql_query($tsql,$linkI); > > if (!$tresult) $errorstring .= "Error creating > > ".$V2DB.".".$row[0]." table\n".mysql_errno($linkI).": > > ".mysql_error($linkI)."\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 = << >>>CREATE TABLE IP_Dept ( > >>&g
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\n".mysql_errno($linkI).": ".mysql_error($linkI)."\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 "$table\n"; > //$result = mysql_query($sql,$linkI); > //if (!$result) $errorstring .= "Error creating ".$V2DB." > ".$table." table\n".mysql_errno($linkI).": > ".mysql_error($linkI)."\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."\n"; > $tresult = mysql_query($tsql,$linkI); > if (!$tresult) $errorstring .= "Error creating > ".$V2DB.".".$row[0]." table\n".mysql_errno($linkI).": > ".mysql_error($linkI)."\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 = <<>>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 = <<>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 .= " Dep
RE: How can I duplicate a mysql template database? [hack]
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\n".mysql_errno($linkI).": ".mysql_error($linkI)."\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 "$table\n"; // $result = mysql_query($sql,$linkI); // if (!$result) $errorstring .= "Error creating ".$V2DB." ".$table." table\n".mysql_errno($linkI).": ".mysql_error($linkI)."\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."\n"; $tresult = mysql_query($tsql,$linkI); if (!$tresult) $errorstring .= "Error creating ".$V2DB.".".$row[0]." table\n".mysql_errno($linkI).": ".mysql_error($linkI)."\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 = << > 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 = << 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,";