Featured
-
How Regression Testing Detects Integral Errors In Business Processes
Humans are forever changing and evolving and so to
by kristina.rigina -
Get Display Banner Advertising Opportunity on FindNerd Platform
“Do you have a product or service that nee
by manoj.rawat -
Android O Released with Top 7 New Features for App Developers
Android was founded by Andy Rubin, Rich Miner, Nic
by sudhanshu.tripathi -
Top 5 Features That Make Laravel the Best PHP Framework for Development
Laravel is a free open source Web Framework of PHP
by abhishek.tiwari.458 -
Objective C or Swift - Which Technology to Learn for iOS Development?
Swift programming language is completely based on
by siddharth.sindhi
Tags
Pattern Matching using REGEXP
REGEXP:
It is similar to LIKE which is used to fetch data based on regular expression from a table.
Syntax:
SELECT columnname(s) FROM tablename WHERE columnname REGEXP pattern;
Example 1:
SELECT name FROM Employee WHERE name R...
What is the mysql Constraints?
Welcome to FindNerd,
Today we are going to discuss about MySQL Constraints.
Firstly let know what is Constraints in MySQL ?
Constraints:- Constraints are the utilities that restrict ingression of data into the table if the designated co...
SQL Server Management Data Warehouse (MDW)
Management Data warehouse is a database containing a warehouse of data useful for managing SQL Server. Ensuring the excellent and high performance for all the users and customers is a high priority for Database Administrators. Troubleshooting SQL...
Compression and Decompression in SQL Server 2016
In the upcoming version of SQL 2016 a new component of COMPRESS and DECOMPRESS T-SQL functions is added. SQL Server 2016 provides built in functions for compression and decompression.The COMPRESS and DECOMPRESS functions can store and retrieve da...
Use of Transaction in DBMS
A transaction is any unit of work done against the database.A transaction can be used to save the changes made to the database, it can be used to rollback the database to any save point.
For example say if you are creating or updating a record...
How to get the colomn name of table using MySql
Hello Reader's, If you have to get the only names of columns of table then see the code below:-
You can use following query for MYSQL:-
SHOW columns FROM your-table;
Below is the example code which shows How to implement above syntax in ph...
How to reset the Auto increment in mysql
Hello Reader's! If you already using the Mysql then you have seen the ID with autoincrement always add up. Forevery new entery the ID will increase even if you drop the table.
So if you want to reset this value you just have to run the mysql cod...
How to use IN statement using array in Mysql
Hello reader's! If you have an array with multiple values to match with database coloumn. Then you can use IN statement in mysql but you have to explode the array first.
let's see the exampkle below:-
$findIN= implode(',', $YourArray); // fir...
Syntax error due to reserved names in Mysql
Hello Reader's!, On using Mysql with the some special resereved keywords the error :-
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near <Keyword>
It...
Use of '@' in PHP
Hello Reader!, You might have seen people using the '@' in the PHP coding. The operator '@' is used when you don't want to show the error messages even if they occur.
By using '@' the errors will be hidden.
Let's see the two syntax below:-
...
Using MySQL with node.js
Hello Everyone , In this blog we will discuss about how to use MYSQL in node.js.
node-mysql is one of the best modules which can be used for working with MySQL database.
To install the MySQL in node.js driver you have to follow up with the be...
How to insert if not exists in Mysql
If you want to enter a new records that is not exits then you can use Mysql two methods
Method 1 'REPLACE' :-
REPLACE INTO `users`
SET `userid` = 65,
`username` = 'abc',
`useremail` = 'abc@gmail.com';
In this case if record exists...
How to import mysql database using command line?
Sometimes when you have large databases files and you are unable to import the database manually. Then the easiest (and fastest) way is to use command line.
Import:--
1. Run the cmd (DOS) and get into the mysql folder, which in my case wo...
How to delete duplicate rows in a table set MySql
Hello readers!. If you having the much of redundant/duplicate data growing in your database you can delete it with just a single query as I wrote here.
Let say I have a table Address and want to delete the all the duplicate records whose city...
How to add a column with comment in MySql?
Sometimes we need to add a column with comment. We can do this easily by using COMMENT attribute in alter command.
Ex- Find the below alter command which will add "address" column to "user" table after "country" column.
ALTER TABLE `user` A...
How to calculate distance from Latitude and longitude
Hello readers!, If you are having a project in PHP and need to calculate the distance from lat and longs you can use the code below:-
Here all the entries are saved in table('business')
(SELECT name, ACOS(SIN(RADIANS($latitude))*SIN(RADIANS...
Control Flow Functions in MySQL
Mysql have several contral flow functions. Such as
1) IF
2) CASE
3) IFNULL
4) NULLIF
Control Flow functions returns value based on processed each row by query executed.
Contral Flow function can be used on **SELECT, WHERE, GROUP BY** a...
Stored Procedures and Functions in MySQL- Part 5
Conditional Constructs - IF...ELSEIF..ELSE
This construct is used to evaluate some complex conditions and based on the results execute different set of statements.
The basic syntax is
IF <conditions> THEN
&n...
Indexed full text search in PHP-MySQL (PART - 1)
We need to write search queries every now and then in our projects. This is one of the most common tasks a PHP developer has to do. Most of the developers still use wildcard queries for this purpose which is very slow in searching records. Howeve...
Stored Procedures and Functions in MySQL - Part 3
Parameters in Stored Routines
While executing any stored routine sometimes we need to pass on certain values in stored procedures these values are handled as Parameters. Their declaration consists of three parts :
Type of parameter
Par...
Create Update, Delete and Insert Functions
Hello,
Here I am writing the code to create update, insert and delete functions so that we can use it the same functions anywhere in the project.
<?php
// function to insert data
function insert($table = "", $fieldsArray = array()) {...
Case Sensitive MYSQL select query
Case Sensitive SQL query
A select query does not performs case-sensitive query which means if the user name is pravesh, Pravesh, PRAVESH then it will select all users using simple select. Below example of normal mysql select query -
SELECT...
Crosstab Query Sample
To convert the rows to column in MySQL
Sample query:
select column1, count((case when (column2 = 'M') then 0 end)) AS `males`, count((case when (column2 = 'F') then 0 end)) AS `females`,count(0) AS `Total` from table_name group by ...
How to connect to your MySQL database from a PHP Script
Create simple Mysql connection with the help of PHP code
<?php
$hostname = 'localhost'; //define the hostname here
$username = 'root'; //define the username here
$password = ' '; ...
Working with triggers in mySql Database
A trigger is SQL code which runs just before or just after an
INSERT, UPDATE or DELETE event occurs on a particular database table.
Creating a Trigger:
Consider we have two tables:
CREATE TABLE `blog` (
`id` mediumint(8) unsigned...
How to set default value of column with datatype varchar to empty string
We usually face this kind of issue when we are doing programing with respect to a column with varchar data-type. If we don't add any value to a particular column it takes it as NULL. Thus forcing the developer to check for NULL every-time other t...
Using Putty and SSH Tunnel to connect to MySQL on remote server from SQLYog
To connect to a remote MYSQL server using SQLYog you can create SSH tunnel using putty following the steps.
Connect to the remote server using putty.
.
Right Click on putty window and select the change settings option.
...
Set up remote access to MySQL database on Ubuntu
Without database any application is almost redundant. So this post might help you to setup access to MySQL database on Ubuntu.
Firstly you need to open up the MySQL server config file present at
/etc/mysql/my.conf
and search for the lin...
Find MYSQL server version information
To find the version information of MYSQL server from command line log into MYSQL server using command
mysql -h hostname -uusername -p
You would be prompted for the password for the user on the server type the same and press Enter key.
...
How to Work with a Stored Procedure
How to Work with a Stored Procedure
DELIMITER //
CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
SELECT 'Hello World !';
END//
The four characteristics of a procedure are:...
Importing a MYSQL database dump from command line
While importing databases dump that are of large sizes in GB you may find issues with PHPmyadmin and some other clients also.
To import the database you can use the command.
mysql -uusername -p databasename < sqldump.sql
where userna...
Known bug of Temporary Table in MYSQL
Known Bug
We cannot use temporary table twice in same query.
http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html
However if you have not required above condition then Temporary table is best to use rather than creating rea...
Delete duplicate row in table
You can delete the duplicate row without using the temporary table. You can use the self join concept.
Suppose we have person table contain 3 columns named id, name, and city.
INSERT INTO person(id,name,city) VALUES
(1,'a', 'aa'),
(2,'b','b...
Use of Find in set() function in mysql
In some cases, we use find_in_set() instead of in() in mysql. Following is an example, where we should use find_in_set() instead of in() :-
Suppose we have a table 'flashdata':-
fid productId
1 75,73
2 72...
