Difference between mysql & mysqli?

There are three main API options when considering connecting to a MySQL database server:

• PHP's MySQL Extension
• PHP's mysqli Extension
• PHP Data Objects (PDO)

In relation to PHP, MySQL is the old database driver, and MySQLi is the Improved driver. MySQLi takes advantage of the newer features of MySQL 5. Lifted verbatim from the php.net site:

• Object-oriented interface
• Support for Prepared Statements
• Support for Multiple Statements
• Support for Transactions
• Enhanced debugging capabilities
• Embedded server support

The "i" stands for "improved".

mysqli stands for mysql improved. The reason to use this over just mysql_ is because it is safer to use.

MySQLi is the OOP version of MySQL extension. In the end, the accomplish the same thing: extension for interacting with MySQL from PHP. A lot of people still use the original MySQL extension vs the new MySQLi extension because MySQLi requires MySQL 4.1.13+ and PHP 5.0.7+ .

MySQLi supports some things that the old MySQL extension doesn't. Things like prepared statements, multiple statements, and transactions on top of my head.

• Dual object-oriented (OO) and procedural interfaces – This versatility was absent in previous version of MySQL. The procedural interface may be preferred by users migrating over from the old MySQL system, with function names differing only in prefix (simply add an “i). The object-oriented interface shows functions grouped by their purpose, making for an easier start. The two interfaces perform similarly with user preference being the deciding factor between the two.
• Support for prepared statements – Bringing MySQL/PHP up to date with other platforms, MySQLi is able to pass a query without any data to MySQL. After binding the parameters, the query is executed. This process results in increased performance, convenience for the developer and more security by preventing SQL injection attacks.
• Ability to process multiple statements – The ability to string together multiple statements in one statement string reduces client-server round trips. The individual statements that make up the string are separated by semicolons, then the results of each executed statement are fetched.
• Embedded server support – MySQLi offers methods of connecting and manipulating embedded MySQL server libraries.
• Enhanced debugging capabilities – Users are able to enter debugging queries into the system which results in a more efficient development process. An example of MySQLi debugging query would read: mysqli_debug(“d:t:o,/tmp/client.trace”)
• Support for ability to use transactions –  Using transactions allows you to input a group of queries that execute but do not save their effects in the database. This is helpful if you have multiple inserts that rely on each other, if one fails, the others can be rolled back and none of the data is inserted.

The Pros and Cons of the Changes

While MySQLi has its advantages in interfacing with MySQL, the other options sometimes have a leg up and may be a better fit for the user, depending on personal preference and convenience. Like most things, the option that works best depends on whom you ask and what situation you need MySQLi or its competitors for.

Pros

1. MySQLi’s interface is similar to the older MySQL interface. This versatility in using both object-oriented and procedural formats makes it easier for users of the older system to make the change to the improved one, as well as facilitating use for people that are completely new to the system. Using the old system, the user would enter a function looking like this: mysql_connect(), using the new system, this old code can be updated simply by changing the function to this: mysqli_connect().
2. MySQLi has good support and maintenance, making the transition to and use of the new system safe and secure.
3. The user has the ability to utilize new features available in the newest versions of MySQL servers. This is one of the biggest advantages of MySQLi. Other platforms are unable to take full advantage of MySQL’s newest capabilities.
4. The installation process with MySQLi not only easy, but is automatic when the PHP5 MySQL package is installed in Windows or Linux.
5. MySQLi performs (slightly) faster than its competition. With non-prepared statements, the system performs  ~2.5% faster, and ~6.5% faster with prepared ones. This is only a slight advantage and may only matter to certain users.

Cons

1. MySQLi only works with MySQL databases, whereas PDO is flexible and able to work with multiple database systems, including IBM, Oracle and MySQL. If you have to switch databases, MySQLi is not the best option.
2. MySQLi is not as portable as PDO.
3. Binding parameters with MySQLi is not as easy or flexible as with PDO. MySQLi uses a numbered binding system and doesn’t support named parameters like PDO does.

