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,";
