PHP Tutorials

Important Link

Home » sql » Mysql or Mysqli

mysql command?

How to open mysql command prompt

start xampp server

C:\xampp\mysql\bin>mysql -u root -p
enter password :

How to use source command in mysql?

open cmd
C:\xampp\mysql\bin>mysql -u root -p
enter password : press enter (password blank)
MariaDB [(none)]> show database;
MariaDB [(none)]> use testdb;
MariaDB [testdb]> source C:\Users\Its\Downloads\test.sql

How to import database on local xampp server using command?

step 1 : download old database such as itechtuto.sql
setp 2 : paste it at this path mysql bin directory
c:\>xampp\mysql\bin
step 3 : open cmd
c:\>cd xampp\mysql\bin
c:\>cd xampp\mysql\bin> mysql -u root -p database_name < itechtuto.sql

download database from mysql to local system

mysqldump -u root -p database_name table_name > /desktop/mahtab/table_name_backup.sql

upload Local system to mysql server

mysql -u root -p database_name < /desktop/mahtab/table_name_backup.sql

mysql -u root -p database_name table_name < /desktop/mahtab/ittutorial.sql

mysql -u root -p

// here root is the user name of the mysql

Apache - MySQL Service detected with wrong path. / Ports already in use

Go to cmd and run it with Administrator mode.

Uninstall mysql service through command prompt using the following command.
sc delete mysql

restart XAMPP

create database

create database itechtuto

show database

show databases;

use db

use [db name];
use itechtuto;

show tables

show tables;

Describe tables

describe [table name];

desc Table_name;

How to alter COLLATE option in table

ALTER TABLE itechxpert CHANGE title `title` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

ALTER TABLE ittutorial CHANGE 'title_hindi' 'title_hindi' VARCHAR(5000) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL;

// it convert all column in unicode
Alter table ittutorial CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Add this before db query insert and fetch.

mysqli_set_charset( $con, 'utf8');

// Change character set to utf8
mysqli_set_charset($con,"utf8");

How to Insert and show hindi text :-

//setting character set
mysql_set_charset('utf8');
//insert Hindi text
mysql_query("INSERT INTO table_name");

To retrieve data

//setting character set
mysql_set_charset('utf8');
//select Hindi text
mysql_query("SELECT * FROM table_name");

showing unicode text (Hindi text) on browser, you should have to set content type of that page by adding a meta tag

How to Delete Database

mysql> drop database db_name;

How to backup of data base?

mysqldump -u funtoosapp -p db_name > /tmp/mahtab/db_name_copy_backup.sql

How to Backup database with stored procedures and functions?

We need to specify --routines to take backup of stored procedures with data and tables.

mysqldump -u funtoosapp -p --routines db_name > /tmp/mahtab/db_name_copy_backup.sql

mysqldump -u username -p -R db_name > dump_backup.sql

To take backup of only Stored Procedures and Triggers (Exclude table and data ) use the following command.

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt mydb > mydb.sql

How to Restore backup of data base from local to mysql?

mysql -u itechxpert -p db_name < /tmp/mahtab/db_name_backup.sql

How to backup of data base Table?

mysqldump -u itechxpert -p db_name db_table > /tmp/mahtab/table_name_backup_copy.sql

how to backup of database table

mysqldump db_name table_name > table_name.sql

Dumping from a remote database
mysqldump -u -h -p db_name table_name > table_name.sql

drop or delete database

drop database [database name];

drop database mydb;

How to create autoincrement Table with primary key.

CREATE TABLE employee_details (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
Name varchar(64) NOT NULL,
Address text NOT NULL,
email varchar(64) DEFAULT NULL,
mobile bigint(10) NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
gender varchar(6) NOT NULL,
dob datetime NOT NULL,
status tinyint(2) NOT NULL DEFAULT 1,
status enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
created_at datetime NOT NULL,
date_added datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
created_by varchar(32) DEFAULT NULL,
updated_at timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
updated_by varchar(32) DEFAULT NULL,
uid_status enum('0','1') NOT NULL
)

How to create autoincrement Table with primary key.

