Category Archives: database

MySQL transaction

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

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)

Some uncommon mysql command

https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

COALESCE()

IN

NOT_IN

NOT_LIKE

FIND_IN_SET

Example: select * from users where (find_in_set (5, access_string) > 0);

will return position of the key if found in string. else will return 0 for not found or NULL if field is NULL

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