Working with multiple databases

Copying data from one database to another

Crystal makes copying data from one database to another very easy.

// Old blog posts table(Wordpress)
| id | post_author | post_date  | post_content | post_title | post_category | post_status | 
| 12 | John	    	| 10.01.2009 | content	   | title		| programming   | draft       |  
| 15 | Adam	    	| 12.01.2009 | content	   | title		| php           | draft       |  

// Our new blog posts table
| id | date  | content | title | category | status | slug |
| 12 | 10.01.2009 | content	   | title		| programming   | draft       | very-cool-url | 
| 15 | 12.01.2009 | content	   | title		| php           | draft       | another-cool-url | 

// I use configuration arrays, you can write down the database details in Crystal's config file if you want.
$config_wordpress = array(
				'username' => 'username',
				'password'=> 'password',
				'database' => 'wp_blog',
				'driver' => 'mysql'
				);

				
$config_new_blog = array(
				'username' => 'username',
				'password'=> 'password',
				'database' => 'new_blog',
				'driver' => 'mysql'
				);	

$wordpress_db = Crystal::db($config_wordpress);
$new_db = Crystal::db($config_new_blog);


$all_posts = $wordpress_db->get('posts')->fetch_all();

foreach($all_posts as $key => $value)
{
	$data = array('date' => $value['post_date'],
				  'content' => $value['post_content'],
				  'title' => $value['post_title'],
				  'category' => $value['post_category'],
				  'status' => $value['post_status']
				  );
				  
	$new_db->insert('posts', $data)->execute();
}

Master/slave configuration

In our configuration file we have the database connection parameters for two different databases

$db['master']['username'] = "username";
$db['master']['password'] = "password";
$db['master']['database'] = "first_database";
$db['master']['driver']   = "mysql";
$db['master']['char_set'] = "utf8";
$db['master']['dbcollat'] = "utf8_general_ci";

$db['slave']['username'] = "username";
$db['slave']['password'] = "password";
$db['slave']['database'] = "second_database";
$db['slave']['driver']   = "mysql";
$db['slave']['char_set'] = "utf8";
$db['slave']['dbcollat'] = "utf8_general_ci";


// We can create two different instances for every database like this: 
$master  = Crystal::db('master');
$slave  = Crystal::db('slave');

In this example the master database is used only for writes and the slave(or multiple slave databases for reads) In our model we have:

function create_entry()
{
	$data = array('content' => 'My content is here', 'title' => 'Blog post title');

	$master->insert('entries', $data)->execute();

}


function get_entries
(
 	return $slave->get('entries')->fetch_all();
)