CREATE TABLE IF NOT EXISTS `articles` (
`article_id` int(11) NOT NULL AUTO_INCREMENT,
`article_cat_id` int(11) NOT NULL,
`article_name` varchar(255) NOT NULL,
`article_slug` varchar(255) NOT NULL,
`article_body` text NOT NULL,
`img_link` varchar(255) DEFAULT NULL,
`image` varchar(255) COLLATE utf8_unicode_ci NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
`price` float(10,2) NOT NULL, `article_state` tinyint(1) NOT NULL DEFAULT '0',
status enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
`article_views` int(11) NOT NULL,
`article_created` int(11) NOT NULL,
date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
PRIMARY KEY (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

How to create Foreign key in table.

CREATE TABLE student (
ID int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (ID),
mobileNo bigint NOT NULL,
empID int,
FOREIGN KEY (empID) REFERENCES itechxpert(ID)
);

How to Delete Foreign key constraint.

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE CANDIDATE;
TRUNCATE TABLE ITECHXPERT;
SET FOREIGN_KEY_CHECKS = 1;

create table.

CREATE TABLE `order_details` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) DEFAULT NULL,
`item` varchar(245) DEFAULT NULL,
`image` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `quantity` int(11) DEFAULT '0',
`price` decimal(10,2) DEFAULT '0.00',
`price` float(10,2) NOT NULL, `sub_total` decimal(10,2) DEFAULT '0.00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT = 1;

create table.

CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` timestamp NULL DEFAULT NULL,
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
`customer_name` varchar(245) DEFAULT NULL,
`customer_address` varchar(245) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT = 1;

create table.

CREATE TABLE `mobile_numbers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mobile_number` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`verification_code` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`verified` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1=Verified, 0=Not verified',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

How to create autoincrement Table WITH Foreign key.

CREATE TABLE file_uploaded (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
fk_id int,
FOREIGN KEY (fk_id) REFERENCES itech(id),
doc_upload_file varchar(128),
status tinyint(4) NOT NULL,
status enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
created_at datetime NOT NULL,
date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
created_by varchar(32));

How to add Foreign key in existing table.

ALTER TABLE itechxpert ADD FOREIGN KEY (UID) REFERENCES itechtuto(ID);

// ID is the primay key of the itechtuto table. and UID is the foreign key of the itechxpert table.

ALTER TABLE `city` ADD FOREIGN KEY (`state_id`) REFERENCES `state`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

How to create table with unique column

CREATE TABLE itechxpert (
ID int NOT NULL,
FirstName varchar(255),
LastName varchar(255) NOT NULL,
Age int,
UNIQUE (ID)
);

How to define a UNIQUE constraint on multiple columns

CREATE TABLE itechxpert (
ID int NOT NULL,
First_Name varchar(255) NOT NULL,
Last_Name varchar(255) NOT NULL,
Age int(11),
CONSTRAINT UC_itechxpert UNIQUE (ID,Last_Name)
);

Alter table

ALTER TABLE `subscribers` ADD PRIMARY KEY (`id`);
ALTER TABLE `subscribers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `candidate` CHANGE COLUMN `dob` `dob` DATE NOT NULL AFTER `name`, ADD COLUMN `age` VARCHAR(50) NULL DEFAULT NULL AFTER `dob`;

How to add Unique key in existing table.

ALTER TABLE itechxpert ADD UNIQUE (email); // for single column.

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_list);

ALTER TABLE itechxpert ADD CONSTRAINT uc_id_email UNIQUE(id,email); // for Multiple column.

Add Column in table

ALTER TABLE ittutorial ADD COLUMN salary INT(11) NULL DEFAULT NULL AFTER title;
ALTER TABLE ittutorial
ADD COLUMN `author` varchar(32) NULL DEFAULT NULL AFTER `salary`,
ADD COLUMN `publication` varchar(32) NULL DEFAULT NULL AFTER `author`;

insert multiple record in table

INSERT INTO ittutorial.in (name, address, status)
VALUES('mahi','ara','1'),('sana','Patna','0'),('Mahira','delhi','1');

what is difference between MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

INSERT INTO itechxpert (id, name, address) VALUES (1,'mahtab','delhi')

$sql = "INSERT INTO ittutorial (Roll_no, name, district_name, state_name)
VALUES('$row->id', '$row->name', '$row->district', '$rwo->state')";

INSERT INTO itechxpert SET id=1, name='mahtab', address='delhi'

