Difference between in Mysql & Mango DB

How to connect to MySQL from the command line?

Connect to MySQL from the command line

To connect to MySQL from the command line:

    Log in to your A2 Hosting account using SSH.
    At the command line, type the following command, replacing USERNAME with your username:

    mysql -u USERNAME -p

    At the Enter Password prompt, type your password. When you type the correct password, 
the mysql> prompt appears.

    To display a list of databases, type the following command at the mysql> prompt:

    show databases;

    To access a specific database, type the following command at the mysql> prompt, 
replacing DBNAME with the database that you want to access:

    use DBNAME;

    After you access a database, you can run SQL queries, list tables, and so on. Additionally:
        To view a list of MySQL commands, type help at the mysql> prompt.
        To exit the mysql program, type \q at the mysql> prompt.

Mysql get two column value in get single field

Table : user
--------------
| Id | Name  |
--------------
|  1 | Test  |
|  2 | Test1 |
|  3 | TEst2 |
|  4 | Test3 |
|  5 | Test4 |
|  6 | Test5 |
|  7 | Test6 |
--------------
Query :

selecr concat(id,":",name) as output from user

Output:
------------
| output   |
------------
|  1:Test  |
|  2:Test1 |
|  3:TEst2 |
|  4:Test3 |
|  5:Test4 |
|  6:Test5 |
|  7:Test6 |
------------

Mysql get multi row value in get single field

Table : user
--------------
| Id | Name  |
--------------
|  1 | Test  |
|  2 | Test1 |
|  3 | TEst2 |
|  4 | Test3 |
|  5 | Test4 |
|  6 | Test5 |
|  7 | Test6 |
--------------

Query :
select group_concat(concat(id) SEPARATOR ',') from user

Output:
1,2,3,4,5,6,7

How to export My Sql Database Backup in php?

$backuptime = "DB_Backup_".DB_NAME."-".time().".sql";
//$backuptime = DB_NAME.".sql";
$data["db_file_name"] = $backuptime;
$insert = $CommonObj->insert_query("db_backup",$data);
$str = "";
$show_tables = "show tables from ".DB_NAME;
$tables = $db->query($show_tables);
if($tables){
   while($row = mysql_fetch_assoc($tables)){
      $table = $row["Tables_in_".DB_NAME];
      $show_each_tables = "show create table ".$table;
      $each_tables  =  $db->query($show_each_tables);
      $each_tables = mysql_fetch_assoc($each_tables);
      $str .= $each_tables["Create Table"];
      $str .= ";\n\n";
      $show_column = "SHOW COLUMNS FROM $table";
      $column  =  $db->query($show_column);
      $get_val_query = "select * from $table";
      $get_val  =  $db->query($get_val_query);
      if($column && mysql_num_rows($get_val)>0){
         $str .= "INSERT INTO `$table` (";
         while($cols = mysql_fetch_assoc($column)){
            $str .= '`'.$cols["Field"].'`, ';
         }
         $str = substr($str,0,-2);
         $str .=  ") VALUES \n";
      }
      if($get_val && mysql_num_rows($get_val)>0){
         while($vals = mysql_fetch_assoc($get_val)){
            $str .= "( ";
            foreach($vals as $k=>$v){
               $v= mysql_real_escape_string($v);
               $str  .= '"'.$v.'", ';
            }
            $str = substr($str,0,-2);
            $str .= "),\n";
         }
         $str = substr($str,0,-2);
      }
      $str .= ";\n\n";
   }
}
$handle = fopen(DIR_DB.$backuptime,'w+');
fwrite($handle,$str);
fclose($handle);

MySQL IF function

MySQL IF function
Syntax: IF(expr,if_true_expr,if_false_expr)
Example:
SELECT IF(1 = 2,'true','false'); -- false
SELECT IF(1 = 1,' true','false'); -- true

MySQL Query GROUP BY day / month / year

Method I :
SELECT count('id') as cnt,order_status,order_date FROM `product_order` 
group by MONTH(order_date) 
Method II :
SELECT count('id') as cnt,order_status,order_date FROM `product_order` 
group by DATE_FORMAT(order_date, '%m')
Method III:
SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*) FROM stats 
GROUP BY YEAR(record_date)*100 + MONTH(record_date)

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY DATE_FORMAT(date_column, '%Y%m')

PHP - Mysql Get date between two date ranges

Fetching records between two date ranges

Between two years
SELECT * FROM `dt_tb` WHERE year( dt2 ) between 2004 and 2005
Between two month ranges. 
SELECT * FROM `dt_tb` WHERE month(dt) between '02' and '08'

SELECT * FROM `dt_tb` WHERE month(dt) between '02' and '08' and year(dt) between 2004 and 2005

Between two date ranges
SELECT * FROM `dt_tb` WHERE dt BETWEEN '2005-01-01' AND '2005-12-31' 

How to get Enum Values in Mysql?

function get_enum($table,$clname){
  $Query="SHOW COLUMNS FROM `$table` LIKE '$clname'";
  $result = $this->db->query($Query) or die("Get Enum value Query");
  if($result){
    $row = mysql_fetch_array( $result , MYSQL_NUM );
    $regex = "/'(.*?)'/";
    preg_match_all( $regex , $row[1], $enum_array );
    $enum_fields = $enum_array[1];
    return $enum_fields;
  }
  return false;
}

To get distance based on latitude & longitude calculate in Mysql query

SELECT DISTINCT ( a.categoryID ), a.categoryName, a.imgFile, MIN( 3959 * ACOS( COS( RADIANS( 11.0168445 ) ) * COS( RADIANS( c.lattitude ) ) * COS( RADIANS( c.longitude ) - RADIANS( 76.9558321 ) ) + SIN( RADIANS( 11.0168445 ) ) * SIN( RADIANS( 11.0168445 ) ) ) ) AS distance