" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi

Using Inner Cursor in MySQL

Posted on November 24th, 2015 by Rashmita Devi

In MySQL,  inner cursor ( which means Cursor inside another Cursor) does not work properly for stored procedures. So you can use “BLOCK” in such situations.   For example, if you want to create a stored procedure to iterate through all the records of a Master table and update related records in the Child table, the following code snippet can help you […]

MySQL: Ordering values according to a predefined pattern

Posted on November 24th, 2015 by Abinash Gracharya

This one is a very interesting and tricky tip. In my recent project I faced a problem  executing a query to find all the records from a table in a given order. The Problem In MySQL we can order the records by – desc , asc or in RAND() to a particular field or for multiple fields. But what […]

How to create temporary table in MySQL

Posted on November 24th, 2015 by Rashmita Devi

It is faster to get data from temporary table instead of getting data form DB tables with several filter conditions. Temporary tables are connection specific. When the current client session terminates all the temporary tables are automatically deleted. Temporary table with same name can be created with in different connections. Here is the syntax to […]

MYSQL:Ordering A TextField Numerically

Posted on November 24th, 2015 by Manaswinee Mohapatra

While ordering the numeric values stored in a text field(having DataType as VarChar,Char,Text etc) in a MySQL table ,you may not be able to get the expected result. For example if your table contains a field “NUMBERS” which is of varchar type and having the values 1, 2, 3, 4, 5 upto 10.Then the query […]

MySQL: Filter or Search Records Using Regular Expression

Posted on November 24th, 2015 by Manaswinee Mohapatra

We can filter or search records according to the matched Regular Expression in MySQL. For that the syntax will be SELECT Col1, Col2, Col3   FROM   table_name   WHERE Col1 REGEXP pattern Where Col1, Col2, Col3 is list of columns to be fetched.You can select any number of columns. Pattern is the regular expression to be matched […]

MySQL: How to get the Particular Data Range

Posted on November 24th, 2015 by Rashmita Devi