both syntaxes are equivalent. The first is SQL standard, the second is MySQL's extension. So they should be exactly equivalent performance wise.

$sql = "INSERT INTO itechxpert SET name='".$row[name]."', roll='".$row['roll_no']."', age='".$row[age]."', current_status='".$row[status]."'";

$sql = "INSERT INTO itechxpert SET name='".$row->name."', roll='".$row->roll_no."', age='".$row->age."', status='".$row->status."'";

drop table

drop table [table name];

drop table employee;

Alter Table :

ALTER TABLE itechtuto ADD id int NOT NULL AUTO_INCREMENT primary key first;

Alter Table Column.

// First you have to remove the primary key from the table

ALTER TABLE itechxpert DROP PRIMARY KEY;

// and now yo can add the autoincrement column

ALTER TABLE itechxpert ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE itechxpert MODIFY id int NOT NULL AUTO_INCREMENT primary key;

How to Alter table column in mysql?

// modify the data type of date_of_joining column datatime to date

Alter Table itechxpert Modify column date_of_joining date;

ALTER TABLE `companies` ADD `banner_title` TEXT NULL DEFAULT NULL AFTER `fk_menu_id`, ADD `banner_description` TEXT NULL DEFAULT NULL AFTER `banner_title`;

ALTER TABLE `companies` ADD `doc_title` VARCHAR(255) NULL DEFAULT NULL AFTER `content`;

How to Add a new column to Table.

alter table [table name] add column [new column name] varchar (20);

ALTER TABLE itechxpert ADD user_id int(11) NULL AFTER user_name

ALTER TABLE itechxpert ADD user_id int(11) NULL FIRST user_name

ALTER TABLE iteh ADD status tinyint(4) NOT NULL AFTER updated_on;

Add a new column to Table.

ALTER TABLE itechxpert add received_date datetime NOT NULL after updated_at;

ALTER TABLE users ADD updated_on DATETIME AFTER created_on;

ALTER TABLE Itechxpert ADD COLUMN updated_date DATETIME NOT NULL DEFAULT '2011-01-26 14:30:00' AFTER created_date

How to set Default value in column in table?

ALTER TABLE Persons ALTER City SET DEFAULT 'Sandnes';

ALTER TABLE Persons ALTER status SET DEFAULT 1;

Select Data from table

SELECT * FROM [table name];

Show Column of tables

show columns from [table name];

SELECT * FROM [table name] WHERE [field name] = "whatever";

SELECT * FROM [table name] WHERE name = "Boby" AND phone_number = '12345';

SELECT * FROM [table name] WHERE name != "Boby" AND mobile = '1234567890' order by mobile;

Show all records starting with the letters 'mah' AND the phone number '9931444'.

SELECT * FROM [table name] WHERE name like "mah%" AND phone_number = '9931444';

Like

SELECT * FROM `tutorial` WHERE name LIKE '%mahi%';

Show unique records.

SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Count rows.

SELECT COUNT(*) FROM [table name];

Delete a column.

alter table [table name] drop column [column name];
Alter table 'emp' drop column salary;

Change column name.

alter table [table name] change [old column name] [new column name] varchar (50);
Alter table 'emp' change salary salary2 varchar(45);

ALTER TABLE itechxpert CHANGE `emp_name` `Employee_name` varchar(64);

Alter table 'emp' modify column salary int(10);

Updating Selected Records

UPDATE Information SET name = 'mahi' WHERE id = 52;
Update ittutorial set name='".$ittutorial."' WHERE ID =".$id.";

Counting the Number of Rows

SELECT COUNT (*) FROM Information
SELECT COUNT (*) as total FROM tutorial

How to truncate table

TRUNCATE table itechxpert;

How to quit or exti from mysql.

mysql> quit

mysql> exit;

How to delete record

delete from itechxpert
delete from itec where id='1234';

update multiple row in single column

In some cases we need to select and modify the record based on specific conditions. So instead of using cursor or looping, we can use case CASE expression. CASE statement works like IF-THEN-ELSE statement.

UPDATE itechxpert
SET LASTNAME = CASE
                     WHEN LASTNAME = 'alam' THEN 'habib'
                     WHEN LASTNAME = 'bano' THEN 'mahtab'
                     WHEN LASTNAME = 'parveen' THEN 'kalam'
                     ELSE LASTNAME
               END
