Category Archives: Coding & Programming

MySQL vs MongoDB: Comparison Between RDBMS and Document Oriented DB

MySQL vs MongoDB (NoSQL) database

MongoDB vs MySQL: Comparison Between Relational and Document Oriented Database

 

Both are used for storing the data and free to use that is both comes under open-sources software.
MongoDB might be unfamiliar at least to some of us as it is a relatively new compared to other established databases such as MySQL, PostgreSQL or Oracle.

Continue reading

MySQL Interview Questions & Answers

MySQL interview questions for experienced

MySQL query for finding max and second max and nth max salary for a employee table using MySQL

Suppose you have a table ’employee’ given as below
——————————
| EmpId        |    Salary      |
| 2                  |    2000        |
| 3                  |    5000        |
| 5                  |    2000        |
| 8                  |    3000        |
| 9                  |    4564         |
| 12                |    3000        |
| 13                |    2000        |
| 15                |    8000        |
——————————
Now the question is : find max salary and 2nd max salary of employee (MySql Query)

1. Highest salary


SELECT MAX(Salary) FROM employee

2. 2nd highest salary


SELECT MAX(Salary) FROM employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM employee )

OR


SELECT Salary from employee order by Salary desc limit 1,1

You can fetch both as below


SELECT (SELECT max(Salary) from employee) as max_sal, (SELECT Salary from employee order by Salary desc limit 1,1) as max_2nd

SELECT (SELECT MAX(Salary) FROM employee) max_sal, (SELECT MAX(Salary) FROM employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM employee )) as max_2nd

nth height Salary


SELECT * FROM employee Emp1 WHERE (n-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

SELECT EmpId, Salary FROM employee  ORDER BY Salary DESC  LIMIT (n-1) , 1

 

MySQL query for case sensitive string comparison on MySQL.

 

According to MySQL Manual (http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html), It’s depends on what collation you have chooses during the column creation.

You can have a look on various collation available for column data storage. If it’s ‘_ci’ (case-insensitive) or ‘_cs’ (case-sensitive)


If you want the comparison to be case sensitive, you could add COLLATE just like this in your SQL query:
WHERE `colname` COLLATE latin1_general_cs = 'KeyWord'

 

You can also do by cast the keyword of column value to binary as when one side in the comparison is cast to binary the comparison is done binary and it will be case-sensitive


SELECT * FROM your_table WHERE BINARY description = 'KeyWord'

OR


SELECT * FROM your_table WHERE description = BINARY 'KeyWord'

 

To determine whether a value will compare as a non-binary or binary string, use the COLLATION() function. This shows that VERSION() returns a string that has a case-insensitive collation, so comparisons are case insensitive or vice-versa:


SELECT COLLATION(VERSION());

According to MySQL 5.0 Manual(http://dev.mysql.com/doc/refman/5.0/en/charset-column.html), If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation.


ALTER TABLE your_table CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

e.g


ALTER TABLE employee CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;

ALTER TABLE employee CONVERT TO CHARACTER SET utf8;

For modifying the collation for a column only you can follow as below


ALTER TABLE employee MODIFY  name VARCHAR(255) CHARACTER SET latin1  COLLATE latin1_swedish_ci;

 

What are Heap tables?

 

HEAP tables are present in memory and they are used for high speed storage on temporary basis. These tables are commonly known as memory tables.

Below are some feature of Heap tables.

  • BLOB or TEXT fields are not allowed
  • Only comparison operators can be used =, <,>, = >,=<
  • AUTO_INCREMENT is not supported by HEAP tables
  • Indexes should be NOT NULL

 

Differences between HEAP and Temporary tables?

The heap tables are shared among clients while temporary tables are not shared.

Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).

 

What is the default port for MySQL Server?

The default port for MySQL server is 3306.

 

What is the difference between primary key and candidate key?

Every row of a table is identified uniquely by primary key. There is only one primary key for a table.

Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

 

How many columns can you create for an index?

You can create maximum of 16 indexed columns for a standard table.

 

What does myisamchk do?

myisamchk is a command line tool for checking, repairing and optimizing non-partitioned MyISAM tables.

 

What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?

It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.

 

Where MyISAM table will be stored and also give their formats of storage?

Each MyISAM table is stored on disk in three formats:
The ‘.frm’ file stores the table definition
The data file has a ‘.MYD (MYData) extension
The index file has a ‘.MYI’ (MYIndex) extension

 

How to enter Characters as HEX Numbers?

If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).

A HEX number string will be automatically converted into a character string, if the expression context is a string.

 

What is the different between NOW() and CURRENT_DATE()?

NOW() command is used to show current year, month, date with hours, minutes and seconds while CURRENT_DATE() shows the current year with month and date only.

 