If we want to fetch the data with in a particular Date range then we can use the INTERVAL keyword.   For example,   Let’s say I want to fetch the records which have been inserted with in last 10 days that means the records which are added with in 21/7/2009 to 30/07/2009(30/07/2009 is Totay’s […]

MySQL : Exporting The Resultset of a Table into CSV File

Posted on November 24th, 2015 by Manaswinee Mohapatra

To export the resultset of a table into a .csv file in MySQL we have to execute the following query. SELECT * INTO OUTFILE pathName FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ FROM tableName Where ‘pathname’ is the path along with the .csv file name and ‘tableName’ is your table. […]

How to backup and restore MySQL database.

Posted on November 24th, 2015 by Partho Banerjee

Two ways to take backup of existing MySQL database: 1. Exporting tables to text files using MySQL dump. 2. Copying database files. (within same version) not recommended.   Exporting tables to text files using MySQL dump:   This is the best solution to keep backup of database so that we can restore it to any version of […]

MySQL: Implementing case-sensitive functionality in query

Posted on November 24th, 2015 by Rashmita Devi

Like SQL Server , in MySQL also we can check case-sensitivity inside a query. For this we have to use “BINARY” keyword.   Lets say I have a Users Table having a column named as “FirstName” ofVarchar DataType. Below are the records present inside this table. Id        FirstName        LastName      JobTitle 1         Albert             Roger       Software Engg […]

Display summary report using a simple query in MYSQL

Posted on November 24th, 2015 by Madhusmita Pradhan

In MYSQL GROUP BY provide us a modifier called WITH ROLLUP, which will added an extra row to the output as a summary report. For Example: Suppose we have a Sales table having SalesId, ContactId and SalesTotal. If we need a summary report, which will display total SalesTotal group by its ContactId. This can be […]

MySQL: Using “ON DUPLICATE KEY UPDATE” while inserting record

Posted on November 24th, 2015 by Rashmita Devi

Sometimes we may have the situation where multiple users insert record into the same table simultaneously . Lets say the primary key for a table is an integer field and user executes an insert query by giving the value for the Id (Primary Key) field as “1”. But at the same time another user has […]

Using Regular Expressions with MySQL queries to Search

Posted on November 24th, 2015 by Sujata Mohanty

Regular expressions are a powerful tooI as most of you will agree but how does one use it with SQL queries ? Recently I had a requirement where I had to write a query that searches for the word “test” in a description field. Usually we use MYSQL “LIKE” or “Full Text” index queries to […]

Backup SQL Database Through Code In VB.NET

Posted on November 24th, 2015 by Amrita Dash

Using the SQL SMO object we can easily take backup of SQL database through code. Following is the sample code used take backup.     [VB.NET CODE STARTS] ‘–Reference added Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.Common   ‘–Declare the backup object Dim WithEvents oBackup As New Backup   ‘–Method to take backup Private Sub BackupSqlDatabase()   Dim […]

Replace function in MySQL

Posted on November 24th, 2015 by Supriti Panda

Like different languages, MySQL too has a Replace() function, which allows you to replace characters. Syntax: replace(fieldname, ‘find string’, ‘replace with this string’); Uses: 1. It can be used in select query to display it in the website without permanent change in db. Syntax: SELECT replace(fieldname, ‘find string’, ‘replace with this string’) AS newfieldname FROM […]

Get list of existing TRIGGERs in MYSQL

Posted on November 24th, 2015 by Madhusmita Pradhan

While MySQL has features like StoredProcedure and View, it doesn’t have any option for displaying TRIGGER list in the ‘Schemata’ tab (right) section of QueryBrowser. Generally, we need a third party control like MySQL Workbench to get the list of existing TRIGGER lists.But there is another way through which we can get existing trigger lists and that is from the Metadata(Data about data). […]

How to find DB size and table info in MYSQL

Posted on November 24th, 2015 by Satheeskumar A

1. To know the size of the databases We can use this query “SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema” This will display all the databases and its related size in MB. But we can change the range of […]

Using BINARY function in MySQL for case sensitive search

Posted on November 24th, 2015 by Satheeskumar A

Generally when we search for a string stored in MySQL database we resort to using “Like” or “=” operators but it returns a case-insensitive search result only. One of my recents projects required me to conduct a case-sensitive search. There are two ways to deal with it. 1. Changing the collation from ci to cs            – In […]

How to update field values partially in MySQL UPDATE statement?

Posted on November 24th, 2015 by Chittaranjan Pattnaik

Generally UPDATE statement is used to update a field value completely. But there may be situations when we need only part of the field value to be replaced with some other values. So in this case we can’t use the traditional UPDATE statement as it may not find all the matches for our search and […]

Difference between utf8_general_ci and utf8_unicode_ci

Posted on November 24th, 2015 by Satheeskumar A

Its not only the query we write which increases MySQL performance a lot of times the collation that we use  when we build db or db tables affect performance too. We all know that there are lot of collation and we can select one from the dropdown from phpmyadmin. But these collation we select for our […]

EXPLAIN your query in MySQL

Posted on November 24th, 2015 by Madhusmita Pradhan

There are some easy ways available, using which we can check how SQL query is taking the index to its fullest extent. MySQL provide us a command called EXPLAIN, which can help us to explain our SQL query and give us some brief idea on how to write good SQL query.   For speeding up query, […]

Using FIELD function in ORDER BY clause in MySQL

Posted on November 24th, 2015 by Runish Kumar

Introduction to FIELD function: FIELD(str, str1, str2, str3, …) function search for ‘str’ in the list ‘str1,str2,str3…’ and returns the index (position) of str in the list. If ‘str’ is not present in the list 0 is returned. If ‘str’ is NULL, it returns 0. Example: SELECT FIELD(‘ab’, ‘abc’, ‘ab’, ‘rabdf’, ‘fab’, ‘rtg’); output: 2 […]

Stored Procedure execution using MySQL Extension

Posted on November 24th, 2015 by SAMBIT MISHRA

Friends, here I am going to share an interesting thing which I faced recently. In PHP, everyone knows to establish a mysql connection we use (This is the standard) <?php mysql_connect(‘HOST_NAME’, ‘USER_NAME’, ‘PASSWORD’);\* Using MYSQL extnsion *\ /* OR */ mysqli_connect(‘HOST_NAME’, ‘USER_NAME’, ‘PASSWORD’);\* Using MYSQLi extnsion *\ ?> Presently, everyone is using Stored Procedures and […]

How to add row number in a query result set.

Posted on November 24th, 2015 by Subhashish Chaki

Sometimes we may need to add row number in query resultset. We can use the below query for automatically addition of row number in resultset. Suppose we have a table student. SELECT * FROM STUDENT; Output First_Name Last_Name Address M Das BBSR R Chakraborty Cattak F Purokayat Kolkata We need to add row number in […]

How to find the engines of MySQL tables

Posted on November 24th, 2015 by Satheeskumar A

Mostly programmers use two types of engines for MySQL DB. They are Innodb, MyISAM. You can check this details very easily in PHPMyAdmin. But if you are in MySQL interactive mode in command prompt. you cant get the details that easily from your DB. But the details of your DB, Tables etc .. are maintained in […]

Physical Mysql – what are these? (.FRM, .MYI, .MYD). Need this?

Posted on November 24th, 2015 by Satheeskumar A

Most of us work with PHPMYADMIN, Mysql Query Browser, Mysql Migration Tool, Mysql Administrator where we see the database and tables with their logical view. How many of us have see the physical structure of our db?. When the term physical comes, several question arises 1) where do they reside? 2) what sort of file […]

Self join and its usage

Posted on November 24th, 2015 by Dinesh Sahoo

