programmatically run a sql file

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

programmatically run a sql file

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

check out "load infile" or something..
dave420
Forum Contributor
Posts: 106
Joined: Tue Feb 17, 2004 8:03 am

Post 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.
Post Reply