CodeIgniter Forums
How to import a sql file in codeigniter? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: How to import a sql file in codeigniter? (/showthread.php?tid=63410)

How to import a sql file in codeigniter? - Aloghli - 10-27-2015

How to import a sql file in codeigniter?

RE: How to import a sql file in codeigniter? - Avenirer - 10-27-2015

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

RE: How to import a sql file in codeigniter? - mwhitney - 10-27-2015

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.

RE: How to import a sql file in codeigniter? - PaulD - 10-27-2015

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/19751354/how-to-import-sql-file-in-mysql-database-using-php

Works for me. I hope that helps,

Best wishes,

Paul.

RE: How to import a sql file in codeigniter? - skunkbad - 10-27-2015

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.

RE: How to import a sql file in codeigniter? - freddy - 02-16-2017

(10-27-2015, 11:01 AM)PaulD Wrote: 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/19751354/how-to-import-sql-file-in-mysql-database-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 ?

RE: How to import a sql file in codeigniter? - jonathanq - 02-23-2017

Try it

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


RE: How to import a sql file in codeigniter? - ignitedcms - 02-23-2017

(02-23-2017, 01:42 PM)jonathanq Wrote: Try it

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

Simply put ... no.