MySQL Storage Engines

Introduction

Storage engines (underlying software component) are MySQL components, that can handle the SQL operations for different table types to store and manage information in a database. InnoDB is mostly used general-purpose storage engine and as of MySQL 5.5 and later it is the default engine. There are many storage engines available in MySQL and they are used for different purposes.

Version : MySQL 5.6

Storage engines of MySQL

Engines	Description
InnoDB	This is the default storage engine for MySQL 5.5 and higher. It provides transaction-safe (ACID compliant) tables, supports FOREIGN KEY referential-integrity constraints. It supports commit, rollback, and crash-recovery capabilities to protect data. It also support row-level locking. It's "consistent nonlocking reads" increases performance when used in a multiuser environment. It stores data in clustered indexes which reduces I/O for queries based on primary keys.
MyISAM	This storage engine, manages non transactional tables, provides high-speed storage and retrieval, supports full text searching.
MEMORY	Provides in-memory tables, formerly known as HEAP. It sores all data in RAM for faster access than storing data on disks. Useful for quick looks up of reference and other identical data.
MERGE	Groups more than one similar MyISAM tables to be treated as a single table, can handle non transactional tables, included by default.
EXAMPLE	You can create tables with this engine, but can not store or fetch data. Purpose of this is to teach developers about how to write a new storage engine.
ARCHIVE	Used to store a large amount of data, does not support indexes.
CSV	Stores data in Comma Separated Value format in a text file.
BLACKHOLE	Accepts data to store but always returns empty.
FEDERATED	Stores data in a remote database.

Advantages of InnoDB storage engine

• InnoDB has maximum performance when processing large data volumes.
• Its DML operations (add, update and delete data) is ACID (atomic, consistent, isolated and durable) model compatible, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
• Row-level locking (locks are placed on single records (rows)) system increase multi-user concurrency and performance. All InnoDB locks held by a transaction are released when the transaction is committed or aborted.
• InnoDB tables arrange your data on disk to optimize queries based on primary keys.
• InnoDB supports FOREIGN KEY constraints to maintain data integrity. Therefore inserts, updates, and deletes are all checked to ensure they do not result in inconsistencies across different tables.
• It is possible to mix InnoDB tables with tables from other MySQL storage engines within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.

Creating InnoDB tables : 
Use CREATE TABLE statement to create am InnoDB table without any special clauses. As of MySQL 5.5, it is the default MySQL storage engine. In MySQL 5.6, issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table. Here is an example :

mysql> CREATE TABLE table1 (col1 INT, col2 CHAR(30), PRIMARY KEY (col1));
Query OK, 0 rows affected (1.11 sec)

Handling FOREIGN KEY Constraints in InnoDB :

MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. Foreign key definitions for InnoDB tables are subject to the following conditions :

• InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
• InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys.
• InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

Limitation: InnoDB table :

• Maximum 1017 columns are allowed in a table (raised in MySQL 5.6.9 from the earlier limit of 1000).
• Maximum 64 secondary indexes are allowed in a table. Secondary indexes is a type of InnoDB index that represents a subset of table columns.
• By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.
• The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes (combined index key in a multi-column index).
• The maximum row length except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is about 8000 bytes for the default page size of 16KB.
• Internally InnoDB supports row sizes larger than 65,535 bytes, but MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns.
• The maximum table space size is four billion database pages (64TB) and the minimum table space size is slightly larger than 10MB.

Why do we use GROUP BY and ORDER BY function in mysql?

Group By is used for retrieving information about a group of data. It is generally used with some aggregate function like SUM, AVG etc.

ORDER BY is used to sort the records using column name. It can sort column in both ascending and descending order.

