Tag Archives: database

Codeigniter bulk insert to database

Leave a reply

If you are to insert hundreds or thousands of records, please use bulk insert. Avoid call insert query in loop. 

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

There will be limit to how many records can be added at one go.

Some limitations

  1. Limit to 1000 records per insert statement
  2. Check the settings for max_allowed_packet, bulk_insert_buffer_size, key_buffer_size

MySQL transaction

Leave a reply

Transaction feature suitable to be used in 2 conditions.

  1. If you have multiple statements to run and want to ensure all of them run successfully to conclude the task.
  2. If you want to run a delete command, and want to ensure everything is ok before commit.

How to use

run TRANSACTION or BEGIN command

then run other statement as usual

then you have option to ROLLBACK; or to COMMIT;

other reference

Database config for localhost and server

Leave a reply

To easily toggle database config for localhost and server, you can simply check whether you are accessing the local or server.

1
2
3
4
5
6
7
8
9
10
11
12
13

if (preg_match("/localhost/i", $_SERVER['SERVER_NAME'])) {
define ('DBHOST','localhost');
define ('DBNAME','localdbname'); 
define ('DBUSER','localuser');
define ('DBPASS','localpass');
define ('DEV',TRUE);
} else {
define ('DBHOST','localhost');
define ('DBNAME','serverdbname'); 
define ('DBUSER','serveruser');
define ('DBPASS','serverpass');
define ('DEV',FALSE);
}