Load file using MySQL

Most of the Programmers will be using the normal file operations to import a CSV file, we can use MySQL Load file too import a CSV to database table.

Below is the syntax to import the file to DB

LOAD DATA INFILE "/home/mysql/data/my_table_data.csv" INTO TABLE dummy_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Retrieve the XML node value using PHP

When we want to read the XML tag value, we may use simple_xml_load or DOMdocument to read it and will get the particular node value. Below is the simple PHP function to retrieve the XML node value by passing the XML as string and passing the starting XML tag and ending XML tag.


function get_value_by_tag_Name( $str, $s_tag, $e_tag)
{
$s = strpos( $str,$s_tag) + strlen( $s_tag);
$e = strlen( $str);
$str= substr($str, $s, $e);
$e = strpos( $str,$e_tag);
$str= substr($str,0, $e);
$str= substr($str,0, $e);
return  $str;
}

Here $str is the XML which we are passing as a string, $s_tag is the Starting tag(Ex: '<EmployeeDetails'>) and $e_tag is the ending tag (Ex: '</EmployeeDetails>'). By passing the values it returns the node value.

So, it reduces the parsing XML and DOM Document steps.

Difference between MyISAM and InnoDB MySQL

InnoDB and MyISAM most popular storage engines in MySQL & also mostly used storage engine is the MyISAM. But most of them don't know the importance of InnoDB.

Below are the basic differences of MyISAM and InnoDB.

1. InnoDB supports transactions which is not supported by tables which use MyISAM storage engine. Transaction is nothing but a set of MySQL queries run in an Stored Procedure as shown (BEGIN TRANSACTION ......SET of QUERIES ......END). 
2. InnoDB has row-level locking, relational integrity i.e. supports foreign key relationship between tables, which is not possible in MyISAM.
3. InnoDB ‘s performance for higher volume data which cannot be beaten by any other storage engines available.

Tables created in MyISAM are known to be much faster compared to tables in InnoDB. But since InnoDB supports volume, transactions, integrity it’s always a better option when we are dealing with a larger database.

Find the nth highest Salary in a table using MySQL

To find the nth highest salary or highest score in a table, below is the query to find out.

SELECT * FROM employee a WHERE n = (SELECT COUNT(*) FROM employee b WHERE a.salary <= b.salary) ;

'n'  --> By replacing n with the required value we can get the second, third, fourth highest and so on..