WHERE LASTNAME IN ('alam', 'bano', 'parveen')

#################################

UPDATE Employee
SET State_Code = CASE State_Code
WHEN 'MH' THEN 'Maharastra'
WHEN 'MP' THEN 'Madhya Pradesh'
ELSE NULL
END

How to update multiple row in single column.

UPDATE itechxpert
SET code_no = CASE id
WHEN '1' THEN '0'
WHEN '2' THEN '0'
WHEN '3' THEN '0'
WHEN '4' THEN '0'
WHEN '5' THEN '68'
WHEN '6' THEN '0'
WHEN '7' THEN '67'
WHEN '8' THEN '67'
WHEN '9' THEN '67'
WHEN '10' THEN '0'
WHEN '11' THEN '0'
WHEN '12' THEN '68'
WHEN '13' THEN '68'
WHEN '14' THEN '68'
WHEN '15' THEN '68'
WHEN '16' THEN '66'
WHEN '17' THEN '66'
WHEN '18' THEN '66'
WHEN '19' THEN '66'
WHEN '20' THEN '66'
WHEN '21' THEN '66'
WHEN '22' THEN '67'
WHEN '23' THEN '67'
WHEN '24' THEN '65'
WHEN '25' THEN '0'
WHEN '26' THEN '69'
WHEN '27' THEN '69'
WHEN '28' THEN '0'
WHEN '29' THEN '69'
WHEN '30' THEN '69'
WHEN '31' THEN '65'
WHEN '32' THEN '65'
WHEN '33' THEN '65'
WHEN '34' THEN '0'
WHEN '35' THEN '68'
WHEN '36' THEN '66'
WHEN '37' THEN '69'
WHEN '38' THEN '69'
WHEN '39' THEN '0'
WHEN '40' THEN '65'
WHEN '41' THEN '66'
WHEN '42' THEN '65'
WHEN '43' THEN '67'
WHEN '44' THEN '0'
WHEN '45' THEN '0'
WHEN '46' THEN '0'
WHEN '47' THEN '0'
WHEN '48' THEN '0'
WHEN '49' THEN '0'
WHEN '50' THEN '0'
WHEN '51' THEN '0'
WHEN '52' THEN '0'
WHEN '53' THEN '0'
WHEN '54' THEN '0'
WHEN '55' THEN '0'

END
WHERE id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55);
//here

In order to make multiple updates, you can use a CASE block in SQL combined with an appropriate WHERE clause to select the appropriate rows and set the different values. For example, in order to update the column `Country` based on column `ID` alone:

UPDATE `membership_list` SET `Country` = CASE `ID` WHEN '1' THEN 'Antarctica' WHEN '3' THEN 'Canada' END WHERE `ID` IN (1,3);

Example 2 - multiple rows, multiple columns:

In order to change more than one column, more than one CASE blocks can be used. For example, in order to both the columns `Country` and `One_or_zero` based on `ID` alone:

UPDATE `membership_list`
SET `Country` = CASE `ID`
WHEN '1' THEN 'Antarctica'
WHEN '3' THEN 'Canada'
END,
`One_or_zero` = CASE `ID`
WHEN '1' THEN '1'
WHEN '3' THEN '1'
END
WHERE `ID` IN (1,3);

multiple rows, multiple WHERE conditions:

Now assuming that you only want to alter all rows in `membership_list` that have '205G0' as the `S_ID` and 'Bob' as the `Name`, and we want to set all `Country` to unique values based on `One_or_zero`:

UPDATE `membership_list`
SET `Country` = CASE `One_or_zero`
WHEN '1' THEN 'Switzerland'
WHEN '0' THEN 'Russia'
END
WHERE `One_or_zero` in (0,1)
AND `S_ID`='205G0'
AND `Name`='Bob';

UPDATE table_users
SET cod_user = (case when user_role = 'student' then '622057'
when user_role = 'assistant' then '2913659'
when user_role = 'admin' then '6160230'
end),
date = '12082014'
WHERE user_role in ('student', 'assistant', 'admin') AND
cod_office = '17389551';

Country, State & City table.

CREATE TABLE country (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
country_name varchar(128),
created_on datetime NOT NULL);

