Category Archives: database

Notes on Azure

Some notes on Microsoft Azure

3 type of services provided

  1. App Service –  Scalable Web Apps, Mobile Apps, API Apps, and Logic Apps for any device
  2. Cloud Service – Highly available, scalable n-tier cloud apps with more control of the OS
  3. Virtual Machines – Customized Windows and Linux VMs with complete control of the OS

Important steps you should know in creating an application

  1. Create a “resource group”. All your services will be grouped under this resource group
  2. Create an “app service plan” – package of data center location, cpu and memory size
  3. Create an “web app service” – based on plan and under resource group created in step 1 and 2

More resources and references

  1. More info on services
  2. Azure calculator
  3. VM sizing options – compute, memory
  4. Azure roadmap – see what’s in preview and what have gone generally available (GA)

Summary of Azure services (click to enlarge)

Other readings/resources:

  1. Running a High Volume Website on Azure Infrastructure Services
  2. Azure documentation
  3. Step by step PHP on azure (starting from create resource, web app, transfer file via git, mysql etc)

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