CODEWITHSUNDEEP
phpzend-frameworkzend-db
Paladin
Paladin

Reputation: 1637

Import large .sql file with php and zend framework

I have to import a (big) .sql file to MySQL with PHP and the Zend framework.

The connection is open, and I have $_sql containing the whole content of the SQL file, but - of course, a simple

$this->db->query($_sql);

will not work.

What can I do to import this large file?

Update: Yes, it is a mysql-dump with structure-definitions of my mysql-database. And yes, it need to run from within php without using command line.

SOLVED: I need a mysqli object to fix that, mysqli->multiQuery is the way to go. Here's what my (unit test) bootstrap look 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 temporarly (will fix that soon) and yes, i am on a windows machine. So, maybe somebody can use it.

Upvotes: 3

Views: 2958

Answers (4)

riyuk
riyuk

Reputation: 174

http://www.mysqldumper.net/

It's a open Source Web-Application. If you don't want to use it you can browse the Code and see how they do it. It's open source.

MySQL-Dumper can handle .sql files with 200+MB...

Upvotes: 0

b.b3rn4rd
b.b3rn4rd

Reputation: 8830

probably fastest way to import large file is using cli:

mysql -u username -p databasename < yourfile.sql

Upvotes: 1

dextervip
dextervip

Reputation: 5059

You would able to 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 much big, It may exceed php maximum execution time. I would recommend you, create a php script and run it from the php command line. For example in your public folder, create php script called 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 a default db table adapter as zendframework documentation. Otherwise you can configure manually the database settings. In the example you could put the sql file in the application folder.You can change to suit your needs.

Then you can run it from your command line, go to the public folder in the command line and run the command

 php import.php

Update:

Another simple way to import large sql files, it would be using a tool. I am used to use Navicat (http://www.navicat.com/en/products/navicat_premium/premium_overview.html). It's so easy and fast to import/export

Upvotes: 1

turist_ua
turist_ua

Reputation: 114

I think, it's not task for ZF. Try to use sypex dymper - http://sypex.net/en/ - perfect tool for sql import/export

Upvotes: 0

Related Questions