Codeigniter database backup - Insert multiple rows without repeating “INSERT INTO”

Question

I'm trying out to find a way to backup my database using Codeigniter "Database Utility" but without repeating “INSERT INTO”, I mean everything works perfectly, but when we see the generated sql file we see something like this:

INSERT INTO Membership (ID, FirstName, LastName, DOB)
VALUES (1, 'John', 'Smith', '5/5/1960')

INSERT INTO Membership (ID, FirstName, LastName, DOB)
VALUES (2, 'Hello", 'World", '4/9/1975')

INSERT INTO Account(ID, Type, Effective_Date, Status)
VALUES (1, 'Gold', GetDate(), 'Active')

INSERT INTO Account(ID, Type, Effective_Date, Status)
VALUES (2, 'Platinum', GetDate(), 'Inactive')

INSERT INTO Participant(ID, Code, Type)
VALUES (1, 002, 'A')

INSERT INTO Participant(ID, Code, Type)
VALUES (2, 002, 'A')

The which is so slow whenever we import in another database. I want to get something like this:

INSERT INTO #temp_table (1, 'John', 'Smith, '5/5/1960', 'Gold', 'Active', '002', 'A')
.
.
.
.
so on

using only one INSERT INTO, is there any way to achieve this? Thanks.

score 0 · Answer 1 · answered Oct 09 '17 at 18:00

0

You can make use of CodeIgniter insert_batch() function

Follow this link here to see how it works:

answered Oct 09 '17 at 18:00

Harish

462
6
13

score 0 · Answer 2 · answered Oct 10 '17 at 04:28

Create a multi-dimensional array with field and values like this, and use codeigniter inset_batch() function to insert all these in a single query.

$data = array(
    array(
            'ID' => 1,
            'FirstName' => 'John',
            'LastName' => 'Doe',
            'DOB'      => '5/5/1960'
    ),
    array(
            'ID' => 2,
            'FirstName' => 'John',
            'LastName' => 'Smith',
            'DOB'      => '5/5/1960'
    )
);

$this->db->insert_batch('temp_table', $data);

score 0 · Answer 3 · answered Oct 10 '17 at 04:52

I understand your problem, Database Utility is quite slow also, due to string concatenation, if passthru() is enabled on your server, then mysqldump can be used, here is working code, which will export current database.

public function backup_current_db()
{
    $db_user=$this->db->username;
    $password=$this->db->password;
    $db=$this->db->database;

    $filename =  $db . "-" . date("Y-m-d_H-i-s") . ".sql";
    $mime = "application/octet-stream";

    header( "Content-Type: " . $mime );
    header( 'Content-Disposition: attachment; filename="' . $filename . '"' );

    $cmd = "mysqldump -u '$db_user' --password='$password' --no-create-info --complete-insert '$db' ";
    passthru( $cmd );

    die();
}

Codeigniter database backup - Insert multiple rows without repeating “INSERT INTO”

3 Answers3