Hierarchical data in MySQL: parents and children in one query

The idea is, how to implement a hierarchical query in MySQL (using the ancestry chains version) for a single row, such that it picks up the parents (if any) and any children (if any).

We need to combine two queries here:

1. Original hierarchical query that returns all descendants of a given id (a descendancy chain)
2. A query that would return all ancestors of a given id (an ancestry chain)

An id can have only one parent, that's why we can employ a linked list technique to build an ancestry chain, like shown in this article:

Here's the query to to this (no functions required):

SELECT  CONCAT(REPEAT('    ', level  - 1), _id) AS treeitem, parent, level
FROM    (
        SELECT  @r AS _id,
                (
                SELECT  @r := parent
                FROM    t_hierarchy
                WHERE   id = _id
                ) AS parent,
                @l := @l + 1 AS level
        FROM    (
                SELECT  @r := 1218,
                        @l := 0,
                        @cl := 0
                ) vars,
                t_hierarchy h
        WHERE   @r <> 0
        ORDER BY
                level DESC
        ) qi

Click to see more details. Thanks to Quassnoi to made my day easy!!  

Add Foreign Key to existing table

To add a foreign key (grade_id) to an existing table (users), follow the following steps:

ALTER TABLE users ADD grade_id SMALLINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE users ADD CONSTRAINT fk_grade_id FOREIGN KEY (grade_id) REFERENCES grades(id);

php mysql Group By to get latest record

If you select attributes that are not used in the group clause, and are not aggregates, the result is unspecified. I.e you don't know which rows the other attributes are selected from. (The sql standard does not allow such queries, but MySQL is more relaxed).

try something like

change the order by and limit as needed.

SELECT post_id, forum_id, topic_id 
FROM   (SELECT post_id, forum_id, topic_id
        FROM posts
        ORDER BY post_time DESC) 
GROUP BY topic_id 
ORDER BY topic_id desc
LIMIT 0,5

Special thanks to stackoverslow .

mysql case sensitive query

MySQL queries are not case-sensitive by default. Following is a simple query that is looking for 'value'. However it will return 'VALUE', 'value', 'VaLuE', etc…

SELECT * FROM `table` WHERE `column` = 'value'

The good news is that if you need to make a case-sensitive query, it is very easy to do:

SELECT * FROM `table` WHERE BINARY `column` = 'value'

Import huge sql file in database

Mysql indexes

1)   Database index is a data structure that improves the speed of operations in a table.

2)   Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

3)   INSERT and UPDATE statements take more time on tables having indexes where as SELECT statements become fast on those tables. The reason is that while doing insert or update, database need to insert or update index values as well.

There are four types of statements for adding indexes to a table:
                                           
            1) PRIMARY KEY
            2) UNIQUE 
            3) INDEX or KEY
            4) FULLTEXT                  

SYNTAX :

 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): This statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL.

· ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): This statement creates an index for which values must be unique (with the exception of NULL values, which may appear multiple times).

· ALTER TABLE tbl_name ADD INDEX index_name (column_list): This adds an ordinary index in which any value may appear more than once.

· ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): This creates a special FULLTEXT index that is used for text-searching purposes.

Differences

·       KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don't enforce any restraints on your data so they are used only for making sure certain queries can run quickly.

·       UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce restraints on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data.

Your database system may allow a UNIQUE index to be applied to columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (the rationale here is that NULL is considered not equal to itself). Depending on your application, however, youmay find this undesirable: if you wish to prevent this, you should disallow NULL values in the relevant columns.

·       PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always be one; though some database systems don't enforce this). A PRIMARY index is intended as a primary means to uniquely identify any row in the table, so unlike UNIQUE it should not be used on any columns which allow NULL values. Your PRIMARY index should be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead.

Some database systems (such as MySQL's InnoDB) will store a table's records on disk in the order in which they appear in the PRIMARY index.

·         FULLTEXT indexes are different from all of the above, and their behaviour differs significantly between database systems. FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause, unlike the above three - which are typically implemented internally using b-trees (allowing for selecting, sorting or ranges starting from left most column) or hash tables (allowing for selection starting from left most column).

Where the other index types are general-purpose, a FULLTEXT index is specialised, in that it serves a narrow purpose: it's only used for a "full text search" feature.

How do you add an index?

To add a correct index, identify the query that executes slowly, and then look at the “where” clause of that query – which fields are used for filtering?  These are probably the ones that should be indexed. For example:

SELECT first_name, last_name FROM contacts WHERE city = “Los Angeles”;

Here, if there isn't already an index on the column “city” (and if the table is more than a few rows), the index should be added:

ALTER TABLE contacts ADD KEY (city);

If you are filtering by multiple fields, like:

SELECT first_name, last_name FROM contacts WHERE status = “active” AND delivery_method = “mail” AND city = “Los Angeles”;

… then you should create a “composite” index:

ALTER TABLE contacts ADD KEY (status, delivery_method, city);

This index will speed up queries that filter by either all of these fields or a subset of them, starting from the left column, as specified in the ALTER TABLE statement.
That means the following where clauses will use the index:

… WHERE status = “active” AND delivery_method = “mail” AND city = “Los Angeles”;
… WHERE status = “active” AND delivery_method = “mail”;
… WHERE status = “active”;

In contrast, where clauses like these will not use this composite index:

… WHERE city = “Los Angeles”;
… WHERE delivery_method = “mail” AND city = “Los Angeles”;


You would have to create another index with the necessary column(s).

Example:

EXPLAIN SELECT first_name, last_name FROM contacts WHERE city = “Los Angeles”;

(I created a small test table consisting of five rows)

mysql> EXPLAIN SELECT first_name, last_name FROM contacts WHERE city = "Los Angeles"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: contacts
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.00 sec)

