PHP-Database connectivity function's

One of the main aspects of PHP is database connectivity.Though it supports variety of databases we will talk about mysql in particular here.

All the functions which are of concern are present in file mysqli.php which contains class "mysqli_sql_exception" which in turn extends "RuntimeException"

The very first functions that we encounter while working with databases with PHP are the "connect functions".

Let's see what we get when we execute these functions in order

As a software development flows we will have some prerequisites,a function which will process them and then we will have some results.

Prerequiaites :

A dbhost : "localhost"
A username : "uroot"
A password : "proot"
Also

That's it and all we need is a php setup LAMP/WAMP/MAMP/XAMPP package

Our first function :

 $con= mysqli_connect('localhost1','puroot','pproot','test1');  

Reference :

/**
 * (PHP 5)<br/>
 * Alias of <b>mysqli::__construct</b>
 * @link http://php.net/manual/en/function.mysqli-connect.php
 * @param $host [optional]
 * @param $user [optional]
 * @param $password [optional]
 * @param $database [optional]
 * @param $port [optional]
 * @param $socket [optional]
 */
function mysqli_connect ($host, $user, $password, $database, $port, $socket) {}

This function tries to connect to the database with given credentials. We have created a negative mutant to test the same as you can see from what credentials we have passed above.


Our Second function and third function :

 if (mysqli_connect_errno())  
 {  
   echo "Failed to connect to MySQL: " . mysqli_connect_error();  
 }

Reference:

/**
 * (PHP 5)<br/>
 * Returns the error code from last connect call
 * @link http://php.net/manual/en/mysqli.connect-errno.php
 * @return int An error code value for the last call to <b>mysqli_connect</b>, if it failed.
 * zero means no error occurred.
 */
function mysqli_connect_errno () {}

/**
 * (PHP 5)<br/>
 * Returns a string description of the last connect error
 * @link http://php.net/manual/en/mysqli.connect-error.php
 * @return string A string that describes the error. <b>NULL</b> is returned if no error occurred.
 */
function mysqli_connect_error () {}

Now when we execute the above pieces of code we get an error

Failed to connect to MySQL: Unknown MySQL server host 'localhost1' (2)Error creating database:

When we set host right we get following error
Failed to connect to MySQL: Access denied for user 'puroot'@'localhost' (using password: YES)Error creating database: 

Similarly when we set username and password right we get
Failed to connect to MySQL: Unknown database 'test1'

Now that we are done with the set of connection functions we will try to create a database on success.Now since we have already created a database "test" we will test with a negative mutant.

Our fourth and fifth function

 $sql="CREATE DATABASE test";  
 if (mysqli_query($con,$sql))  
 {  
   echo "Database test created successfully";  
 }  
 else  
 {  
   echo "Error creating database: " . mysqli_error($con);  
 }  

Reference:

 /**  
  * (PHP 5)<br/>  
  * Performs a query on the database  
  * @link http://php.net/manual/en/mysqli.query.php  
  * @param mysqli $link  
  * @param string $query <p>  
  * The query string.  
  * </p>  
  * <p>  
  * Data inside the query should be properly escaped.  
  * </p>  
  * @param int $resultmode [optional] <p>  
  * Either the constant <b>MYSQLI_USE_RESULT</b> or  
  * <b>MYSQLI_STORE_RESULT</b> depending on the desired  
  * behavior. By default, <b>MYSQLI_STORE_RESULT</b> is used.  
  * </p>  
  * <p>  
  * If you use <b>MYSQLI_USE_RESULT</b> all subsequent calls  
  * will return error Commands out of sync unless you  
  * call <b>mysqli_free_result</b>  
  * </p>  
  * <p>  
  * With <b>MYSQLI_ASYNC</b> (available with mysqlnd), it is  
  * possible to perform query asynchronously.  
  * <b>mysqli_poll</b> is then used to get results from such  
  * queries.  
  * </p>  
  * @return mixed <b>FALSE</b> on failure. For successful SELECT, SHOW, DESCRIBE or  
  * EXPLAIN queries <b>mysqli_query</b> will return  
  * a <b>mysqli_result</b> object. For other successful queries <b>mysqli_query</b> will  
  * return <b>TRUE</b>.  
  */  
 function mysqli_query (mysqli $link, $query, $resultmode = 'MYSQLI_STORE_RESULT') {}  
 /**  
  * (PHP 5)<br/>  
  * Returns a string description of the last error  
  * @link http://php.net/manual/en/mysqli.error.php  
  * @param mysqli $link  
  * @return string A string that describes the error. An empty string if no error occurred.  
  */  
 function mysqli_error (mysqli $link) {}  

