FIND_IN_SET() Function in MySql

How to use FIND_IN_SET() function in MySql . This tutorial explain how to use this in-built function to search for values within a comma separated values.

While working on some module i came across with MySql FIND_IN_SET() function. In my case i have to pull all the records whose status match with status list separated with comma are stored in table.

Syntax of FIND_IN_SET() function

1	FIND_IN_SET(argument1,argument2)

argument1 is a string.

argument2 is a string list separated by comma.

find_in_set() function returns the position of a string within second string. Returns zero when search string doesn’t exist in string list.

Note:

i) If string is not found in string_list, the FIND_IN_SET function will return 0.

ii) If string is NULL, the FIND_IN_SET function will return NULL.

iii) If string_list is an empty string, the FIND_IN_SET function will return 0.

iv) If string_list is NULL, the FIND_IN_SET function will return NULL.

Examples

1 2 3 4

/* Search a in a string of 'b,a,c,d' */ select find_in_set ('a', 'b,a,c,d');   //output - 2

1 2 3 4 5

/* Search h in a string of 'b,a,c,d' */   select find_in_set ('h', 'b,a,c,d');   //output - 0, as h is not present in a list

1 2 3 4 5

/* Search a in NULL value. */   select find_in_set ('a', NULL);   //output - NULL

Explanation of find_in_set() Function through Example

Create one table category and populate some values.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create table category (   category_id int(10) NOT NULL AUTO_INCREMENT,   name varchar(200),   cat_path varchar(200),   PRIMARY KEY(category_id) );   insert into category (name,cat_path) values('Electronics','4,2,6,7');   insert into category (name,cat_path) values('Mobiles','2,4,6');   insert into category (name,cat_path) values('DSLR','1,4,7');   insert into category (name,cat_path) values('Fashion','3,9,2');

Table will look like this.

1 2 3 4 5 6 7 8 9 10

mysql> select * from category; +-------------+-------------+----------+ | category_id | name        | cat_path | +-------------+-------------+----------+ |           1 | Electronics | 4,2,6,7  | |           2 | Mobiles     | 2,4,6    | |           3 | DSLR        | 1,4,7    | |           4 | Fashion     | 3,9,2    | +-------------+-------------+----------+ 4 rows in set (0.00 sec)

Let’s write query to find all the category_id and name whose cat_path contains value 4.

1 2 3 4 5 6 7 8 9 10 11

/* Search category 4 in cat_path (which is a comma separated cat_ids) */ mysql> select * from category where find_in_set('4',cat_path); +-------------+-------------+----------+ | category_id | name        | cat_path | +-------------+-------------+----------+ |           1 | Electronics | 4,2,6,7  | |           2 | Mobiles     | 2,4,6    | |           3 | DSLR        | 1,4,7    | +-------------+-------------+----------+ 3 rows in set (0.00 sec)

Three matching rows are fetched.

Some Important Facts a developer should know

A programming language used to design dynamic web pages. It is generally used to get data out of the database and display it on the web page. PHP can be spread on many web servers and almost on all operating systems without charging any cost and released under PHP license. It has also resulted in the growth of many frameworks that provide further steps for building and designing structures for promoting application development. PHP has many basic functions and many more extensions. These functions and extensions are smoothly executed on the PHP site.

The visibility and presentation of PHP properties are the vision in PHP. And the visibility is expressed by using the keywords public, private and protected.

A PHP developer should know 10 interesting facts:

1. PHP in its original sense stands for Personal Home Page and officially it stands for ‘Hypertext Preprocessor’.
2. It is the most commonly used and common purpose programming language. It is that scripting language which can be written in HTML context also.
3. The PHP code provides an output which is combined with the script of HTML and after that results are sent to the person designing web pages.
4. PHP 3 was officially launched in June 1998, PHP 5 has already been developed and PHP 6 is under development. The major changes taken place are the removal of magic quotations, safe mode etc.
5. PHP has launched some of the biggest online brands such as Facebook, Friendster, Flickr, yahoo etc.
6. For designing the web pages PHP takes into account two types of programming; procedural programming and object oriented programming.
7. The possibility of using PHP is in every operating system for say in Linux, Microsoft, and Windows etc.
8. The strength of PHP developers is about 5 million throughout the world.
9. There is a sign in feature requesting separate user name and password also which is mandatory and helps in securing the website.
10. A script having PHP code can help in many ways such as creating images, reading and writing files.

