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.

Harish · Accepted Answer · 2017-10-09 18:00:21Z

0

You can make use of CodeIgniter insert_batch() function

Follow this link here to see how it works:

answered Oct 9, 2017 at 18:00

Harish

4626 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

geeth · Accepted Answer · 2017-10-10 04:28:57Z

0

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);

answered Oct 10, 2017 at 4:28

geeth

7142 gold badges19 silver badges44 bronze badges

Comments

Akshay Hegde · Accepted Answer · 2017-10-10 04:52:56Z

0

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();
}

answered Oct 10, 2017 at 4:52

Akshay Hegde

17.1k2 gold badges25 silver badges39 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related