CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Bobe
Bobe

Reputation: 2040

CodeIgniter - Create multiple databases from master SQL file

What would be the best way to take an .sql file that includes schema and table creation statements and use it to create new databases from within CodeIgniter? What I want to be able to do is use this .sql file as a blueprint for several databases with the same schema, but which may be created at any time.

I imagine I just need to be able to take this file, extract the contents and echo it out into a database query. Is there a better way to do it?

I also need to be able to inject a custom database name into the statements before submitting the query. How would I go about this? Just have a placeholder keyword and do a preg replace with the database name?

Just to ensure all databases are maintained synchronously, I thought perhaps this blueprint schema should be added to CodeIgniter as a module. That way if I need to alter the schema, I can upload a new version of the module with the updated .sql file and perform the necessary migrations across all the databases. Sound reasonable? If so, how would I go about this?

Upvotes: 0

Views: 414

Answers (1)

Craig
Craig

Reputation: 1823

I have done this (run a .sql file) before, and I used this;

$sql = read_file('path/to/file.sql');

$final = '';
foreach(explode("\n", $sql) as $line)
{
      if ( isset($line[0]) && $line[0] != '#' )
      {
                $final .= $line . "\n";
      }
}

foreach (explode(";\n", final) as $sql)
{
      if ($sql)
      {
                $this->db->query($sql);
      }
}

Upvotes: 1

Related Questions