Install mysql-python in windows

To Enable the mysql extension related python need to run the below command

pip install mysqlclient

If you are trying to use mysqlclient on WINDOWS with this failure,

   "_mysql.c(29) : fatal error C1083: Cannot open include file: 'my_config.h': N
 such file or directory"

try to install the lower version instead:

pip install mysqlclient==1.3.4

Codeigniter Transaction queries creation

Introduction on Transaction

A transaction is a logical unit of work that contains one or more SQL statements.
Transactions are atomic units of work that can be committed or rolled back.
When a transaction makes multiple changes to the database, either all the changes succeed
when the transaction is committed, or all the changes are undone when the transaction is rolled back.

A transaction begins with the first executable SQL statement.
A transaction ends when it is committed or rolled back, either explicitly
with a COMMIT or ROLLBACK statement or implicitly when a DDL (Data Definition Language (DDL) is used to manage table and index structure and CREATE, ALTER, RENAME, DROP and TRUNCATE statements are to name a few data definition elements) statement is issued.

Achieve the same in codeigniter using below code

$this->db->trans_begin();


//insert transaction
$i_data = array(
      'user_id'     => $app['opp_id']
    , 'amount'      => $app['total']
    , 'type'        => 1
    , 'feb_bal'     => 1
    , 'note'        => 'Earnings'
);

$this->db->insert('transaction', $i_data);
$tran_id = $this->db->insert_id();

//insert 2
$c_data = array(
      'oppt_opp_id' => $app['id']
    , 'status'      => 0 //closed
);
$this->db->insert('progress', $c_data);

//insert 3
$e_data = array(
      'tran_id' => $tran_id
    , 'app_id'  => $app['id']
    , 'type'    => 1
    , 'status'  => 2
);
$this->db->insert('earn_spend', $e_data);


if( if ($this->db->trans_status() === FALSE) {){
$this->db->trans_rollback();
}else{
  $this->db->trans_commit();
}

Note: To achieve the transaction statements all tables engine should be in innodb

Mysql Interview Questions

• What are the different tables present in mysql

           ---Myisam, isam, innodb, heap, merge

• Types of index in mysql 

           --unique index, index

• How to create Indx

           --sy: CREATE  INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)

• Table have multiple index which order query will follow

• Explain keyword 

Mysql adding user for remote access or workbench

In order to connect remotely you have to have MySQL bind port: 3306 to your machines IP in my.cnf. Then you have to have created the user in both localhost and '%' wildcard and grant permissions on all DB's as such . See below:


path : /etc/mysql/my.cnf

my.cnf

#Replace xxx with your IP Address 
bind-address        = xxx.xxx.xxx.xxx

Creating the user

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

And granting permissions to the user

GRANT ALL ON *.* TO 'myuser'@'localhost';

GRANT ALL ON *.* TO 'myuser'@'%';
FLUSH PRIVILEGES;



To get the user list and there permissions in mysql db
mysql> SELECT user, host FROM mysql.user;
+------------+---------------------+
| user       | host                |
+------------+---------------------+
| adadmin    | %                   |
| tbpmagento | %                   |
| wpuser_agf | %                   |
| root       | 127.0.0.1           |
| root       | ::1                 |
| ces        | localhost           |
| root       | localhost           |
| tbp        | localhost           |
| tbpmagento | localhost           |
+------------+---------------------+