RE: [PHP-DB] 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.
databaseBR\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/PREphr\n;
// $result = mysql_query($sql,$linkI);
// if (!$result) $errorstring .= Error creating .$V2DB.
.$table. tableBR\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]. tableBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
}
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 8:00 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] 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./PREP;
$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
Re: [PHP-DB] How can I use PHP to duplicate a mysql template database?
At 07:59 PM 12/16/02 -0800, Daevid Vincent wrote: 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. The easiest way I know of is at the command line: mysqldump -p -u root name_of_existing_database schemafile mysql -p -u root mysql create database newdatabase; mysql grant whatever on newdatabase.* to whoever mysql exit mysql -p -u root newdatabase schemafile The commands typed into the mysql interpreter must be done by a user with rights to create databases, the final mysql command where you import the schemafile must be done with a user having rights to create tables within the new database, the mysqldump can be done by any user with select rights to all tables in the existing database. 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 .= );; With MySQL, you only get one sql statement per call to mysql_query, so call it here, empty the $sql variable and start on the next table. Also, a trailing semicolon is not allowed in the $sql variable, so end with $sql .= ); instead of $sql .= );; Don't complain, this is a feature that prevents a common database attack where someone adds ;drop table users; at the end of one of your queries by poisoning user input. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] How can I use PHP to duplicate a mysql template database? [hack] bug
DAMNIT!
This method copies the data, but doesn't copy 'Extra' stuff like
auto_increment.
UGH!!!
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 2:33 AM
To: [EMAIL PROTECTED]
Subject: RE: [PHP-DB] 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.
databaseBR\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/PREphr\n;
//$result = mysql_query($sql,$linkI);
//if (!$result) $errorstring .= Error creating .$V2DB.
.$table. tableBR\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]. tableBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
}
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 8:00 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] 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./PREP;
$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
[PHP-DB] 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./PREP;
$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
