Category : MySql


How to get latitude longitude from mysql in laravel 5?

Whenever you need to get nearest posts from lat and long and if you are working on laravel query builder. then you can get distance easily using DB::raw(), so you can learn from following example how to get nearest value from mysql laravel. <strong>Example:</strong> <pre class="prettyprint lang-php"> $lat = 1; $lon = 1; DB::table("posts") ->select("posts.id" ,DB::raw("6371 * acos(cos(radians(" . $lat . ")) * cos(radians(posts.lat)) * cos(radians(posts.lon) - radians(" . $lon . ")) + sin(radians(" .$lat. ")) * sin(radians(posts.lat))) AS distance")) ->groupBy("posts.id") ->ge.....

By Hardik Savani | March 8, 2016 | | 4447 Viewer | Category : MySql
READ MORE

How to add mysql trigger from migrations in Laravel 5?

Sometimes you require to add trigger in your mysql database of laravel 5. but you think how to create trigger using laravel migration because laravel not provide special function like insertTrigger and something so it problem to create direclty, But laravel DB::unprepared() through we can create trigger for database. In Following example you can see how to create migration and create trigger code and how to write drop trigger code, so let's do it this way. <strong class="step">Create Migration:</strong> <pre>php artisan make:migration add_trigger</pre> <strong class="step">Migration:</stron.....

By Hardik Savani | March 8, 2016 | | 7589 Viewer | Category : MySql
READ MORE

Laravel 5 query builder where exists example

you use sql where exists clause in laravel. whereExists through you can use sql where exists clause in your laravel project. It is very easy to use and you can easily undestand. You can give SELECT statment in where condition. you can see bellow example and you can learn how to use whereExists in your app. <strong>SQL Query</strong> <pre class="prettyprint lang-sql"> SELECT * FROM `items` WHERE EXISTS (SELECT `items_city`.`id` FROM `items_city` WHERE items_city.item_id = items.id) </pre> <strong>Using Laravel Query Builder</strong> <pre class="prettyprint lang-php"> DB::table('it.....

By Hardik Savani | March 7, 2016 | | 4218 Viewer | Category : MySql
READ MORE

How to import CSV file using mysql

In this post we have learn how to import CSV file using LOAD DATA INFILE statement. this is a very simple example for a import file to the database. Here we have provide a full sysntax for LOAD DATA INFILE statement. <strong>Sysntax</strong> <pre class="language-mysql"> LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] .....

By Hardik Savani | March 3, 2016 | | 2295 Viewer | Category : MySql
READ MORE

How to fetch this week records in MySql ?

If you require to get all the records of current week using mysql query. for example you have one table "items" and columns are id, title, description, created_at and update_at, Now you want to get all records of this week. so you can fetch fields value using DATE_SUB and INTERVAL of mysql. In following example you can see how to select data of current week. <strong>Example</strong> <pre class="language-mysql">SELECT * FROM items WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 WEEK);</pre>

By Hardik Savani | February 28, 2016 | | 3140 Viewer | Category : MySql
READ MORE

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 | | 911 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 | | 1927 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 | | 6381 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 | | 849 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 | | 1362 Viewer | Category : MySql
READ MORE

    Laravel 5 Autocomplete using Bootstrap Typeahead JS Example with Demo

    Laravel 5 Autocomplete using Bootstrap Typeahead JS Example with Demo

    Open Post

    Laravel 5.3 - image dimension validation rules example

    Laravel 5.3 - image dimension validation rules example

    Open Post

    Laravel 5 create and download zip file example using chumper/zipper composer package

    Laravel 5 create and download zip file example using chumper/zipper composer package

    Open Post

    AngularJS - update bind ng-model input value from Jquery Code

    AngularJS - update bind ng-model input value from Jquery Code

    Open Post

    How to get Instagram followers count using JQuery Ajax?

    How to get Instagram followers count using JQuery Ajax?

    Open Post

    Simple PHP Jquery Ajax CRUD(insert update delete) tutorial example with source code

    Simple PHP Jquery Ajax CRUD(insert update delete) tutorial example with source code

    Open Post