Mysql Questions

1. Types of Joins in Mysql
INNER JOIN : The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables
LEFT JOIN: The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
RIGHT JOIN:  The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
FULL OUTER JOIN: The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).  The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
2. How many maximum columns can be in a single MySQL table?
There is hard limit of 4096 columns in a MySQL table, but this also depends on some other factors as well like, size of the every column in the table, a row in table can be maximum 65,535 bites.

 

3. How many Maximum options can be there in SET and ENUM?
There can be maximum 64 options in ENUM, as well as in SET.

4. What is the difference ENUM and SET?
The difference between is only like Radio button form and checkbox in HTML form, ENUM is like Radio button user can only select one, while SET is like the Checkbox, user are able to select more than one value from the SET.

5. What is the difference between UNION and UNION ALL?
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
UNION ALL will indeed be more performant, specifically due to the lack of the distinct sort.
UNION ALL can be slower than UNION in specific cases like, where the network such as the internet, is a bottleneck

6. What does myisamchk do?
It compresses the MyISAM tables, which reduces their disk or memory usage.

7 . How many TRIGGERS are allowed in MySql table?
BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

8 . What is the difference between BLOB AND TEXT?
A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB differ only in the maximum length of the values they can hold.
The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT correspond to the four BLOB types and have the same maximum lengths and storage requirements. The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive fashion for BLOB values and case-insensitive fashion for TEXT values. In other words, a TEXT is a case-insensitive BLOB.

9.  What are HEAP tables in MySQL?
HEAP tables are in-memory. They are usually used for high-speed temporary storage.
No TEXT or BLOB fields are allowed within HEAP tables.
You can only use the comparison operators = and ‹=›.
HEAP tables do not support AUTO_INCREMENT.
Indexes must be NOT NULL.

10. How do you find out which auto increment was assigned on the last insert?
SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function.

11. What is difference between Primary key and Candidate key?
Primary Key:
are used to uniquely identify each row of the table. A table can have only one primary Key.
Candidate Key
Candidate Key:
primary key is also a candidate key. There is no difference. By common convention one candidate key is designated as a “primary key” and that key is used for any foreign key references.

12. How would you change a table to InnoDB?
ALTER TABLE name_file ENGINE innodb;

13. How do you get current user in mysql?
SELECT USER();

14. Explain the difference between FLOAT, DOUBLE and REAL?
FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes.
DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes.
REAL is a synonym of FLOAT for now.

15. How do you change a password for an existing user via mysqladmin?
mysqladmin -u root -p password “newpassword”

16. How to export MySQL database to SQL file?
mysqldump -h [mysql hostname] -u [mysql username] -p [database name which to export] > [file name to which to export the dump with (.)dot sql extension]

6 Steps to create cron jobs in cakephp

Step 1 – Creating your own shell file

Step 2 – Writing the functionality in that shell file

Step 3 – Creating a cakeshell file

Step 4 – Creating a component file(optional)

Step 5 – Run command line and test it

Step 6 – Set cron tab

STEP 1 – Creating your shell file:

Creat the Shell file

For cake version 1.3 >> Create a file named shellname.php

in the Project name/app/vendors/shells/

Example : app/vendors/shells/order.php

For  cake version 2.0 >> Create a file named shellnameShell.php

in the Project name/app/vendors/shells/

Example : app/Console/Command/orderShell.php

STEP 2 – Writing Functionality in that shell file:

<?php

class OrderShell extends Shell {

        function main() // main needs to define
        {
                App::import(‘Component’, ‘BusinessLogic’);
                $this->BusinessLogic = &new BusinessLogic();

                $option = !empty($this->args[0]) ? $this->args[0] : ”;
                echo ‘Cron started without any issue.’;

                switch ($option)
                {
                        case ‘first’:
                        $result= $this->BusinessLogic->first_method();
                        break;
                        case ‘second’:
                        $result= $this->BusinessLogic->deleteauto();
                        break;
                        default:
                        echo ‘No Parameters passed .’;
                }
        }
}

