CODEWITHSUNDEEP
phpsqlcodeignitermysqli
Mohamad Alasly
Mohamad Alasly

Reputation: 340

how to insert many rows without using loop from server side

I have a users table and notifications table

I want to insert notification for each user with the role (admin)

how can I do this using SQL commands? without looping and the (insert) commands from the server side

 SELECT id.users FROM users
 , (
INSERT INTO `notifications` (`id`, `note`, `link`, `type`, `object_id`, `user_id`, `status`, `status_2`, `user`, `date`) VALUES (NULL, '', '', '', '', id.user , '', '', '', '');

)

WHERE user_role.users = "admin"

ps: I'm using CodeIgniter

I tried this but the syntax is wrong

Upvotes: 0

Views: 384

Answers (2)

Atural
Atural

Reputation: 5439

you can try to create an insert statement combined with a select statement from your table (you are pretty close - but the syntax is wrong) - try the following

$this->db->query("
    INSERT INTO `notifications` (`note`, `link`, `type`, `object_id`, `user_id`, `status`, `status_2`, `user`, `date`)
    SELECT '' AS note, '' AS link, '' AS type, '' AS object_id, id, '' AS status, '' AS status_2, '' AS user, '' AS date  FROM users WHERE user_role.users = 'admin'
");

I assumed your id field in your notifications table is an auto increment - so i left that out.

Upvotes: 0

Amit Rajput
Amit Rajput

Reputation: 2059

$this->db->insert_batch()

Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the function. Here is an example using an array:

$data = array(
        array(
                'title' => 'My title',
                'name' => 'My Name',
                'date' => 'My date'
        ),
        array(
                'title' => 'Another title',
                'name' => 'Another Name',
                'date' => 'Another date'
        )
);

$this->db->insert_batch('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'),  ('Another title', 'Another name', 'Another date')

And for large array or batch you can split your data into chunks like below:

$chunks = array_chunk($data, 500);

foreach ($chunks as $key => $chunk) {
    $this->db->insert_batch('table', $chunk);
}

Upvotes: 2

Related Questions