Is it possible to run a long .sql file through Codeigniter?

Question

I want to run a .sql file from my server in Codeigniter 3.1.0. I have tried following

//code to create a DB & this is successful then following ocde 
$query = file_get_contents('./test.sql');
$this->db->query($query);

Here is my test.sql file. https://gist.github.com/rejoan/97dfae1b08116e386b3e6fda97eeb4f7

Now when I run this it shows error always

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE user ( id int(11) NOT NULL, test_id int(11) NOT NULL, `' at line 10

CREATE TABLE `test` 
( `id` int(11) NOT NULL, 
`name` varchar(250) NOT NULL, 
`description` text NOT NULL, 
`added` date NOT NULL, 
`outdate` date NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

CREATE TABLE `user` 
( `id` int(11) NOT NULL, 
`test_id` int(11) NOT NULL, 
`name` varchar(250) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

ALTER TABLE `test` ADD PRIMARY KEY (`id`), 
ADD UNIQUE KEY `name` (`name`);

ALTER TABLE `user` ADD PRIMARY KEY (`id`), 
ADD KEY `test_id` (`test_id`); 

ALTER TABLE `test` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37; 

ALTER TABLE `user` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; 

ALTER TABLE `user` 
ADD CONSTRAINT `FK_user_test` FOREIGN KEY (`test_id`) 
REFERENCES `test` (`id`) ON UPDATE NO ACTION;

Filename: C:/xampp/htdocs/spider_clients/system/database/DB_driver.php

Line Number: 691

So how can I resolve this by issue? I have a idea to do this by PHP raw code but Firstly I want to solve by CI. Any Idea?

** Even I have tried by replacing all backtick by single quote still not works

Look here http://stackoverflow.com/questions/8156828/codeigniter-how-to-run-multiple-batch-queries — Bogdan Burym, Aug 01 '16 at 17:46
Well, as you said you need plain-CI solution. My link does not provide it. I hope it will help you to understand the idea of the problem. — Bogdan Burym, Aug 01 '16 at 17:54
maybe interesting? http://stackoverflow.com/questions/4027769/running-mysql-sql-files-in-php — Ryan Vincent, Aug 01 '16 at 18:21
I don't think you can run *multiple* queries in one statement. You may need to break this up and call `->query()` multiple times. — gen_Eric, Aug 01 '16 at 20:18

score 3 · Answer 1 · edited May 23 '17 at 12:00

You can't run any SQL file through the API. Not even if you split up the file and run one query per API call.

There are some commands that can appear in SQL files, but they're actually mysql client builtin commands. These commands are not recognized by the SQL parser in the server.

It's tricky to split up the SQL file. There are SQL statements that contain literal semicolons, like CREATE TRIGGER. So you need more complex logic to split up the file, it's not as simple as preg_split('/;/', $query)

Another gotcha: you'll find that submitting a "query" that consists of nothing but an SQL comment causes an error.

Also, if your SQL file is too large, you'll blow out PHP's max memory if you use file_get_contents().

The bottom line is that you'll waste a lot of your time developing this code and trying to make it work. You're better off leveraging the tool that is already designed to run SQL scripts:

shell_exec("mysql databasename < ./test.sql");

Is it possible to run a long .sql file through Codeigniter?

1 Answers1