CREATE TABLE state (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
state_name varchar(128),
country_id int,
FOREIGN KEY (country_id) REFERENCES country(id)
);

CREATE TABLE city (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
city_name varchar(128),
state_id int,
FOREIGN KEY (state_id) REFERENCES state(id)
);

Creating Dump of Your Database and Optimizing the Table

mysqldump –h localhost –u username –ppassword databasename > backup_file.sql

Optimizing a Table

Optimize table TABLE_NAME

Create index at time of creating table

CREATE TABLE 'emp' ( Name varchar(45), 'id' INT, INDEX(ID) )

Create Index for an existing table

Create Index id_index on emp(ID);

Output :-

How to get privious month data

SELECT * FROM table_name WHERE date_created > (NOW() - INTERVAL 1 MONTH);

SELECT * FROM table WHERE date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();

How to count no of menu and submenu

SELECT int_pid, COUNT(int_pid) FROM menu_master GROUP BY int_pid HAVING COUNT(int_pid) > 1;

How to Join Two table from two databases

SELECT * FROM ittutorial AS it
JOIN itechxpert.itech_users as itech ON it.email= itech.email
WHERE it.status=1;

SELECT it.name, it.email FROM ittutorial AS it
JOIN itechxpert.itech_users as itech ON it.email= itech.email
WHERE it.status=1;

SELECT it.*, itech.* FROM ittutorial AS it
JOIN itechxpert.itech_users as itech ON it.email= itech.email
WHERE it.status=1;

How to Join three table

SELECT cd.*, edu.*, gt.* FROM candidate AS cd
LEFT JOIN educational AS edu ON (edu.fk_applicant_id = cd.pki_id)
LEFT JOIN gate AS gt ON (gt.fk_applicant_id = edu.fk_applicant_id)
WHERE cd.i_status='1';

How to Join three table and remove duplicat record from edu table.

SELECT cd.*, edu.*, gt.* FROM candidate AS cd
LEFT JOIN educational AS edu ON (edu.fk_id = cd.pki_id)
LEFT JOIN gate AS gt ON (gt.fk_id = cd.pki_id)
WHERE cd.status='1'
GROUP BY edu.fk_id;

Group by :- it remove duplicate row from educational table.

How to Count duplicate value in table.?

public function get_report($category,$discipline)
{

$where='1=1';
if(!empty($category)){

if($category == 'All'){
$where='cd.i_status=1';
}
else{
$where='cd.category="'.$category.'" and cd.i_status=1';
}
}
if(!empty($discipline)){

if($discipline == 'All'){
$where='cd.i_status=1';
}
else{

$where.=' AND cd.discipline="'.$discipline.'" and cd.i_status=1 ';
}
}

$this->db->select('cd.*, edu.*, gt.*, cd.i_status as can_status');
$this->db->from('applicant as cd');
$this->db->join('edutable as edu','edu.fk_applicant_id=cd.pki_id','left');
$this->db->join('gatetable as gt','gt.fk_applicant_id=cd.pki_id','left');
$this->db->where($where);
$this->db->group_by('edu.fk_applicant_id');
//$this->db->where('cd.i_status','1');
//$this->db->where('cd.category', $category);
//$this->db->where('cd.discipline', $discipline);
$query=$this->db->get();
//echo $this->db->last_query();
//$response = $query->result_array();
return $result = $query->result();

}

How to Count duplicate value in table.?

SELECT email, COUNT(email) FROM candidate GROUP BY email HAVING COUNT(email) > 1

SELECT mobile, COUNT(mobile) FROM candidate GROUP BY mobile HAVING COUNT(mobile) > 1

SELECT DISTINCT * FROM customers WHERE fk_id IN (SELECT pk_id FROM candidate WHERE status='1');

Count duplicate row in table

SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) > 1

SELECT name,email, COUNT(*) AS CountOf FROM itechxpert GROUP BY name,email HAVING COUNT(*)>1

Count duplicate row in table

declare itechxpert table (id int, name varchar(10), email varchar(50))

INSERT itechxpert VALUES (1,'mahi','mahi-email')
INSERT itechxpert VALUES (2,'mahi','mahi-email')
INSERT itechxpert VALUES (3,'sana','mahi-email')
INSERT itechxpert VALUES (4,'sana','fred-email')
INSERT itechxpert VALUES (5,'sam','sam-email')
INSERT itechxpert VALUES (6,'sam','sam-email')

