How to delete duplicates row in a table and migrate data one table to another table?

Duplicates Row Detection :
Suppose there is a table called “EmployeeTable” which have some duplicate records. There is a three way to delete the duplicate rows.

First way to delete duplicate rows :

Select distinct * into Emp_Temp_Table from EmployeeTable

In the above line we are inserting all the distinct row of the “EmployeeTable” to another table “Emp_Temp_Table” (Emp_Temp_Table will create automatically when you use the above query.)

Actuall the above query create clone of EmployeeTable and insert all the distinct row inside the Clone Table (Emp_Temp_Table).

drop table EmployeeTable
sp_rename ‘Emp_Temp_Table’,EmployeeTable’

Then Delete the orginal table and rename the clone table with the name of orginal table.

Second way to delete duplicate rows :

Select distinct * into Emp_Temp_Table from EmployeeTable

Truncate table EmployeeTable

insert into EmployeeTable select * from Emp_Temp_Table

drop table Emp_Temp_Table

Third way to delete duplicate rows :

Populate the new Primary Key
Alter table EmployeeTable add NewPK int NULL
Go
Declare @intCounter int
Set @intCounter = 0
Update EmployeeTable
SET @intCounter = NewPK = @intCounter + 1
Select name,RecCount=count(*), PktoKeep = max(NewPK)
Into #dupes
From EmployeeTable
Group by name
Having count(*) > 1
Order by count(*) desc

Delete dupes except one Primary key for each dup record

