Import large .sql file with php and zend framework
I need to import a (large) .sql
file into MySQL with PHP and Zend framework.
The connection is open and I $_sql
contain all the contents of the SQL file, but of course a simple one
$this->db->query($_sql);
will not work.
What can I do to import this large file?
Update: Yes, this is a mysql dump with the structural definitions of my mysql database. And yes, it should be run from php without using the command line.
SOLUTION: I need a mysqli object to fix this mysqli-> multiQuery is the way to go. This is what my (unit test) boot tray looks like:
<?php
// Define path to application directory
defined('APPLICATION_PATH')
|| define('APPLICATION_PATH', realpath(dirname(__FILE__) . '/../application'));
// Define application environment
defined('APPLICATION_ENV')
|| define('APPLICATION_ENV', (getenv('APPLICATION_ENV') ? getenv('APPLICATION_ENV') : 'testing'));
// Ensure library/ is on include_path
set_include_path(implode(PATH_SEPARATOR, array(
realpath(APPLICATION_PATH . '/../library'),
get_include_path(),
)));
require_once 'Zend/Loader/Autoloader.php';
Zend_Loader_Autoloader::getInstance();
// Create application, bootstrap, and run
$application = new Zend_Application(
APPLICATION_ENV,
APPLICATION_PATH . '/configs/application.ini'
);
$options = $application->getOptions();
$_sqlFile = realpath(APPLICATION_PATH.'\\..\\tests\\sql\\');
$_sqlFile .= '\\myLargeSQL.sql';
$sql = file_get_contents($_sqlFile);
$mysqliobject = new mysqli(
$options['resources']['db']['params']['host'],
$options['resources']['db']['params']['username'],
$options['resources']['db']['params']['password'],
$options['resources']['db']['params']['dbname']
);
$mysqliobject->multi_query($sql);
if ($mysqliobject->errno <> 0) {
throw new Exception('Error creating temp database in ('.__FILE__.' on line '.__LINE__.')');
}
$mysqliobject->close();
Don't worry about the \, this is only temporary (will fix it soon) and yes, I'm on a Windows machine. So maybe someone can use it.
source to share
You could do it with the following code.
$db = Zend_Db_Table::getDefaultAdapter();
$db->beginTransaction();
$sql = file_get_contents(APPLICATION_PATH . '/path/to/your/data.sql');
$db->query($sql);
$db->commit();
However, if your sql is too large, it may exceed the maximum php execution time. I would recommend that you create a php script and run it from the php command line. For example, in your shared folder, create a PHP script named import.php
<?php
// Define path to application directory
defined('APPLICATION_PATH')
|| define('APPLICATION_PATH', realpath(dirname(__FILE__) . '/../application'));
// Define application environment
defined('APPLICATION_ENV')
|| define('APPLICATION_ENV', (getenv('APPLICATION_ENV') ? getenv('APPLICATION_ENV') : 'development'));
// Ensure library/ is on include_path
set_include_path(implode(PATH_SEPARATOR, array(
realpath(APPLICATION_PATH . '/../library'),
get_include_path(),
)));
/** Zend_Application */
require_once 'Zend/Application.php';
// Create application, bootstrap, and run
$application = new Zend_Application(
APPLICATION_ENV,
APPLICATION_PATH . '/configs/application.ini'
);
$options = $application->getOptions();
$db = Zend_Db::factory($options['resources']['db']['adapter'], array(
'host' => $options['resources']['db']['params']['host'],
'username' => $options['resources']['db']['params']['username'],
'password' => $options['resources']['db']['params']['password'],
'dbname' => $options['resources']['db']['params']['dbname']
));
//OR
//$application->getBootstrap()->bootstrap('db');
//$db = Zend_Db_Table::getDefaultAdapter();
$db->beginTransaction();
$sql = file_get_contents(APPLICATION_PATH . '/path/to/your/data.sql');
$db->query($sql);
$db->commit();
Make sure your application.ini has default db table adapter as zendframework documentation. Otherwise, you can manually adjust the database parameters. For example, you can put sql file in application folder. You can change it according to your needs.
Then you can run it from the command line, go to the shared folder in the command line and run the command
php import.php
Update:
Another easy way to import large sql files, it will use the tool. I am using Navicat (http://www.navicat.com/en/products/navicat_premium/premium_overview.html). It's so easy and fast to import / export
source to share
I guess this is not a task for ZF. Try using sypex dymper - http://sypex.net/en/ - perfect tool for sql import / export
source to share