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:joseph.bueno@stripped]
>>Sent: Tuesday, December 17, 2002 2:47 AM
>>To: mysql@stripped
>>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:daevid@stripped]
>>>>Sent: Tuesday, December 17, 2002 1:16 AM
>>>>To: mysql@stripped
>>>>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 <mysql-thread127675@stripped>
>>To unsubscribe, e-mail
>><mysql-unsubscribe-daevid=daevid.com@stripped>
>>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>
>
>
