Category : MySql

ItSolutionStuff.com have tutorials for MySql, here you can study articles of MySql, MySql posts collection, most popular and useful tutorials of MySql, here you can find list of all relevant posts and example about MySql, we have lists of tutorials and examples about MySql.very simple and quick example collection of Latest MySql category.



Sql function remove numeric characters from column example

Mysql custom fucntion is a very prety and intresting concept. In this posts i want to create one custom mysql function for remove numeric characters from table field value. this function through you can also remove numeric characters from string in mysql. in following sql query fire in your mysql or mssql database and check how it works. So, lets create function and how to use in select statement in sql query. <strong class="step">Create removeNumber function:</strong> <pre class="prettyprint lang-mysql"> DROP FUNCTION IF EXISTS removeNumber; DELIMITER | CREATE FUNCTION removeNumber( str CH.....

By Hardik Savani | March 12, 2016 | | 11670 Viewer | Category : MySql
READ MORE

How to remove special characters by mysql custom function.

Mysql custom fucntion is a very prety and intresting concept. In this posts i want to create one custom mysql function for remove special characters from table field value. this function through you can also remove special characters from string in mysql. in following sql query fire in your mysql or mssql database and check how it works. So, lets create function and how to use in select statement in sql query. <strong class="step">Create removeSpacialChar function:</strong> <pre class="prettyprint lang-mysql"> CREATE FUNCTION `removeSpacialChar`(`in_str` varchar(4096)) RETURNS varchar(4096) C.....

By Hardik Savani | March 10, 2016 | | 22623 Viewer | Category : MySql
READ MORE

How to use where clause with mysql function in Laravel 5?

When you are working on laravel projects and you need to use mysql function in where clause then you can easily use that using DB::raw() and whereRaw(). In this example you can show how to i use mysql function in where clause. In this example i want to compare with year of created_at field but not whole date, that's whay i use mysql function Year(), this function will return only year from timestamp and compare with given value. I add two example of how to use sql function in where cause. let's See both example. <strong class="example">Example 1:</strong> <pre class="prettyprint lang-sql"> $da.....

By Hardik Savani | March 9, 2016 | | 15596 Viewer | Category : MySql
READ MORE

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 | | 18214 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 | | 28237 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 | | 24534 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 | | 12638 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 | | 21463 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 | | 10146 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 | | 16236 Viewer | Category : MySql
READ MORE