Delete test
from EmployeeTable a join #dupes d
a.name
where a.NewPK not in (select PKtoKeep from #dupes)
Remove the NewPK column
ALTER TABLE test DROP COLUMN NewPK
go
drop table #dupes

Only Selected Duplicate Row

Duplicate row select only …………………………………………
SELECT name, roll, count( roll) AS sroll FROM student
GROUP BY name HAVING sroll >1

Data Migration One table to another table

insert into student_details(sid , sname, dept_id) select s.roll , s.name ,s.dept_id from student s, departments d where s.dept_id = d.id

Or

Insert into student_details(sid, sname, dept_id) select s.roll, s.name, s.dept_id from student s inner join department d oN s.dept_id = d.id

Advertisements

What are the 3 main design principles of object oriented programming?

Object oriented programming has 3 main design principles as follows

1.inheritance

– offers to derive a new class from an existing one and acquire all the feature of the existing class. The new class which get the feature from the existing class is called the derived class and other class is called the base class.

2. Encapsulation

-this allows the user to hide the information for outside world and doesn’t allow the other user to change or modify the internal values of class.

For example :
if you’re building a banking application that handles details of customer accounts , you might have a Account object with a property called totatBalance and methods called makeDeposit and makeWithdrawal.

The totalBlance property should be read-only. The only way to affect the balance is to make a withdrawal or a deposit.

If the totalbalance property is implement as a public member variable, you can write code that would increase the value of that variable without having to actually make a deposit. Obviously, this would be bad for the bank.

Instead, you implement this property as a private member variable and provide a public method called getTotalBalance,
which return the value of that private member variable. With the variable storing the value of the account balance made made private, it can’t be manipulated directly. Because the only public methods that affect the account balance are makeWithdrawal and makeDeposit , a user has to acually make a deposit if he/she wants to increase the value of this account.

Encapsulation of interanl data and method implementations allow an object-oriented software system to protect and control access to data and to hide the details of implementation, giving you flexible, stable appliaction.

3.Polymorphism

– one term in many forms

I will write this topic as soon as possible.

How to Restore MySQL database Using php script?

This is the php script which help you to restore a dump of data into your database …


<?php
ini_set('memory_limit','128M'); // set memory limit here
$db = mysql_connect ( 'Your Host', 'Your Username', 'Your password' ) or die('not connected');
mysql_select_db( 'Your database', $db) or die('Not found');
$fp = fopen ( 'sql_dump.sql', 'r' );
$fetchData = fread ( $FP, filesize ( 'sql_dump.sql') );
$sqlInfo = explode ( ";\n", $fetchData); // explode dump sql as a array data

foreach ($sqlInfo AS $sqlData )
{
mysql_query ( $sqlData ) or die('Query not executed');
}

echo 'Done';
?>

Hope this will help you

Create subdomain dynamically in server using php and cPanel.

Create subdomain code in php

Download Source Code

The file contains a php functions for creating a subdomain and deleting a sub domain dynamically

Click here to download subdomain creation php code from GIT HUB

Here is the function to create a subdomain in php.

  • First parameter is the subdomain name. Like if you want arisha.example.com then you should provide the word arisha.
  • Second parameter is the user name of your web hosting control panel username.
    This is the login where you manage your sites files, ftp, database, etc,.
  • Third parameter is the password of your control panel.
  • Fourth parameter is the name of your original or root domain. for example if your domain name is example.com then you have to provide this here without the subdomain name.


function create_subdomain($subDomain,$cPanelUser,$cPanelPass,$rootDomain) {

// $buildRequest = "/frontend/x3/subdomain/doadddomain.html?rootdomain=" . $rootDomain . "&domain=" . $subDomain;

$buildRequest = "/frontend/x3/subdomain/doadddomain.html?rootdomain=" . $rootDomain . "&domain=" . $subDomain . "&dir=public_html/subdomains/" . $subDomain;

$openSocket = fsockopen('localhost',2082);
if(!$openSocket) {
return "Socket error";
exit();
}

$authString = $cPanelUser . ":" . $cPanelPass;
$authPass = base64_encode($authString);
$buildHeaders = "GET " . $buildRequest ."\r\n";
$buildHeaders .= "HTTP/1.0\r\n";
$buildHeaders .= "Host:localhost\r\n";
$buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
$buildHeaders .= "\r\n";

fputs($openSocket, $buildHeaders);
while(!feof($openSocket)) {
fgets($openSocket,128);
}
fclose($openSocket);

$newDomain = "http://" . $subDomain . "." . $rootDomain . "/";

// return "Created subdomain $newDomain";

}

In the above code you can see that i have specified a folder name in public_html where all the subdomains will be created.
. $subDomain . “&dir=public_html/” . so all subdomains will be created inside the folder subdomains.
You can change this name according to yoru preference.

Delete Subdomain Code in php

function delete_subdomain($subDomain,$cPanelUser,$cPanelPass,$rootDomain)
{
$buildRequest = "/frontend/x3/subdomain/dodeldomain.html?domain=" . $subDomain . "_" . $rootDomain;

$openSocket = fsockopen('localhost',2082);
if(!$openSocket) {
return "Socket error";
exit();
}

$authString = $cPanelUser . ":" . $cPanelPass;
$authPass = base64_encode($authString);
$buildHeaders = "GET " . $buildRequest ."\r\n";
$buildHeaders .= "HTTP/1.0\r\n";
$buildHeaders .= "Host:localhost\r\n";
$buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
$buildHeaders .= "\r\n";

fputs($openSocket, $buildHeaders);
while(!feof($openSocket)) {
fgets($openSocket,128);
}
fclose($openSocket);

$passToShell = "rm -rf /home/" . $cPanelUser . "/public_html/subdomains/" . $subDomain;
system($passToShell);
}

Download Source Code

The file contains a php functions for creating a subdomain and deleting a sub domain dynamically

Click here to download subdomain creation php code from GIT HUB

That’s all. Enjoy.

What is PHP?

What is PHP?

PHP is a widely-used general-purpose scripting language that is especially suited for Web development and can be embedded into HTML. If you are new to PHP and want to get some idea of how it works, try the introductory tutorial. After that, check out the online manual, and the example archive sites and some of the other resources available in the links section.

Ever wondered how popular PHP is? See the
Netcraft Survey.

How to call MySQL stored function, stored procedure from PHP

I think, How to use stored function and stored procedure with MySQL like SQL Server and Oracle. So, I have some R & D this topics then I have decided write a basic(stored function and stored procedure) WP article as follows

Here, MySQL database table raw structure

CREATE TABLE IF NOT EXISTS `tbl_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aa` int(11)  NOT NULL,
  `bb` int(11)  NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

Here, MySQL stored procedure raw structure for “tbl_test” Table

// Storeprocedure ------------------START HERE
	DELIMITER $$

	DROP PROCEDURE IF EXISTS `tbl_test`.`spInsert`$$
  
    // create "spInsert" stored procedure with list of paramaters

        DEFINER is mandatory. if you use it's means set a user for this stored procedure 
	CREATE DEFINER=`root`@`localhost` PROCEDURE `spInsert`(in a int, in b int, out ret int)

       // Not use DEFINER means it's use any user 
	CREATE  PROCEDURE `spInsert`(in a int, in b int, out ret int)

	BEGIN
		
		 declare exit handler for not found rollback;
		 declare exit handler for sqlwarning rollback;
		 declare exit handler for sqlexception rollback;  
		 
		 set ret=0;
		 
		 START TRANSACTION; 
		 
		   insert into tbl_test (aa, bb) values(a,b);   
		   insert into tbl_test (aa, bb) values(a,b);   
	 
	   
		COMMIT; 
		set ret=1;
		
	END$$

	DELIMITER ;
    // Stored procedure ------------------END HERE 

Here, How to call stored procedure from PHP

What are the differences between MyISAM and InnoDB of MySQL engines?

I have faced a interview, This viva board team asked me, what’s main differences between MyISAM and InnoDB of MySQL engine?. I know this but forgot it this moment. So, I think , I will be write a article. Differences between as follows

InnoDB

InnoDB uses row level locking, has commit, rollback, and crash-recovery capabilities to protect user data. 

MyISAM

  MyISAM uses table level locking  to protect user data. 

Limitations of MYISAM and InnoDB as follows

MyISAM limitations

•	No Foriegn keys and cascading deletes and updates
•	No rollback abilities
•	No transactional integrity (ACID compliance)
•	Row limit of 4,284,867,296 rows
•	Maximum of 64 indexes per row

InnoDB Limitations

•	No full text indexing
•       Cannot be compressed for fast, read-only

MyISAM uses table level locking and this can be a great problem if your database INSERT/DELETE/UPDATE load is very high. If this is the problem then you should try converting it to INNODB. It manages non transactional tables. It has fast storage and retrieval, as well as full text searching capabilities.

When to use MyISAM?

MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations. If your read to write(insert|update) ratio is less than 15% its better to use MyISAM.

When to use InnoDB?

InnoDB uses row level locking, has commit, rollback, and crash-recovery capabilities to protect user data. It supports transaction and fault tolerance.

How to convert mysql default character set and collation another one like utf8_general_ci by using the MySQL command

I was a project that’s run on live and client want from me this project enhance with the internationalization and localization features, But MySQL database has default collation for latin1 is ‘latin1_swedish_ci’. So I want to change it with character set and collation utf8_general_ci .I solved this by…..

I change the character set and collation of a database by using the following MySQL command:

ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

I change the character set and collation of each table by using the following MySQL command:

ALTER TABLE TableName CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

I believe help this.