How to import a sql file in codeigniter?

How to import a sql file in codeigniter?

Well... If you managed to ask this question...

Website: http://avenir.ro

The only difference between doing this in native PHP and doing this in CodeIgniter is that you can use CI's database library to send the individual queries to the database. So, you would probably use file_get_contents() to read the .sql file into a string. In a fairly simple case, you would just explode the string on a semicolon (';') to split up the commands in the .sql file so you can execute them. Then you loop over the array of queries generated by exploding the string, trim each query, and pass it to db->query().

If you want to get complicated, you can do the whole thing in a transaction and roll it back if any of the queries fail. You could also attempt to parse the string for comments and anything else you might want to remove.

It would also be highly recommended that you check the SQL for any commands you don't want executed against your database, and that you ensure the ability to run this code is very well protected from execution by unauthorized users.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

Try something like this:

Code:

// Set line to collect lines that wrap
$templine = '';

// Read in entire file
$lines = file('/path/to/file/my_file.sql');

// Loop through each line
foreach ($lines as $line)
{
// Skip it if it's a comment
if (substr($line, 0, 2) == '--' || $line == '')
continue;

// Add this line to the current templine we are creating
$templine .= $line;

// If it has a semicolon at the end, it's the end of the query so can process this templine
if (substr(trim($line), -1, 1) == ';')
{
// Perform the query
$this->db->query($templine);

// Reset temp variable to empty
$templine = '';
}
}

I got this approach from here: http://stackoverflow.com/questions/19751...-using-php

Works for me. I hope that helps,

Best wishes,

Paul.

If your on Linux your best option is to us exec:

#backup
mysqldump -u username -ppassword database | gzip > ./dumpfilename.sql.gz

#restore
gunzip < ./dumpfilename.sql.gz | mysql -u username -ppassword database

This is going to be the fastest and best way. You could even have it tied into an uploader.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

Try something like this:

Code:

// Set line to collect lines that wrap
$templine = '';

// Read in entire file
$lines = file('/path/to/file/my_file.sql');

// Loop through each line
foreach ($lines as $line)
{
// Skip it if it's a comment
if (substr($line, 0, 2) == '--' || $line == '')
continue;

// Add this line to the current templine we are creating
$templine .= $line;

// If it has a semicolon at the end, it's the end of the query so can process this templine
if (substr(trim($line), -1, 1) == ';')
{
// Perform the query
$this->db->query($templine);

// Reset temp variable to empty
$templine = '';
}
}

I got this approach from here: http://stackoverflow.com/questions/19751...-using-php

Works for me. I hope that helps,

Best wishes,

Paul.

my question is database update or replace with new one ? tell more about the flow, should user upload file .sql first then continue with your script above ?

Try it

Code:

exec( 'echo "source '.$fileSQL.'"  | mysql -u '.$user.' -p.'$password  );

Try it

Code:

exec( 'echo "source '.$fileSQL.'"  | mysql -u '.$user.' -p.'$password  );

Simply put ... no.

Practical guide to IgnitedCMS - Book coming soon, www.ignitedcms.com