What is difference between MYISAM and InnoDB?

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.
8. MySQL 5.0 Default Engine

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
6. InnoDB offers:
   • ACID transactions
   • row-level locking
   • foreign key constraints
   • automatic crash recovery
   • table compression (read/write)
   • spatial data types (no spatial indexes)
7. Rackspace Cloud Default Engine
8. InnoDB is the default storage engine as of MySQL 5.5.5.

ISAM = Indexed Sequential Access Method and is essentially a flat file (for those DBAs who can remember, think Btrieve, or B-Tree). It's a very old technology - but don't let that put you off using it. Because it's a flat file (more on that later), it is not relational, and thus is not an RDBMS, and thus is more appropriate in some situations.

InnoDB is the full RDBMS like you are most likely familiar with. MyISAM can appear to be relational through another layer added on top that maintains your links, logic and referential integrity.

Tables created in MyISAM are known to have higher speed compared to tables in InnoDB. But since InnoDB supports volume, transactions, integrity it’s always a better option which you are dealing with a larger database. A single database can have tables of different storage engines.

To check engines you can use command:

mysql> Show engines;

You can change the engine while creating the table by command:

CREATE TABLE test name varchar(30) ENGINE = InnoDB;

It is also possible to convert from one engine to the other by command:

ALTER TABLE my_table ENGINE=new_engine;

When you will execute the above command, complete process will be as such - the table will get locked, dumped to a tmp space, then rebuilt with the new engine. Also you will be losing innodb-only info (foreign keys, etc.) and features if you’re going to MyISAM.

In how many ways we can retrieve the data in the result set of MySQL using PHP?

You can do it by 4 Ways
1. mysql_fetch_row.
2. mysql_fetch_array
3. mysql_fetch_object
4. mysql_fetch_assoc

Why use views. View rules and restrictions.

Views use:

• Same table can be created in different forms
• Can hide certain columns of a table
• Can save time and efforts for queries that are frequently executed
• Can help in manipulating data

Rules and restriction:

• The SELECT statement cannot contain a subquery in the FROM clause.
• The SELECT statement cannot refer to system or user variables.
• Any table or view referred to in the definition must exist
• A temporary view cannot be created
• Any tables named in the view definition must exist at definition time
• A trigger cannot be associated with a view

CREATE VIEW Syntax with an example

SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
Syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Example:
CREATE VIEW [sample] AS
SELECT employeeID,employeeName
FROM employee
WHERE salary > 10000

ALTER VIEW Syntax with an example

The existing view can be updated as shown below. Below we are adding an additional columns salary.
CREATE VIEW [employee_details] AS
SELECT employeeID,employeename,salary
FROM Employee
WHERE Discontinued=No

DROP VIEW Syntax with an example

The view can be dropped by using the DROP statement.
Syntax:

DROP view view_name

Explain how to use ORDER BY clause inside PHP Script.

ORDER BY clause in PHP is used to sort the data in the result set. It sorts data in ascending manner by default. To sort data in descending order, DESC is used.
Example:

<?php
//connect to database
mysql_select_db("my_db", $con);
//Query to list employees ordered by AGE
$result = mysql_query("SELECT * FROM Employee ORDER BY age");
?>

Explain SELECT Statement by providing examples for the following.

• Retrieving Individual Columns
• Retrieving multiple Columns
• Retrieving ALL Columns
• Retrieving Distinct Rows

Select statement allows selecting and retrieving data from tables.
Syntax:
Select * | column_name from table name

Retrieving Individual Columns
Select employee_name from employee Where emp_id > 1

Retrieving multiple Columns
Select employee_firstname, employee_lastname from employee Where emp_id > 1

Retrieving ALL Columns
Select * from employee Where emp_id > 1


Retrieving Distinct Rows
Select disctint mobile_number From employee

Explain the use of Group By with an example.

Group By is used along with aggregate functions to group the result-set by one or more columns.

Example: The query below will display employees along with their salaries.


SELECT employee_name,SUM(salary) FROM employee GROUP BY employee_name