The PHP language is controlled by an interpreter and that interprets only executes PHP code within its delimiters. Anything beyond the delimiters is not able to get processed by PHP. While delimiters used within the said limit, can make script files less portable. The main purpose of all these delimiters is division of PHP code from non-PHP code, also including HTML. PHP language allows programmers to write extensions in C language to add a type of functional sense to the PHP language. The PHP language’s external core functions such like as those which deals with strings and arrays also implemented as an extension. There are some common criticisms of the PHP language which include lack of support for object-oriented programming, exception handling etc. In the years passed there have been some issues found relating to the security check within this framework. Despite of this criticism from PHP designers and programmers, PHP is still the named n most popular scripting language in the world.

This article is written by Jay Krishna Yadav, he is a part time blogger and an article writer .He has his guest posting and link building service.

Things you can do to become a better php developer

In recent days the PHP language is getting more and more obvious for developers. If any developer or programmer wants to build a networking site or an internet site then he should know the PHP language. So, PHP language is a must learn for a developer. There are some facilities to work on PHP database. It allows you to change your domain and update it regularly without changing its previous work directory. The sites like Wikipedia, Facebook all are developed in PHP. So, we can understand the demand of a good PHP developer. It is really very safe in developing PHP. Choosing PHP for developing networking sites is a good approach as it consumes less time than other languages and it is also very effective. PHP is actually server based scripting language for upgrading internet sites. The main application of the PHP language is the PHP developing modulator which converts it addressing structure. Its commanding line interfacing capability helps the graphical development users to set up their structural ideas.

Capability of resolving: The PHP language runs by its runtime resource center which converts its coding into web developing dynamic web pages. These types of scripting language are based on real time system approach. It is simple to manage the settings of the networking settings. According to need of the change it has the capability of resolving it.

Simple effort of developers: By giving simple effort the whole process can be updated, so the PHP is effective in changing and developing the internet sites. It can fulfil the need of recent day’s demands by changing some of the setting processes.

Difference from other languages: PHP language helps the developers in two ways. The account able part of any networking site can solve and run through it. The other part is web designing part; this part is really challenging and needs proper ideas for developing these. It is effective to deal with it. Solving those problems actually needs proper PHP learned developers. Normal site developers can’t understand its techniques, as it is not same as the other developing languages. So, only trained and well skilled developers can work on it. The main usefulness of the PHP language is that it solves the problems instantly.

Ideas about PHP: If a developer wants to work on PHP then he do not need to know all the procedure of the PHP languages. Just knowing the particular portion of the language can help it. Working in this platform actually helps the developer to move in any direction of web pages as it has the ability to create unique system codes for each page. So, tracking the base domain sites is not very easy.

Classes and variables: Working in PHP language is very helpful to a developer who wants to give a dynamic access to his developed website. So that he can update and change it according to the demand of the market and growing fields.

The above discussion in PHP language is good enough to understand the need of qualities to be a good PHP developer.

This article is written by Jaykrishna Yadav. He is a blogger , Seo expert  and a contributor to   top software companies  and loves to be a part of this blog.

Difference between IN and FIND_IN_SET - MYSQL

You may be confused with IN() and FIND_IN_SET() MYSQL functions. There are specific case/situation for both functions where to use which Mysql function. Look at below explanation about IN() and FIND_IN_SET().

IN() : This function is used when you have a list of possible values and a single value in your database.
Example: WHERE memberid IN (1,2,3)

FIND_IN_SET() : This function is used where you have comma separated list of values stored in database and want to see if a certain value exists in that comma seperated list.
Example: WHERE FIND_IN_SET( ‘Bhavin’, namelist )