STEP 3 – Create a cakeshell file:

For cake version 1.3: create another file named cakeshell with out any extenstion in the same folder app/vendors/shells/

Refer Link: http://book.cakephp.org/1.3/en/The-Manual/Developing-with-CakePHP/The-CakePHP-Console.html#running-shells-as-cronjobs

For cake version 2.0: create another file named cakeshell with out any extenstion in the same folder app/Console/
Refer Link: http://book.cakephp.org/2.0/en/console-and-shells/cron-jobs.html#running-shells-as-cronjobs/

STEP 4 – Creating Components:

create a component file inside app/controllers/components/

example: business.php

<?php
class BusinessComponent extends Object
{
function first_method()
{
echo “First Method called at” .date(‘M d, Y h:i:s T’).” \n”;
}

function second_method()
{
echo “Second Method called at” .date(‘M d, Y h:i:s T’).” \n”;
}
}

?>

STEP 5 – Run Command Line:

For cake version 1.3 : Go to project_name/app/ folder
Run the following command and check that shell is working fine

Syntax: cake shellname optional parameters

Example: var/www/projectname/app/ cake order

For cake version 2.0: Go to project_name/app/ folder
Run the following command and check that shell is working fine

Syntax: cake shellname optional parameters

Example: var/www/projectname/app/Console/cake order

STEP 5 – Set Crontab:

Final step setting the timing of the cron
go to your command prompt and type crontab -

Syntax :

* * * * * /path of the shell folder(inside app folder)/cakeshell shellname parameter
-cli /usr/bin -console
/path of the console folder(outside app folder)/console -app /path of app folder/ >>
/home/annamalai/Desktop/file.log

Example cake 1.3:

*/2 * * * * /var/www/v3.0/php/app/vendors/shells/cakeshell order first -cli /usr/bin -console
/var/www/v3.0/php/cake/console -app /var/www/v3.0/php/app >> /home/annamalai/Desktop/file.log

Example cake 2.0:

 * * * * * /var/www/apps/app/Console/cakeshell my first -cli /usr/bin -console

/var/www/apps/Cake/Console/ -app /var/www/apps/app >> /home/annamalai/Desktop/file.log

PHP Quiz

1.What is the content of $c after the following code has executed?
$a = 2;
$b = 3;
$c = ($a++ * ++$b);

2. What is the Output of the following code or how many times it will execute?

function counter($start, &$stop)
{
if ($stop > $start)
{
return;
}
counter($start–, ++$stop);
}
$start = 5;
$stop = 2;
counter($start, $stop);

3. what will be the output ?
$num = 10;
function multiply(){
$num = $num * 10;
}
multiply();
echo $num;

4. <? echo (2) . (3 * (print 3)); ?>

5.<?
$a = 0x01;
$b = 0x02;
echo $a === $b >> $a;
?>

6.<?
$a = 3;
print ‘$a';
?>

7.Is it possible to use feof () with a file opened by  fsockopen ()?

8.<? echo false; ?>

9.echo ‘1’.print(2) + 3;

10. <? print null == NULL; ?>

11.Which feature is not supported in php5?

12.Data Hiding:
Public: Any one can access it
Protected: only descendants can access it
Private: Only you can access it
Final: No one can redeclare it
Abstract: Some one else will implement this

13. PHP only supports ‘is a ‘and ‘has a’ relations

14.There are only two naming conventions for constructors depending on which version of PHP you are using.<br/>
<b>Before PHP5 and PHP5:</b>
The naming convention was that the constructor had to have the same name as the class.<br/>
<b>After PHP5:</b>
If you are running PHP5 or above, you can use the old style or you can use “__construct” as the name for the constructor.

15.In PHP 5, what is the difference between using self and $this?
Use $this to refer to the current object. Use self to refer to the current class. In other words, use $this->member for non-static members, use self::$member for static members.