SELECT
name,email, COUNT(*) AS CountOf
FROM itechxpert
GROUP BY name,email
HAVING COUNT(*)>1
OUTPUT:

name email CountOf
---------- ----------- -----------
mahi mahi-email 2
sam sam-email 2

(2 row(s) affected)
if you want the IDs of the dups use this:

SELECT
y.id,y.name,y.email
FROM itechxpert y
INNER JOIN (SELECT
name,email, COUNT(*) AS CountOf
FROM itechxpert
GROUP BY name,email
HAVING COUNT(*)>1
) dt ON y.name=dt.name AND y.email=dt.email

Delete duplicate row in table

to delete the duplicates try:

DELETE d
FROM itechxpert d
INNER JOIN (SELECT
y.id,y.name,y.email,ROW_NUMBER() OVER(PARTITION BY y.name,y.email ORDER BY y.name,y.email,y.id) AS RowRank
FROM itechxpert y
INNER JOIN (SELECT
name,email, COUNT(*) AS CountOf
FROM itechxpert
GROUP BY name,email
HAVING COUNT(*)>1
) dt ON y.name=dt.name AND y.email=dt.email
) dt2 ON d.id=dt2.id
WHERE dt2.RowRank!=1
SELECT * FROM itechxpert

Delete duplicate row in table

Select duplicates:

SELECT *
FROM table
WHERE
id NOT IN (
SELECT MIN(id)
FROM table
GROUP BY column1, column2
);
Delete duplicates:

DELETE FROM table
WHERE
id NOT IN (
SELECT MIN(id)
FROM table
GROUP BY column1, column2
);

How to filter data in Heidi sql

Click filter button on right side.

type query such as :

File name : index.php

name ='mahtab'

id='101'
email = 'sana@gmail.com'

`pk_id` LIKE '%mahi%' OR `doc_no` LIKE '%mahi%' OR `first_name` LIKE '%mahi%' then click on apply filter button.

Grant permission to database.

grant all privileges on mytutorial.* to 'user_name'@'%' identified by 'mytute*#@1234';

// $sql = "SELECT pk_id FROM ittutorial WHERE id in ($tid) and type='$type'";
$sql = "SELECT pk_id,text FROM ittutorial WHERE tutorial_id='$id' and type='$type'";
$query = $this->db->query($sql);
$rows = $query->result_array();
return $rows;

$this->it_db = $this->load->database('other_tutorial_db', TRUE);
$query = $this->it_db->query("SELECT designation FROM staff st, designation d WHERE st.employee_number='$empno' and st.fki_designation_id=d.pk_des_id");
$rows = $query->result_array();
return $rows;

Array merge

public function get_details($id,$ref_type){
$currentdate=date('Y-m-d');
$startDate = $currentdate . " 00:00:00";
$qry1 = "SELECT it.* FROM itech as it WHERE it.ref_id IN ('".$this->db->escape_str($id)."') AND it.ref_type IN ('".$this->db->escape_str($ref_type)."')
AND meeting_ref_id=0 AND (TIMESTAMP(CONCAT(Year,'-',Month,'-',Day))) < '".$this->db->escape_str($currentdate)."' ORDER BY meeting_date DESC";
$query = $this->db->query($qry1);
$rows1 = $query->result_array();

$qry2 = "SELECT it.*, tcn.fromdate, tcn.todate FROM itech as it LEFT JOIN ittutorial tcn ON (tcn.ID = it.ref_id)
WHERE it.fki_ref_id IN ('".$this->db->escape_str($id)."') AND ref_id!=0 AND it.ref_type IN ('".$this->db->escape_str($ref_type)."')
AND DATE(tcn.todate) < '".$this->db->escape_str($currentdate)."' GROUP BY it.ref_id ORDER BY tcn.fromdate DESC";
$query2 = $this->db->query($qry2);
$rows2 = $query2->result_array();

$rows=array_merge($rows2,$rows1);
return $rows;
}

Itechtuto

PHP Tutorials

Important Link

mysql command?

Output :-

File name : index.php

Connect Us Socially:

Quick Links