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
>