Error creating database: Can't create database 'test'; database exists

because we have already created one

similarly mysql_query() is used for create table,insert

Our sixth function

 $result= mysqli_query($con,"SELECT * FROM Persons");  
 while($row= mysqli_fetch_array($result))  
 {  
   echo "<pre>";  
   print_r($row);  
   echo "</pre>";  
 }  
 $result= mysqli_query($con,"SELECT * FROM Persons");  
 while($row= mysqli_fetch_row($result))  
 {  
   echo "<pre>";  
   print_r($row);  
   echo "</pre>";  
 }  
 $result= mysqli_query($con,"SELECT * FROM Persons");  
 while($row= mysqli_fetch_object($result))  
 {  
   echo "<pre>";  
   print_r($row);  
   echo "</pre>";  
 }  
 $result= mysqli_query($con,"SELECT * FROM Persons");  
 while($row= mysqli_fetch_assoc($result))  
 {  
   echo "<pre>";  
   print_r($row);  
   echo "</pre>";  
 }  

References for ways we can fetch row:

 /**  
  * (PHP 5)<br/>  
  * Fetch a result row as an associative, a numeric array, or both  
  * @link http://php.net/manual/en/mysqli-result.fetch-array.php  
  * @param mysqli_result $result  
  * @param int $resulttype [optional] <p>  
  * This optional parameter is a constant indicating what type of array  
  * should be produced from the current row data. The possible values for  
  * this parameter are the constants <b>MYSQLI_ASSOC</b>,  
  * <b>MYSQLI_NUM</b>, or <b>MYSQLI_BOTH</b>.  
  * </p>  
  * <p>  
  * By using the <b>MYSQLI_ASSOC</b> constant this function  
  * will behave identically to the <b>mysqli_fetch_assoc</b>,  
  * while <b>MYSQLI_NUM</b> will behave identically to the  
  * <b>mysqli_fetch_row</b> function. The final option  
  * <b>MYSQLI_BOTH</b> will create a single array with the  
  * attributes of both.  
  * </p>  
  * @return mixed an array of strings that corresponds to the fetched row or <b>NULL</b> if there  
  * are no more rows in resultset.  
  */  
 function mysqli_fetch_array (mysqli_result $result, $resulttype = 'MYSQLI_BOTH') {}
/**
 * (PHP 5)<br/>
 * Get a result row as an enumerated array
 * @link http://php.net/manual/en/mysqli-result.fetch-row.php
 * @param mysqli_result $result
 * @return mixed <b>mysqli_fetch_row</b> returns an array of strings that corresponds to the fetched row
 * or <b>NULL</b> if there are no more rows in result set.
 */
function mysqli_fetch_row (mysqli_result $result) {}
/**
 * (PHP 5)<br/>
 * Fetch a result row as an associative array
 * @link http://php.net/manual/en/mysqli-result.fetch-assoc.php
 * @param mysqli_result $result
 * @return array an associative array of strings representing the fetched row in the result
 * set, where each key in the array represents the name of one of the result
 * set's columns or <b>NULL</b> if there are no more rows in resultset.
 * </p>
 * <p>
 * If two or more columns of the result have the same field names, the last
 * column will take precedence. To access the other column(s) of the same
 * name, you either need to access the result with numeric indices by using
 * <b>mysqli_fetch_row</b> or add alias names.
 */