PHP tips that you need to know

  • PHP only supports ‘is a ‘and ‘has a’ relations
  • There are only two naming conventions for constructors depending on which version of PHP you are using.
    Before PHP5 and PHP5: The naming convention was that the constructor had to have the same name as the class.
    After PHP5:If you are running PHP5 or above, you can use the old style or you can use “__construct” as the name for the constructor.

Setting Cron job with cakephp 2.0 in Easy steps:

 
Setting Cron job with cakephp 2.0 in Easy steps: 
 
In some cases we need to do some process automatically every day /every hour / every week. For this 
we can use the Cron job. To see more about Cron Job Click here.
 
This example will shows that sending email to customers every 6 hours based on some  conditions:
 
My Project name is Sample and the Sample folder contains the following folders:
Sample
  — app
   — Controller etc
  —  lib
  — vendors
  — plugins
 
 
Step 1:  Go to the following folder
           Project folder/app/Console/Command/ 
 
Step 2: Create a file named MyShell.php  (You can create the file as per your own risk like SampleShell, CalShell etc)
 
Step 3: Paste the following code in that file. 
 
<?php 
   class MyShell extends Shell {
    function main()
    {
        App::import(‘Component’, ‘BusinessLogic’);
        $businessLogic = & new BusinessLogicComponent();
        $businessLogic->initialize();
        $settings = $businessLogic->senReminderEmail();
           // senReminderEmail Mail function is defined in my Controller/Components/BusinessLogicComponent.php File
    }
    function expireMail()
    {
 App::import(‘Component’, ‘BusinessLogic’);
        $businessLogic = & new BusinessLogicComponent();
        $businessLogic->initialize();
        $settings = $businessLogic->senExpireEmail();
     // senExpireEmail Mail function is defined in my Controller/Components/BusinessLogicComponent.php File
    }
}
?>
 
 
Step 4 : Go to the following folder:   Project folder/app/Vendor/Shell/
            Inside that create a file name called cron.sh and paste the following code.  Give 777 permission to this file.
 #!/bin/bash
# lock logic for semaphore – http://mywiki.wooledge.org/BashFAQ/045
lockdir=/tmp/cron_sh.lock
echo >&2 “$(date ‘+%Y-%m-%d %H:%M:%S’)
 
 #################################################”
if mkdir “$lockdir”
 then    # directory did not exist, but was created successfully
     echo >&2 “successfully acquired lock: $lockdir”
 
##########################################################################################################
 
# —————————————————————–
# Project name —>
# =========
 
 
# Project folder/lib/Cake/Console/cake – app  Project folder/app cron
/var/www/Sample/lib/Cake/Console/cake – app /var/www/Sample/app cron
##########################################################################################################
rmdir “$lockdir”
 else
     echo >&2 “cannot acquire lock, giving up on $lockdir”
     exit 0
 fi 
 
Step 5: To check that the cron functions are working or not, Go to the following folder in terminal
 Project folder/app/  #example  /var/www/Sample/app
and run the following Command :
 /var/www/Sample/app$  Console/cake myshell
The above line will execute the main function that we written in the MyShell.php file
To call other function just Console/cake my function_name
example: Console/cake my expireMail
Check the above functions are executing without any errors. If it executes with out any error then go for next step
Step 6: Now we are going to set the crontab. Just go to the Project folder through terminal and type crontab -e
It will open the editor in that paste the following code:
#The below line will execute the function every 6 hours
# *  */6   *    *   * /var/www/Sample/app/Console/cakeshell  my -cli /usr/bin -console /var/www/Sample/lib/Cake/Console -app /var/www/Sample/app
 
#The below line will execute the function named “expireMail” from myshell.php every 6 hours and it will log the execution in to the Home/error_file.log file
# *  */6   *   *   * /var/www/Sample/app/Console/cakeshell  my -cli /usr/bin -console /var/www/Sample/lib/Cake/Console -app /var/www/Sample/app >> ~/error_file.log