Category : MySql


How to check empty or null in Mysql Query?

If you want to check empty or null value with conditional statement like case when and if in table row with Mysql database. you like to check empty or null value form table then you have multiple way check that. first we check empty value with case when statement then following example. <strong>Empty Check:</strong> <pre class="language-mysql"> SELECT (CASE WHEN COALESCE(email,'')!='' THEN email ELSE 'example@gmail.com' END) as test FROM `customers` ---------------------OR---------------------- SELECT (CASE COALESCE(email,'') WHEN '' THEN 'example@gmail.com' ELSE email END) as test FR.....

By Hardik Savani | January 31, 2016 | | 757 Viewer | Category : MySql
READ MORE

How to copy one table data into another table using Mysql Query?

If you are working on php or any php framwork and you want to copy one table into other table by using sql query, so you could do it using simple SELECT and INSERT statement. just you need to think how you can use. In Following example you can see how to fetch data from "post" table and how to insert into "post_copy" table, so basically you have to select field of one that you want to copy and second one give name of table with proper field that you want insert. But don't forgot to give a name of field otherwise it generate error. <pre class="language-mysql"> INSERT INTO `post_copy` (name,sl.....

By Hardik Savani | January 30, 2016 | | 1260 Viewer | Category : MySql
READ MORE

GROUP_CONCAT with different SEPARATOR in laravel Example

Whenever, you need to use GROUP_CONCAT with differente separator(I mean default separator is ',', But you want to change '@','#','&' etc as you want). then you have to use use SEPARATOR keyword in GROUP_CONCAT(). If you are working on laravel then you have to also use DB::raw() for write GROUP_CONCAT() inside the this function. So, Basically how to change seprator in group_concat(), In following example i change ',' separator into '@'.you can use the following example how to use in laravel 4 and 5. <strong>Example:</strong> <pre class="language-php"> $items = DB::table('items') -.....

By Hardik Savani | January 20, 2016 | | 3099 Viewer | Category : MySql
READ MORE

How to count unique domains from email address field in MySQL ?

you want to count domain from your email address field in mysql. if you are working on PHP then you could do easily by using explode function and counting domains, but that way is a very bad logic and it take max time on execution when you have lot of data. But we can do easily in MySQL by using SUBSTRING_INDEX(). SUBSTRING_INDEX() take a three arguments string, delimeter and number.string is a source of string, the delimeter to search for in the string and the number parameter will search for delimeter.If pass negative in third argument then it will everything from the left of the targeted .....

By Hardik Savani | January 14, 2016 | | 581 Viewer | Category : MySql
READ MORE

How to get last executed mysql query in laravel ?

If you are working on laravel framwork and you use query builder of laravel 5. but you want to get last executed mysql query from laravel. If you are working on laravel 5 then you have to first enable query log that way it can be store query log. At last getQueryLog() through you can get last executed query. I give you example of how to get last executed mysql query. you can use example as following example. <pre class="language-php"> public function querylog() { DB::enableQueryLog(); $item = Item::find(2); $last_query = DB::getQueryLog(); print_r($last_query); } </pre> Try this......

By Hardik Savani | January 11, 2016 | | 1128 Viewer | Category : MySql
READ MORE

Mysql procedure with pagination in laravel 5?

You are working on laravel with mysql database. you had created mysql procedure for get data and you want to call procedure with pagination in laravel 5 then you can not directly make pagination as laravel document.If you are using directly MySQL stored Procedure in your controller, model or repository and you want to give pagination like this way : <pre class="language-php"> $data = DB::select(DB::raw('CALL hardik("hari")'))->paginate(5); </pre> This way you found error in your Laravel 5 website. we can't give directly this way pagination because your procedure will get all the data fr.....

By Hardik Savani | January 11, 2016 | | 2247 Viewer | Category : MySql
READ MORE

Which MySQL datatype use for store an IP address?

If you want to store IP address in mysql database then don’t mistake to use varchar datatype because you can use INT UNSIGNED 4(BYTE) datatype. using integer datatype you can save more space in database. when you fire insert query at that time use INET_ATON() and select query at that time INET_NTOA() use. how to use this function given bellow example. <strong>Create Table : </strong> <pre class="language-mysql"> CREATE TABLE IF NOT EXISTS `ip_addresses` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ip_address` INT(4) UNSIGNED NOT NULL, PRIMARY KEY (`id`) ); </pre> <strong>Insert Da.....

By Hardik Savani | January 7, 2016 | | 798 Viewer | Category : MySql
READ MORE

Mysql Hexadecimal color code store in binary datatype.

If you have thousands of record store, that time database size would be large. but you can some field can save your number of bites in the database. you have color field then most of you choose string(6) data type and you wast 6 bites on every record, but you can save number of bites data by using BINARY(3) data type. following example through you can set data type for hexa color code with only allocate binary(3) on every records. first how to create table and assign data type with following query. <pre class="language-mysql"> CREATE TABLE IF NOT EXISTS `colors` ( `id` int(10) unsigned NOT N.....

By Hardik Savani | January 7, 2016 | | 1120 Viewer | Category : MySql
READ MORE

    Laravel 5 - Implement Flash Messages with example

    Laravel 5 - Implement Flash Messages with example

    Open Post

    Jquery - Show hide password in Password textbox using checkbox.

    Jquery - Show hide password in Password textbox using checkbox.

    Open Post

    Solved - Access Not Configured. Google+ API (truncated...) on google console developer

    Solved - Access Not Configured. Google+ API (truncated...) on google console developer

    Open Post

    Angularjs nl2br(textarea newline and linebreak conversion) example with demo

    Angularjs nl2br(textarea newline and linebreak conversion) example with demo

    Open Post

    Jquery - input tags plugin example using jquery.tagsinput.js plugin

    Jquery - input tags plugin example using jquery.tagsinput.js plugin

    Open Post

    How to use login throttle in laravel 5?

    How to use login throttle in laravel 5?

    Open Post