Page 1 of 1

programmatically run a sql file

Posted: Wed Jun 02, 2004 9:03 am
by lostboy
Hi Guys,

I am working on creating an install package for an application (uses Apache, MySQL, PHP). I have the table structures in a install.sql file that I need to run to create the base tables for the application.

The installer is a VB exe.

Can any one tell me how to include the install.sql file to create the base tables? As well, I need to run a sql to create new user accounts and drop the basic default accounts that come with the MySQL install.

Should I be using PHP to do this? I could work this out, but I know I can run the SQL files. I just need a pointer or two on how to do this.

TIA

Posted: Sun Jun 06, 2004 9:36 am
by kettle_drum
Well if your using php then just connect to the database, open the file, read the queries, and query the database. I seperate each query with something that i can identify thats not in any mysql query so i can split the file into seperate queries:

Code: Select all

##--------------------------------------------
##TEMPLATES
##Holds the available templates for use on the site
##--------------------------------------------
CREATE TABLE templates (
   id int(4) NOT NULL auto_increment unique,
   icicle_id varchar(10) NOT NULL,
   name varchar(100) NOT NULL,
   path varchar(255) NOT NULL,
   active tinyint(1) NOT NULL,
   PRIMARY KEY (id)
);

*-----*

##--------------------------------------------
##TEMPLATE TAGS
##Holds the available tags that can be used in the template
##--------------------------------------------
CREATE TABLE template_tags (
   tag varchar(50) NOT NULL unique,
   icicle_id varchar(10) NOT NULL,
   content varchar(100) NOT NULL,
   static tinyint(1) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
   PRIMARY KEY (tag)
);
Then the code:

Code: Select all

$fp = fopen('install.sql', 'r');
         while(!feof($fp)){
            $sql .= fread($fp, 1024);
         }
         fclose($fp);
         $sql = explode("*-----*", $sql);
         foreach($sql as $q){
            mysql_query($q);
         }
Easy as that.

Posted: Sun Jun 06, 2004 9:45 am
by lostboy
I know I can do this, what I was after was using exec or system to open the mysqld and somehow run a large sql file against the db directly...its for an install application that I am doing?

Any ideas there?

Posted: Sun Jun 06, 2004 9:54 am
by feyd
check out "load infile" or something..

Posted: Mon Jun 07, 2004 6:18 am
by dave420
http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html, or providing you know the host/username/password of the database, you could just dump the file to the mysql client, which would do what you want.