What is SQLyog?

SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.

 

What is a trigger in MySQL & how many TRIGGERS are allowed in MySQL table?

A trigger is a set of codes that executes in response to some events.
SIX triggers are allowed in MySQL table. They are as follows:

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE and
AFTER DELETE

 

Some Common SQL Function?

CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.

FORMAT(X, D) – Formats the number X to D significant digits.

CURRDATE(), CURRTIME() – Returns the current date or time.

NOW() – Returns the current date and time as one value.

MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.

HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.

DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculate age

SUBTIMES(A, B) – Determines the difference between two times.

FROMDAYS(INT) – Converts an integer number of days into a date value.

 

Explain CSV tables.

They special tables for which the data is saved into comma-separated values files. They cannot be indexed.
CSV table stores data in plain text and tabular format. It typically contains one record per line. CSV tables are most widely used to store phone contacts to Import and Export and can be used to store any sort of plain text data.

 

Explain advantages of InnoDB over MyISAM.

InnoDb supports locking of rows while MyISAM supports only table locking.
Data integrity is more in InnoDB.
Transactional nature of InnoDB enables easy and online backups.

 

Explain advantages of MyISAM over InnoDB.

Data is not cached by the MySQL query browser.
MyISAM is a simple storage engine.
MyISAM provides more optimization.
MyISAM has a low relative memory use.

 

How large can a MySQL database become?

Thus, as long as your file system supports a maximum file size of at least 256 TB,
you will never run into any file system limitations before MySQL runs into other internal limits.

 

How to get the date after 7 days using MySQL query

select DATE_ADD(NOW(), INTERVAL 7 DAY) as last_date.

 

Define system tables.

mysql
All the user and permission information is stored in the mysql database in a set of tables known as the grant tables.

information_schema
The information_schema database really is not a database but an interface to various system metadata

 

MySQL command for showing current configuration variables


SHOW VARIABLES
SHOW VARIABLES LIKE '%max%'

 

For updating these configuration variable values


SET sort_buffer_size=10000;


SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;

 

Automatic session timeout/logout using PHP after X Minutes of Inactivity/Idle time

PHP Automatic Session Expire after X Minutes of Inactivity/Idle time

 Automatic session timeout/logout using php

 

Session timeout is a notion and the only way you make you sure that no session ever will survive after X minutes of inactivity. Session timeout or Session expire depends on the server configuration or the relevant directives (session.gc_maxlifetime) in php.ini.

 

Typically the default is 1440 seconds(24 minutes), but you can alter the default to something else. Below are some Session configurations.

 

http://php.net/manual/en/session.configuration.php

 

You can update this easily and without writing custom logic.

 

If your sessions are implemented with cookies (which they probably are), and if the clients are not malicious, you can set an time limit on the session duration by tweaking certain parameters. If you are using PHP’s default session handling with cookies, setting session.gc_maxlifetime along with session_set_cookie_params should work for you like this:

 

// server should keep session data for 1 hour
ini_set('session.gc_maxlifetime', 3600);

// each client remember their session id for exactly 1 hour
session_set_cookie_params(3600);

session_start(); // ready to go!

 

You can also put this in .htaccess file with a slight change in syntax.

 

php_value session.gc_maxlifetime 3600

php_value session.gc_probability 1

php_value session.gc_divisor 1

 

session.gc_probability, and session.gc_divisor directives: PHP has garbage collection it uses to clean up sessions that have expired, otherwise on a site with a lot of users accessing the site could cause a huge amount of session files to be continually generated. Garbage collection does not happen automatically and needs to be incorporated into your system maintenance routines.

 

You can also make a custom script that automatically logout a user if user is inactive (not performed any action or idle) for X minutes.

 

1) When user logged in, start session, start session expiry time, like this:

 $_SESSION['expire'] = time() + X*60; 

We took current time, added X minutes in it and stored this in session.

 

2) At every page check that if X minutes (for above script) have been passed or not make a file as include that in that page, like this:

If yes, clear session and logout, like this:

if(time() > $_SESSION['expire']){
    session_destroy();
    session_write_close();
    session_unset();
    $_SESSION = array();
}

 

And then redirect to login page.

 

3) In else statement (if X minutes have not passed), reset time (takes current time and add X minutes in it and restore in session named ‘expire’) stored in session, like this:

else { $_SESSION['expire'] = time()+X*60; }

 

and do nothing – don’t clear sessions, don’t redirect to login page, so that user may stay at website, as much time as he is active.

 

You can also do this purely using JavaScript. Start an countdown timer. Then wait for activity and reset this timer. If there is no activity and timer goes off, you can call your logoff sequence.


