clarification about DAL and MySQLi functions in PHP

Question

Up to now, I was considering MySQLi functions in PHP one example of DAL. And I initiated a simple project to publish in an article with the title "starting with PHP and DAL and MySQL"

But when I made a search for some topics to add in my article I "discovered" two things:

1. The website PHP.net don't list MySQLi in there page about DAL
2. Many articles speech about MySQLi as library or API, but I didn't found anyone stating it as DAL.

And now I have this question: Why the class PDO is considered to be a DAL example, but the class MySQLi isn't considere a DAL example ?

What I was thinking to write is something like that:

If you create a class named Container, like the code bellow, it's easy to see the access to DAL by functions: mysqli_connect, mysqli_query, and the others mysqli_functions

// this is a very basic implementation, and need many improvements, it is just an example
class Container 
{
 private $_Connection = null;
 private $_Fields = null;
 function __construct($host, $user, $password, $database) 
 {
 $connection = mysqli_connect($host,$user,$password);
 if( $connection ) 
 {
 mysqli_select_db($connection, $database);
 mysqli_set_charset($connection, "utf8");
 $this->_Connection = $connection;
 }
 }
 function __destruct() 
 {
 if( $this->_Connection )
 {
 mysqli_close($this->_Connection); 
 } 
 }
 function Query( $sql ) 
 {
 $dataset = array();
 $result = mysqli_query( $this->_Connection, $sql );
 if ($result)
 {
 $this->_Fields = mysqli_fetch_fields($result);
 while($data = mysqli_fetch_assoc($result)) 
 {
 $dataset[] = $row;
 }
 }
 return $dataset; 
 }
 function Fields( $index ) 
 {
 if(isset($index))
 {
 return $this->_Fields[$index];
 }
 else
 {
 return $this->_Fields; 
 } 
 }
}

---

16/02/17:

What if I change my class Container constructor to something like:

function __construct($driver, $host, $user, $password, $database) 
{
 switch( $driver )
 {
 case "MYSQL": $connection = mysqli_connect($host,$user,$password);
 if( $connection ) 
 {
 mysqli_select_db($connection, $database);
 mysqli_set_charset($connection, "utf8");
 $this->_Connection = $connection;
 }
 break;
 case "SQLServer": // to do...
 case "Oracle": // to do...
 }
}

And make similar modifications in Container::Query and Container::Fields methods.

So, in this case, my class Container will be an example of DAL ?

---

17/02/17:

Can someone create a DAL for an specific database ?

Like this: https://github.com/mdandy/PHP-DAL

This is a Data Access Layer for MySQL

Answer 1

Because Mysqli is not Abstraction Layer, it's specific to Mysql.

PDO is an abstraction, you don't have to know about how PDO drivers talk to different kinds of databases. You only have one interface to care about.

ODBC/JDBC are also example of abstractions.

2nd question

That would make the class an example of DAL if you don't make "similar modification" to Query() and Fields() methods. I'd advise you to change that Container constructor to be:

public function __construct(Connection $connection) {
 $this->_Connection = $connection;
}

and create a Connection class with factory method that takes ($driver, $host, $user, $password, $database). The specifics of how to do Query() and Fields() should go to specific Connection implementations, not in the Container abstraction layer.

Answer 2

First thing, there is DAL and DAL:

Data Access Layer: is a layer of a computer program which provides simplified access to data stored in persistent storage (en.wikipedia.org/wiki/Data_access_layer)

MySQLi is an example of Access Layer, as it provides simplified acces to MySQL database.

Data Abstraction Layer: is an Application Programming Interface (API) which unifies the communication between a computer application and databases such as SQL Server, DB2, MySQL, PostgreSQL, Oracle or SQLite (en.wikipedia.org/wiki/Database_abstraction_layer)

PDO is an example of Abstract Layer, as it provides simplified acces to a list of databases.

Your first code is in fact a Access Layer, to MySQL, using MySQLi functions.

Your second code could be an Abstraction Layer, it will depend on how you write the other functions.

In your last question, as you can see and understand now, the Data Access Layer is specific to one database.

Further reading:

1. www.xaprb.com/blog/2006/08/13/four-types-of-database-abstraction-layers/
2. stackoverflow.com/questions/2838661/what-is-the-difference-between-database-abstraction-layer-data-acess-layer