Display Other Texts rather then English using PHP MySQL

When we want to display and store the data other than ENGLISH using PHP.

Below is the code used to save and retrieve the data in PHP.

mysql_set_charset('utf8',$db_connect);

where $db_connect is the "MySQL Host" connection handler. You can see the example below for the connection handler.


<?php
$db_connect = mysql_connect(HOST_NAME,USERNAME,PASSWORD);
mysql_set_charset('utf8',$db_connect);
?>

By using the above, we can store and retrieve the data in different languages like Telugu, French, Hindi, German and many.

Note: And also a major note we need to be keep in mind, when we try to retrieve the same UTF-8 converted data using AJAX. We should not use "mysql_set_charset" as AJAX by default sets the content as "UTF-8" 

Difference between current date and past date using MySQL

Below is the function to get the time difference between Current date and the past date using MySQL. Mainly it can be used where we want to display the time gap between the registration of the user.

function getTimeDifference($created_since)

{

            $datediffquery = mysql_query  ("SELECT CASE WHEN TIMESTAMPDIFF(YEAR ,'".$created_since."', NOW()) > 1 THEN CONCAT(TIMESTAMPDIFF(YEAR ,'".$created_since."', NOW()), ' years ago')

                                                WHEN TIMESTAMPDIFF(YEAR ,'". $created_since."', NOW()) > 0 THEN CONCAT(TIMESTAMPDIFF(YEAR ,'". $created_since ."', NOW()), ' year ago')

                                                WHEN TIMESTAMPDIFF(MONTH ,'".$created_since."', NOW()) > 1 THEN CONCAT(TIMESTAMPDIFF(MONTH ,'".$created_since."', NOW()), ' months ago')

                                                WHEN TIMESTAMPDIFF(MONTH ,'".$created_since."', NOW()) > 0 THEN CONCAT(TIMESTAMPDIFF(MONTH ,'".$created_since."', NOW()), ' month ago')

                                                WHEN TIMESTAMPDIFF(DAY ,'".$created_since."', NOW()) > 1 THEN CONCAT(TIMESTAMPDIFF(DAY ,'".$created_since."', NOW()), ' days ago')

                                                WHEN TIMESTAMPDIFF(DAY ,'".$created_since."', NOW()) > 0 THEN CONCAT(TIMESTAMPDIFF(DAY ,'".$created_since."', NOW()), ' day ago')

                                                WHEN TIMESTAMPDIFF(HOUR ,'".$created_since."', NOW()) > 1 THEN CONCAT(TIMESTAMPDIFF(HOUR ,'".$created_since."', NOW()), ' hours ago')

                                                WHEN TIMESTAMPDIFF(HOUR ,'".$created_since."', NOW()) > 0 THEN CONCAT(TIMESTAMPDIFF(HOUR ,'".$created_since."', NOW()), ' hour ago')

                                                WHEN TIMESTAMPDIFF(MINUTE ,'".$created_since."', NOW()) > 1 THEN CONCAT(TIMESTAMPDIFF(MINUTE ,'".$created_since."', NOW()), ' minutes ago')

                                                WHEN TIMESTAMPDIFF(MINUTE ,'".$created_since."', NOW()) > 0 THEN CONCAT(TIMESTAMPDIFF(MINUTE ,'".$created_since."', NOW()), ' minute ago')

                                                WHEN TIMESTAMPDIFF(SECOND ,'".$created_since."', NOW()) > 1 THEN CONCAT(TIMESTAMPDIFF(SECOND ,'".$created_since."', NOW()), ' seconds ago')

                                            END

                                        AS total_days");

            

            $row = mysql_fetch_assoc($datediffquery);

            

            return $row['total_days'];

        }

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';

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..

Replace the contents in MySQL data

Below is the Query to replace the data with the required data in the column fields for the Table.

UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'search string','replace string');

table_name --> The table data which you want to change

column_name --> Particular column of the table