Most Popular Tutorials




How to write mysql query?

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;
    }





    Previous Next


    Trending Tutorials


    PHP

    PHP

    100+ Tutorials
    oops

    PHP OOPs

    80+ Tutorial
    Codeigniter

    Codeigniter

    100+ Tutorial
    Laravel

    Laravel

    100+ Tutorials
    Node.js

    NODE.JS

    100+ Tutorials
    Javascript

    Javascript

    100+ Tutorials


    Review & Rating

    0.0 / 5

    0 Review

    5
    (0)

    4
    (0)

    3
    (0)

    2
    (0)

    1
    (0)

    Write Review Here


    Most Popular Tutorials

     Important Link

    Ittutorial