How to delete duplicate records from MySQL table | MySQL query to delete duplicate records

Many of the interviews I was asked a common MySQL question and the questions was, Write a MySQL query to delete the duplicated records from MySQL table.
Here in this small article I'll be explaining MySQL query to get duplicate records and to delete the duplicate records.

For example you have a users table and inside the users table we have duplicate email id of user so the first question how to get the duplicate records ?
Here is the MySQL query to get the duplicate records:

It will return duplicate emails along with the count of duplicate emails.



Now we wanted to delete the duplicate records. Here are some scenarios, either you wanted to keep the highest record or you wanted to keep the lowest record. ...  Read More

Share This:


Second Highest Salary without subquery in MySQL | Nth highest Salary without subquery | MySQL second highest salary query

In so many interviews Interviewer will ask you a very simple MySQL query. And the question is :

How to select 2nd,3rd or the Nth highest salary from a table in MySQL ?

If you will answer with sub query like below query:

Next question might be asked like:

How to select 2nd,3rd or the Nth highest salary from a table in MySQL without using sub query ?

To get the second highest salary of an employee here is the MySQL query:

If you wanted to get 3rd highest salary then here is below MySQL query to get the 3rd highest salary:

If you wanted to get nth highest salary then here is below MySQL query to get the nth highest salary:

for example if you wanted to write MySQL query to get 4th highest salary of the employee then the query will be like:

  ...  Read More

Share This:


Left join with last record of right table in Laravel & Mysql

Here we have 2 tables 'users' and 'answers' where users is left table and answers is right table which has user answers.

We wanted to left join users with answers but the join should be with the latest record or answers table.

 

Share This:


if else and case when in Laravel query

Often you need to do some MySQL query operation conditionally. You may need get any column value conditionally. How you will do that?

Here is the example. You can either IF ELSE or CASE WHEN for conditional queries.

Here in this example of MySQL and Laravel, if start_at column value in products table is NULL then we retuning 'started_at' column value else we are returning minimum value of 'start_at' column.

If and Else:

Products::SELECT(DB::raw('IF(MIN(products.start_at) IS NULL, products.started_at, MIN(products.start_at)) as start_at'))->get();

Case When:

Products::SELECT(DB::raw('CASE WHEN COUNT(products.user_id) != 0 THEN COUNT(products.user_id) ELSE 1 END as user_id'))->get();

Share This:


Group by date range on weeks/months interval

Group by week:

select COUNT(id) as total_count, WEEKOFYEAR(created_at) AS yr_week from analytics where is_redeemed = 1 and created_at >= NOW() - INTERVAL 7 WEEK group by yr_week

Group by month:

select COUNT(id) as total_count, MONTH(created_at) AS yr_week from analytics where is_redeemed = 1 and created_at >= NOW() - INTERVAL 3 MONTH group by yr_week

Share This:


Batch update in php mysql

In PHP and MySQL we easily can insert the data in bulk or with a single query but it's always critical to updated the bulk records in MySQL table.

Here I'm going to explain how can we update the records in bulk in PHP and MySQL.

 

Share This:


Batch insert in php mysql

<?php $sql = array(); foreach( $data as $row ) { $sql[] = '("'.mysql_real_escape_string($row['name']).'", '.$row['email'].')'; } mysql_query('INSERT INTO test (name, email) VALUES '.implode(',', $sql)); ?>

Using MySQLIi :

<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "test";// Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); }$sql = "INSERT INTO test (name, email) VALUES ('Saurabh', 'saurabh@coding4developers.com');"; $sql .= "INSERT INTO test (name, email) VALUES ('Gaurav', 'gaurav@coding4developers.com');"; $sql .= "INSERT INTO test (name, email) VALUES ('Sachin', 'sachin@coding4developers.com');";if ($conn->multi_query($sql) === TRUE) { echo "New records created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; }$conn->close(); ?>

Share This:


Prepared statement in php

<?php

// Create connection
$conn = new mysqli('localhost', 'root', '', 'test');

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// prepare and bind
$stmt = $conn->prepare("INSERT INTO test (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

// set parameters and execute
$name = "Saurabh";
$email = "info@coding4developers.com";
$stmt->execute();

$name = "Gauravg";
$email = "query@coding4developers.com";
$stmt->execute(); ...  Read More

Share This:


Why to use prepared statement in php mysql

  • Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.
  • Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query.
  • Prepared statements reduces parsing time as the preparation on the query is done only once (although the statement is executed multiple times).
  •  ...  Read More

    Share This: