Category Archives: database

Sample use of CI query builder for CRUD

Sample insert

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

$this->db->insert('mytable', $data);

Sample update

$this->db->update('mytable', $data, "id = 4"); OR
$this->db->update('mytable', $data, array('id' => $id));

Sample delete

$this->db->delete('mytable', array('id' => $id));

Sample to get data

$where = "name='Joe' AND status='boss' OR status='active'";
$this->db->where($where);

More on getting data with CI

CI Query builder reference: CI2, CI3

Getting data from CI db query

1. All result set

$query = $this->db->query("YOUR QUERY");

if ($query->num_rows() > 0)
{
   foreach ($query->result() as $row)
   {
      echo $row->title;
      echo $row->name;
      echo $row->body;
   }
}

2. Just one row

$query = $this->db->query("YOUR QUERY");

if ($query->num_rows() > 0)
{
   $row = $query->row(); 

   echo $row->title;
   echo $row->name;
   echo $row->body;
}

Reference: CI2, CI3

Ways to auto backup MySQL database

1. AutoMySQLBackup

AutoMySQLBackup with a basic configuration will create Daily, Weekly and Monthly backups of one or more of your MySQL databases from one or more of your MySQL servers.

Other Features include:
– Email notification of backups
– Backup Compression and Encryption
– Configurable backup rotation
– Incremental database backups

2. Exabytes off-site backup RM199/mo for 50GB.

3. Handy Backup

4. Percona XtraBackup (incremental backup)

5. Explaining on AutoMySQLBackup and MySQL Workbench

Append 0’s in MySQL

In MySQL there is function to append character to string.

LPAD for left padding
LPAD(str,len,padstr)

RPAD for right padding

Example of usage

1
2
3
UPDATE sometable
SET FIELD = LPAD (FIELD,'5','00')
WHERE fieldid = 1