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) {}