List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:December 17 2002 9:15am
Subject:RE: How can I duplicate a mysql template database?
View as plain text  
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-thread127657@stripped>
> > To unsubscribe, e-mail 
> <mysql-unsubscribe-blovett1=rcn.com@stripped>
> > Trouble unsubscribing? Try: 
> http://lists.mysql.com/php/unsubscribe.php
> 
> 
> 
> 
> ---------------------------------------------------------------------
> 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-thread127660@stripped>
> To unsubscribe, e-mail 
> <mysql-unsubscribe-daevid=daevid.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

Thread
How can I duplicate a mysql template database?Daevid Vincent17 Dec
  • Re: How can I duplicate a mysql template database?Bill Lovett17 Dec
    • RE: How can I duplicate a mysql template database?Daevid Vincent17 Dec
      • RE: How can I duplicate a mysql template database? [hack]Daevid Vincent17 Dec
        • Re: How can I duplicate a mysql template database? [hack]Joseph Bueno17 Dec
          • RE: How can I duplicate a mysql template database? [hack]Daevid Vincent17 Dec
            • Re: How can I duplicate a mysql template database? [hack]Joseph Bueno17 Dec
      • Re: How can I duplicate a mysql template database?David T-G17 Dec
        • RE: How can I duplicate a mysql template database?Daevid Vincent17 Dec
          • Re: How can I duplicate a mysql template database?Joseph Bueno17 Dec
          • Re: How can I duplicate a mysql template database?David T-G17 Dec