As you see here, possible_keys and keys are set to NULL, which means the MySQL optimizer didn’t find any keys to use. An important column in the EXPLAIN output is “rows”, which is an estimate of how many rows MySQL has to read to find the desired result. Here, it is five, which are all rows in the table. That means the database has to perform a full table scan.

Let’s add an index:

ALTER TABLE contacts ADD KEY (city);

That brings us to another important command – you can use “SHOW INDEX FROM <table>” to find out which indexes are already created on a particular table:

mysql> SHOW INDEX FROM contacts\G
*************************** 1. row ***************************
Table: contacts
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

*************************** 2. row ***************************

Table: contacts
Non_unique: 1
Key_name: city
Seq_in_index: 1
Column_name: city
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

Here we see two rows – the second row shows the newly added index on the column city. Now if we run the same EXPLAIN-statement again, we get a different – better – result:

mysql> EXPLAIN SELECT first_name, last_name FROM contacts WHERE city = "Los Angeles"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: contacts
type: ref
possible_keys: city
key: city
key_len: 103
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)


Notice that the possible_keys and keys columns both contain the field “city”, which means that MySQL was able to use this index to satisfy the query. Another important difference is the number of rows MySQL estimates it will have to search – it’s now only one!  The database is now going directly from the index to the row (which is referenced by the primary key).

Storage engines:

MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables:

MYISAM:

1.    MYISAM supports Table-level Locking

2.    MyISAM designed for need of speed

3.    MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS

4.    MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)

5.    MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.

6.    MYISAM supports fulltext search

7.    You can use MyISAM, if the table is more static with lots of select and less update and delete.

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine : MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance

     INNODB :

1.    InnoDB supports Row-level Locking

2.    InnoDB designed for maximum performance when processing high volume of data

3.    InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS

4.    InnoDB stores its tables and indexes in a tablespace

5.    InnoDB supports transaction. You can commit and rollback with InnoDB

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine : InnoDB
Support: YES
Comment: Supports transactions, row-level locking,and foreign keys

MUL mean in MySQL for the key

In any case, there are three possible values for the “Key” attribute:

1. PRI
2. UNI
3. MUL

The meaning of PRI and UNI are quite clear:

• PRI=> primary key
• UNI=> unique key

The third possibility, MUL, (which you asked about) is basically an index that is neither a primary key nor a unique key. The name comes from “multiple” because multiple occurences of the same value are allowed. Straight from the MySQL documentation:

“If Key is MUL, the column is the first column of a non-unique index in which multiple occurrences of a given value are permitted within the column.”


“If more than one of the Key values applies to a given column of a table, Key displays the one with the highest priority, in the order PRI, UNI, MUL.” 

Select current ( day ,month , year ) records mysql from timestamp column

Get Todays Record From table

SELECT * 
  FROM table1

  WHERE DAY(FROM_UNIXTIME(timestampfield)) = DAY(CURDATE())

Get Current Month Records

SELECT * 
  FROM table1  WHERE MONTH(FROM_UNIXTIME(timestampfield)) = MONTH(CURDATE()) 

Get current year Records

SELECT * 
  FROM table1  WHERE YEAR(FROM_UNIXTIME(timestampfield)) = YEAR(CURDATE())  

Just change the table name and timestampfield name .....

Database exported automatically in mysql

Simply to export database in mysql and stored in your particular path. See the following code and run in your system with your db details. After run successfully set the file in cron job. so you get the db backup daily or weekly or monthly to store automatically and take from particular path. cheers :)

<?php
$db_host="localhost"; //your host name
$db_user="root"; //your db user name
$db_pass="root"; //your db password
$db_name="test_db"; //your db name
$tables="*"; // all tables to select as *

db_backup($db_host,$db_user,$db_pass,$db_name,$tables);


