Run whole sql files in php?

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
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Run whole sql files in php?

Post by allspiritseve »

Hello all,

I feel like I should know the answer to this..

Is it possible to run a whole sql file in php, as one query? Or do you have to go line by line through a given file and execute each line as its own query?

Thanks,

Cory
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Run whole sql files in php?

Post by jaoudestudios »

Can you give us an example of your sql file?

Is it an SQL dump or each line has a different query on it?

MySQLi has multiple query capabilities.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Run whole sql files in php?

Post by allspiritseve »

well, probably a dump from phpmyadmin. I'd like to use one to set up a test database for testing my code.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Run whole sql files in php?

Post by jayshields »

PHPMyAdmin can import as well as export. You could probably pull the code from theirs, if not, coding your own won't be too hard.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Run whole sql files in php?

Post by allspiritseve »

No I understand that, but I need to automate the creating/deleting of the database for my tests.

I just needed to know if there was a way to run a whole file at once, without splitting it up into individual queries. I guess there isn't.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Run whole sql files in php?

Post by jaoudestudios »

If it is a sql dump from a database, then there might be a way just in php, but I know there is definitely a way using php and shell command line. Drop from php into the shell and use the sql function source.
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Run whole sql files in php?

Post by matthijs »

What I do is create basic queries for each sql statement. For example:

Code: Select all

 
    public function setup(){
// first drop the tables
        $this->conn->query('drop table if exists mc_training_category');
        $this->conn->query('drop table if exists mc_training_tag');
        $this->conn->query('drop table if exists mc_trainings');
        $this->conn->query('drop table if exists mc_categories');
        $this->conn->query('drop table if exists mc_tags');
// now create a few tables, defined in a config file
        $this->conn->query(TRAININGS_TABLE);
        $this->conn->query(CATEGORIES_TABLE);
        $this->conn->query(TRAINING_CATEGORY_TABLE);
        $this->conn->query(TAGS_TABLE);
        $this->conn->query(TRAINING_TAG_TABLE);
}
 
Of course this is a bit more work. But at the same time, for some test files I don't need to build up the complete database, which saves me from creating and dropping the other tables. I have noticed things can get pretty slow with even a few tests in place. So maybe there jaoudestudios' way is a faster option?
Post Reply