function mysqli_fetch_assoc (mysqli_result $result) {}
/**
 * (PHP 5)<br/>
 * Returns the current row of a result set as an object
 * @link http://php.net/manual/en/mysqli-result.fetch-object.php
 * @param mysqli_result $result
 * @param string $class_name [optional] <p>
 * The name of the class to instantiate, set the properties of and return.
 * If not specified, a <b>stdClass</b> object is returned.
 * </p>
 * @param array $params [optional] <p>
 * An optional array of parameters to pass to the constructor
 * for <i>class_name</i> objects.
 * </p>
 * @return object an object with string properties that corresponds to the fetched
 * row or <b>NULL</b> if there are no more rows in resultset.
 */
function mysqli_fetch_object (mysqli_result $result, $class_name = null, array $params = null) {}

Types of Indexes in a database

Bitmap index

A bitmap index is a special kind of index that stores the bulk of its data as bit arrays (bitmaps) and answers most queries by performing bitwise logical operations on these bitmaps. The most commonly used indexes, such as B+trees, are most efficient if the values they index do not repeat or repeat a smaller number of times. In contrast, the bitmap index is designed for cases where the values of a variable repeat very frequently. For example, the gender field in a customer database usually contains two distinct values: male or female. For such variables, the bitmap index can have a significant performance advantage over the commonly used trees.

Dense index

A dense index in databases is a file with pairs of keys and pointers for every record in the data file. Every key in this file is associated with a particular pointer to a record in the sorted data file. In clustered indices with duplicate keys, the dense index points to the first record with that key.

Sparse index

A sparse index in databases is a file with pairs of keys and pointers for every block in the data file. Every key in this file is associated with a particular pointer to the block in the sorted data file. In clustered indices with duplicate keys, the sparse index points to the lowest search key in each block.

Reverse index

A reverse key index reverses the key value before entering it in the index. E.g., the value 24538 becomes 83542 in the index. Reversing the key value is particularly useful for indexing data such as sequence numbers, where new key values monotonically increase.

 

MYISAM and INNODB difference

Differences between INNODB abd MYISAM Database

• InnoDB uses row locking, MyISAM uses table locking.
• InnoDB is slower when it comes to SELECT
• InnoDB enforces referential integrit
• InnoDB allows transactions
• MyISAM allows full text search Indexes where as InnoDB does not allows that 
• You will find better crash recovery in InnoDB. 

MyISAM	Innodb
Not *ACID compliant and non-transactional	*ACID compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys
MySQL 5.0 Default Engine	Rackspace Cloud Default Engine
Offers Compression	Offers Compression
Requires full repair/rebuild of indexes/tables	Auto recovery from crash via replay of logs
Changed Db pages written to disk instantly	Dirty pages converted from random to sequential before commit and flush to disk
No ordering in storage of data	Row data stored in pages in PK order
Table level locking	Row level locking

Third Highest Salary

Here is a database question asked usually during Interviews which I would like to share

Name Salary
A          50
B          20
C          30
D          40
E          30
F          40

Now out of these find employees with third highest salary

Here are steps to do so first let us sort in descending order and limit the same to third row

Select DISTINCT(Salary) from emp order by Salary DESC limit 2,1

Now this gives us 30 but we need salary of C and D as rows we take this as subquery

Select * from Emp where salary in( Select DISTINCT(Salary) from emp order by Salary DESC limit 2,1);
which is the correct answer

Normalizing the database

Normalization refers to removal of redundant information from a database Design
Following are the basic normal Forms

1st Normal Form
When each table row is free of repeated data.So a relation is in first normal form if and only if all underlying domains contain atomic values

2nd Normal Form
Applies to tables that have composite keys i.e. the primary key is made up of 2 or more columns.When a table has a composite key then every column must depend on the whole key instead of a particular column.

A relation must be in 1st NF and every nonkey attribute must be fully dependent on the primary key.

3rd Normal Form
When every column in a table depends on entire primary key and no nonkey columns depend on each other.

2nd NF + nonkey attribute is non transitively dependent on primary key

Boyce-Codd Normal Form
A table is in Boyce–Codd normal form if and only if for every one of its nontrivial dependencies X → Y, X is a superkey—that is, X is either a candidate key or a superset thereof.

4th Normal Form
A Table is in 4NF if and only if, for every one of its non-trivial multivalued dependencies X Y, X is a superkey—that is, X is either a candidate key or a superset thereof.

5th Normal Form
Every join dependency in R is implied by the candidate keys of R