/* Resets the timer. The timer is reset on events
   (mouse-move,mouse-click,key press,scrolling),
   these events occurs indicates that user is active on the application:
*/

<body onmousemove="reset_interval()" onclick="reset_interval()" onkeypress="reset_interval()" onscroll="reset_interval()">

<script type="text/javascript">
 
//the interval 'timer' is set as soon as the page loads

var timer = setInterval(function(){ auto_logout() }, 20000);

// the figure '20000' (20 seconds) indicates how many milliseconds the timer be set to.

//e.g. if you want it to set 5 mins, calculate 5min= 5x60=300 sec => 300,000 milliseconds.
 
function reset_interval(){

    //first step: clear the existing timer
    clearInterval(timer);
   
    //second step: implement the timer again
    timer = setInterval(function(){ auto_logout() }, 20000);
    //..completed the reset of the timer

}

function auto_logout(){

    //this function will redirect the user to the logout script
   
    if(confirm("You have been logged out from the application, Press OK to login again!")){
        window.location="your_logout_script.php";
    }

}

</script>

Hope it will works, let me know by your valuable comments if you need any more assistance.

PHP Traits

One of the problem of PHP as a OOP language is, the fact that you can only have single inheritance. This means your class can only inherit from one other class.

 

PHP Traits (new feature was added in PHP 5.4) is kind of like a Mixin, allows you to mix Trait classes into an existing class. This means you can reduce code duplication and get the benefits whilst avoiding the problems of multiple inheritance.

 

As per the PHP Documentation, a Trait is intended to reduce some limitations of single inheritance by enabling a developer to reuse sets of methods freely in several independent classes living in different class hierarchies. The semantics of the combination of Traits and classes is defined in a way which reduces complexity, and avoids the typical problems associated with multiple inheritance and Mixins.

 

A Trait is similar to a class, but only intended to group functionality in a fine-grained and consistent way. It is not possible to instantiate a Trait on its own. It is an addition to traditional inheritance and enables horizontal composition of behavior; that is, the application of class members without requiring inheritance.

 

Let’s take some example to make more clear about Traits.

 

A trait is similar to an abstract class which cannot be instantiated on its own (though more often it’s compared to an interface).


<?php

trait Helloworld
{
      private $message;

      function sayHello() {
        echo "Hello";
      }

      function sayWorld() {
       echo "World";
      }

      abstract function define();
}

?>

It can have functions, data members, abstract function etc same as class you have.

 

PHP have some set of rules defined to use Traits in his best way like Precedence, Use of Multiple Traits, Conflict Resolution, Method Visibility etc.

To understand the Traits and how these can be used to make your life easy, I have googled and found some very good example/posts with explanation that will really help you.

 

One is PHP Documents itself (Bible for all PHP Developers)
http://php.net/manual/en/language.oop5.traits.php

 

Second one is found on SitePoint
http://www.sitepoint.com/using-traits-in-php-5-4/

 

Hope these will helps!!

 

Benefits of Traits

  1. Traits reduce your code duplication whilst preventing complicated class inheritance that might not make sense within the context of your application.
  2. It allows you to define simple clear and concise code and then mix in that functionality wherever needed.
  3. It somehow reduce the situation as generally possible in the multiple inheritance supporting languages called as “Diamond Problem“.

Drawbacks of Traits

  1. Entire class abilities are NOT in one location visually.
  2. You alter the trait thinking it will alter one type of class using it, but may introduce problems in another class also using it. Meaning you will have to know all classes using the trait.
  3. Introduces the idea of multiple inheritance, which bring with it the need to worry about conflicts in naming. (Yes you can solve it using insteadof, but now you have to worry about that and take precautions like specific naming related to the trait or to use this keyword).

Conclusion

So now after have a look on all features and benefits and drawback now a question is, Should you use Traits?
You should, as Traits allow you to create cleaner, simpler, and more efficient code fragments horizontally across multiple classes which do not have to be within the same inheritance hierarchy.

As a developer we have some bad or good situation for all code and logic, so it is your choice how will you play with Traits?

How to prevent gaps/holes in a MySQL table AUTO_INCREMENT Primary Key field

Prevent gaps/holes in a MySQL table AUTO_INCREMENT Primary Key field.

 

Generally during the MySQL database development you create tables with a Primary Key, a very simple way to create a primary key is make it Auto-increment and unsigned integer with Not Null property right?

 

I think most of you generally follow the above process. Suppose you are playing a very large database and have a very large number of Ids, I know this is rarely happens but you should aware this one, you might face an issue where the new row insertion will through error.

 

Have a look on MySQL data type and their range

 