Sometimes we wonder when to use Self joins. I was getting confused regarding its usage as a newbie. Sometimes, they are very useful as it can be used in place of nested queries maintaining the efficiency of our application. I would like to share one scenario where it was very useful to me. Problem: We […]

Swap table name in MySQL, SQL Server

Posted on November 24th, 2015 by Subhashish Chaki

Sometimes we need to swap table names of existing two tables. We can do this using a single sql statement in MySQL. Suppose we have two tables SalesOrder, SalesOrderDetails. Here is the description of these tables DESCRIBE SalesOrder; SalesOrder ‘SaleOrderNo’, ‘ItemNo’, ‘Qty’, ‘UnitPrice’ DESCRIBE SalesOrderDetails; SalesOrderDetails ‘SalesOrderNo’, ‘Date’, ‘CustomerNo’, ‘ClerksNo’ So we want to swap […]

Some Interesting features of ENUM data type in MySQL

Posted on November 24th, 2015 by Madhusmita Pradhan

n MySQL, ENUM is a datatype that allow us to define some list of permitted values during creation of table. This will help us to restrict values that are going to be entered against a column. This is something like white listing(list of permitted values) that we have learned in security section for web application. […]

Automatic Database(MySQL) backup

Posted on November 24th, 2015 by Bachan Smruty

Create automatic database(MySQL) backup by php script into a .sql file in Windows platform. MySQL is one of the most popular open source database management system for the development of interactive Websites. If a site has sensitive data in a MySQL database,then it needs to take backup of the information, so that it can be […]

LAMP Setup on AWS EC2 SUSE Linux

Posted on November 23rd, 2015 by Chittaranjan Pattnaik

AWS (Amazon Web Services) provides EC2 instance for its users which can be used as a webserver and on cloud. Users has the flexibility to choose what operating system and which environment they want to use for their web server. For one of our project the client has selected SUSE linux as the EC2 instance […]

PHP : Connect MySQL through extended class

Posted on November 23rd, 2015 by Amrit Pattanayak

PHP provides inbuilt functions to do the MySQL database operations and we can use the functions in traditional way. But the following is an example of extended class which can be used for the same purpose.   /*************************************************** * Define the database parameters (Usually defined in configuration file) ****************************************************/ define(“DBHOST”, “DB_HOST”);//Database Host define(“DBUSER”, “DB_USER”);//Database User […]

Time Difference problem in PHP and MySQL and its solution

Posted on November 23rd, 2015 by Abinash Grahacharya

In many projects we need to generate the time difference between the current date and the date the record was created. For example, suppose you want to get the date difference of number of comments related to present date and time. What possibilities come to your mind ? First I thought of getting the date time from […]

Using PHP Data Object

Posted on November 23rd, 2015 by Runish Kumar

I just read about PHP Data Object(PDO) and found it really interesting and useful, so I decided to share this with everyone. Introduction to PDO: To explain it better let me take a hypothetical situation, suppose you are working on a project which is almost complete and then you come to know that the client has switched to PostgreSQL database instead […]

Using GROUP_CONCAT IN MYSQL

Posted on November 23rd, 2015 by Zainab Ahsan

USE OF GROUP_CONCAT IN MYSQL It is a useful extension on Group by clause It returns concatenated non null values from a group Syntax:   GROUP_CONCAT(column_name) [ default separator is comma] GROUP_CONCAT(column_name SEPARATOR ‘_’) [ if you want to separate the values with an underscore.]   Suppose you have a requirement like below: You have […]

How To Loop Through A Result Set in MySQL Strored Procedure

Posted on November 23rd, 2015 by Roshan Gupta

Sometimes we get situations to perform a set of tasks for a result set. There are 2 ways to do it. 1. We can fetch the result set in server side(like php) and loop through the data in server side and execute the stored procedure one by one. 2. We can use cursor to do […]

MYSQL Cursor – Not Found continue handler issue

Posted on November 23rd, 2015 by Roshan Gupta

In my last tip i have explained “How to use a cursor in MYSQL to loop through a result set”.  We use cursor to execute a set of tasks on a result set.   we know that the continue handler updates the ‘done’ variable to 1 when a ‘Not Found’ error occurs.   Imagine a condition where […]

SSL Secure connection testing

Posted on November 23rd, 2015 by Amrita Dash

Here is a query to test whether the ODBC connection to MySQL database using SSL certificates is securely done. Even if, in the ODBC manager we set the SSL certificates in the details tab,to confirm that the connection is securely done we can use the following query :   [query] “show status like ‘Ssl_cipher'” [query] […]

How To Keep MySQL Connection Alive In Servoy

Posted on November 23rd, 2015 by Arup Sahoo

While using MySQL as databases in Servoy,at times users experience issues of disconncted database services. The tip below describes the potential problem and the quick and easy fix for the same. For using MySQL as database in Servoy, we need to first create a server connection for the same and we generally use the “com.mysql.jdbc.Driver” […]