function db_backup($db_host,$db_user,$db_pass,$db_name,$tables = '*')
{

  $con= mysql_connect($db_host,$db_user,$db_pass);
  mysql_select_db($db_name,$con);

  //get all of the tables
  if($tables == '*')
  {
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result))
    {
      $tables[] = $row[0];
    }
  }
  else
  {
    $tables = is_array($tables) ? $tables : explode(',',$tables);
  }

  //cycle through
  foreach($tables as $table)
  {
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);

    $return.= 'DROP TABLE IF  EXISTS '.$table.';';
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return.= "\n\n".$row2[1].";\n\n";

    for ($i = 0; $i < $num_fields; $i++)
    {
      while($row = mysql_fetch_row($result))
      {
        $return.= 'INSERT INTO '.$table.' VALUES(';
        for($j=0; $j<$num_fields; $j++)
        {
          $row[$j] = addslashes($row[$j]);
          $row[$j] = ereg_replace("\n","\\n",$row[$j]);
          if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
          if ($j<($num_fields-1)) { $return.= ','; }
        }
        $return.= ");\n";
      }
    }
    $return.="\n\n\n";
  }

  //save file
  $filename='db-backup-'.time().'.sql';
  $handle = fopen($filename,'w+');
  fwrite($handle,$return);
  fclose($handle);
}
?>

How to Import Data in MySql from MS SQL SERVER

I have a very good and Easy Steps for import Data in MySql from MS SQL Server.

1.) First of All You have to open Visual Studio 2010.

2.) Go to Server Explorer.

3.) Add a New Connection of MySql.

4.) Add Database which you want to import Data From SQL Server Db.

5.) Add another Connection of MS SQL Server.

6.) Add Database which you want to Export Data for MySql.

7.) Now you have Opened both Databases in Server Explorer of Visual Studio 2010.

8.) Now Right Click on Table (which you want to copy data to MySql) and click on SHOW TABLE DATA.

9.) Now Select All data from your table and Copy that records.

10.) Now Open MySql Database and open same Table (which you want to paste data into) from MySql and Paste all Data you copied from MS SQL SERVER.

MySQL - UPDATE query with LIMIT

Create a New Table .Table name is employee.

Query:

CREATE TABLE `testing`.`employee` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`dept` VARCHAR( 255 ) NOT NULL ,
`salary` DOUBLE NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;


Insert the 6 rows:

Query:

INSERT INTO `testing`.`employee` (`id`, `name`, `dept`, `salary`) VALUES (NULL, 'peter', 'dept-A', '4000'), (NULL, 'prince', 'dept-A', '5000');
INSERT INTO `testing`.`employee` (`id`, `name`, `dept`, `salary`) VALUES (NULL, 'palani', 'dept-A', '4000'), (NULL, 'raja', 'dept-A', '5000');
INSERT INTO `testing`.`employee` (`id`, `name`, `dept`, `salary`) VALUES (NULL, 'nagaraj', 'dept-A', '8000'), (NULL, 'vasanth', 'dept-A', '10000');

Query: SELECT * FROM `employee`;

Result:

id     name      dept           salary
----------------------------------------
1     peter      dept-A         4000
2     prince      dept-A         5000
3     palani      dept-A         4000
4     raja      dept-A         5000
5     nagaraj  dept-A         8000
6     vasanth  dept-A         10000


Query: SELECT dept , count(dept) FROM `employee` group by dept;

Result:

dept     count(dept)
-----------------------
dept-A    6


I want to change the last 3 rows dept field values from 'dept-A'  to 'dept-B'.

Use following query.This query implement limit option in update query.

Query :
UPDATE employee as a , (SELECT id,dept FROM `employee` where dept='dept-A' limit 3,3) as b SET a.dept='dept-B'  where a.id=b.id;

After run this query  see the all results using below query.


Query: SELECT * FROM `employee`;


Result:
id     name      dept           salary
----------------------------------------
1     peter      dept-A         4000
2     prince      dept-A         5000
3     palani      dept-A         4000
4     raja      dept-B         5000
5     nagaraj  dept-B         8000
6     vasanth  dept-B         10000


Query : SELECT dept , count(dept) FROM `employee` group by dept;

Result:

dept     count(dept)
-----------------------
dept-A    3
dept-B    3

Delete duplicate rows in mysql and php

How to delete duplicate records in table?
For example :

user_new table - total rows - 45000

duplicate rows - 5150

I have used below this code.It works fine.cheers!!!

<?php 

ini_set("max_execution_time","18800");
$con = mysql_connect("localhost","root","root");
mysql_select_db("testdb",$con);

$query = "SELECT email,count(email),group_concat(user_id) as uid FROM `user_new` group by email having count(email) > 1  ";

$result = mysql_query($query);
$total_rows = mysql_num_rows($result);
$del_ids = array();

if($total_rows>0)
{
  while($row=mysql_fetch_object($result))
  {
    $uid = $row->uid;
$aUid = explode(",",$uid);
array_shift($aUid);
 $rids = array_merge($del_ids,$aUid);
 $del_ids =  $rids;
   }
 //   print_r($del_ids);
   $Sdel_ids = implode(",",$del_ids);
   $del_query = "DELETE FROM user_new where user_id in ($Sdel_ids) ";
   echo $del_query;
   mysql_query($del_query);
}

?>