INT – A normal-sized integer that can be signed or unsigned.
– If signed, the allowable range is from -2147483648 to 2147483647.
– If unsigned, the allowable range is from 0 to 4294967295.
– Max allowed length is 11 digits.

 

TINYINT – A very small integer that can be signed or unsigned.
– If signed, the allowable range is from -128 to 127.
– If unsigned, the allowable range is from 0 to 255.
– Max allowed length is 4 digits.

 

SMALLINT – A small integer that can be signed or unsigned.
– If signed, the allowable range is from -32768 to 32767.
– If unsigned, the allowable range is from 0 to 65535.
– Max allowed length is 5 digits.

 

MEDIUMINT – A medium-sized integer that can be signed or unsigned.
– If signed, the allowable range is from -8388608 to 8388607.
– If unsigned, the allowable range is from 0 to 16777215.
– Max allowed length is 9 digits.

 

BIGINT – A large integer that can be signed or unsigned.
– If signed, the allowable range is from -9223372036854775808 to 9223372036854775807.

– If unsigned, the allowable range is from 0 to 18446744073709551615.
– Max allowed length is 20 digits.

 

Let a suppose you have specified your primary key as INT so the max number you can enter with in it is 4294967295 (if unsigned),
Yes it is not a small number and will not reach generally but if the system allow deletion of records/data on daily basis then what will happen, one day a new row will not insert and it will reach a max limit as you know once a row deleted that id will not use any more and you will have a lot of holes/gaps in the id that was not used.

 

I think you have got what I want to say. You have a table named as student with simply 2 columns
id and name and your table data are

 

1 student1
2 student2
3 student3
.. ..
20 student 20

 

Now you have deleted 2 and 3 id’s records then these id will never user by auto-increment id (primary key), you can use these id but you should know which id is not used and how to fetch this automatically, it will very hard to find if there are lot of rows.

 

How to fill or use this id or holes or gaps automatically.
Use the below query to insert the id instead of use this automatically. It will return NULL if no gap/hole is there in the primary key.

 


SELECT MIN(st1.id+1)
FROM student st1
LEFT JOIN student st2 ON st2.id = st1.id + 1
WHERE st2.id IS NULL

The above query returns you an id that can be used to insert the new row.

There are some most important note you should take care before using these.

 

You should not have values (id) on other tables associated (as a foreign key) with the deleted primary key. If this happen your rest of life will not fine. You should take care that when you insert a new entry with primary key that was taken from the gap, the gaped value should not be there in the associated tables.

You should clean ALL of your tables when you delete a primary key.

 

PHP short_open_tag = On should not be used

After a fresh installation you could find that PHP short_open_tag disabled by default in php.ini file.

 

You can check by using phpinfo() PHP function.

Under PHP Core setting you can find as below

 

Directive Local Value Master Value
short_open_tag Off Off

 

If “short_open_tag” is found off in your php.ini file then you cannot use short form (<? ?>) of PHP’s open tag and you must use long form of PHP tags (<?php ?>) instead, otherwise any PHP code inside these tags will not be parsed as PHP.

 

Drawback of PHP short_open_tag if you have enabled.

 

1) Setting this value to off allows for easier use alongside XML by providing the ability to use <?xml ?> inline without having to print it with PHP echo ‘<?xml version=”1.0″?>’; ?>.

 

With the wide spread use of XML(SOAP, XMLRPC, REST-XML) and use of these tags by other languages, the server can become easily confused and end up parsing the wrong code in the wrong context. But because this short cut has been a feature for such a long time, it’s currently still supported for backwards compatibility, but it’s recommend you don’t use them.

 

2) All the standard library use <?php ?>, full php tags so we can say it’s a standard, if you are open source script (distributable or portable script) writer or have PHP open sources community member then it’s always recommended to use full tag instead of short_tag as by default PHP have it disabled in php.ini settings.

 

3) Due to security reasons in some extent it’s advised to not to use short_open_tag.

 

If you still want to use short_open_tag or if you have just entered in a large application that is previously build by any other developer and he have used short_tags in many places, but your you php setting is was disabled then you should enabled short_open_tag, let me tell how you should do this quickly.

 

1) Open you root’s .htaccess file and add the following in that

php_flag short_open_tag on

or

php_value short_open_tag 1

 

2) If you php.ini access and can update any php directive and setting then open php.ini file and change
short_open_tag = Off to short_open_tag = On

It’s always advised to start Apache services after any changes in php.ini settings.

 

3)

 ini_set( "short_open_tag", 1 ); 

will not work, if you are trying to do as it (short_open_tag) is marked as PHP_INI_PERDIR in PHP < 5.3.0, which means you can’t change it with ini_set(). You can check this by below link.

 

Description of core php.ini directives