PHP Classes

DaoOne: Access MySQL databases using the MySQLi extension

Recommend this page to a friend!
  Info   View files View files (23)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog (1)    
Ratings Unique User Downloads Download Rankings
Not yet rated by the usersTotal: 212 This week: 1All time: 8,340 This week: 560Up
Version License PHP version Categories
daoone 1.0.0MIT/X Consortium ...5PHP 5, Databases
Description 

Author

This class can access MySQL databases using the MySQLi extension.

It can connect to a MySQL database and execute SQL queries with the MySQLi extension.

The class provides several types of functions to run and process queries like:

- Prepare and execute arbitrary SQL queries
- Run multiple queries at once
- Manage transactions
- Get the last inserted record identifier
- Get the last error message
- Convert time and date values between different formats
- Log executed queries to a file

The class has a read-only mode that prevents it to execute SQL queries that would modify the database.

Picture of Jorge Castro
  Performance   Level  
Name: Jorge Castro <contact>
Classes: 30 packages by
Country: Chile Chile
Age: 48
All time rank: 12763 in Chile Chile
Week rank: 106 Up1 in Chile Chile Up
Innovation award
Innovation award
Nominee: 14x

Winner: 2x

Details

Database Access Object wrapper for PHP and MySqli in a single class

DaoOne. It's a simple wrapper for Mysqli

This library is as fast as possible. Most of the operations are simple string/array managements.

> Note: This release is moved to https://github.com/EFTEC/PdoOne > PdoOne does the same job but it works with PDO library (instead of MySQLi). > Right now PdoOne works with Mysqli and SqlSrv but it has many other features that will > not be present on DaoOne

Build Status Packagist Total Downloads [Maintenance]() [composer]() [php]() [php]() [CocoaPods]()

Migrating to eftec/PdoOne

Adding the dependency

Install via composer

> composer require eftec/pdoone

This library could works in tandem with eftec/daoone.

Changing the library

Change the class, instead of use eftec/daoone -> eftec/pdoone

Example:

Before:

/ @var \eftec\DaoOne $db */
$db=null;

After:

/ @var \eftec\PdoOne $db */
$db=null;

Constructor

Before:

$db=new DaoOne('127.0.0.1','root','abc.123','sakila');

After:

$db=new DaoOne('mysql','127.0.0.1','root','abc.123','sakila'); // check 'mysql'

Operators

If we use DaoOne::runGen(false), then we must check the result. runGen (DaoOne) returns a mysqli_result object. runGen (PdoOne) returns a pdostatement object.

Before:

$result=$db->runGen(false); // it returns a mysqli_result
$result->fetch_assoc();
$result->free_result();

After:

$result=$db->runGen(false); // it returns a pdostatement
$result->fetch( PDO::FETCH_ASSOC);
$result=null;

How it works?

Turn this

$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while($row = $result->fetch_assoc()) {
  $ids[] = $row['id'];
  $names[] = $row['name'];
  $ages[] = $row['age'];
}
var_export($ages);
$stmt->close();

into this

$products=$dao
    ->select("*")
    ->from("myTable")
    ->where("name = ?",[$_POST['name']])
    ->toList();

Table of Content

Install (using composer)

>

Add to composer.json the next requirement, then update composer.

  {
      "require": {
        "eftec/daoone": "^3.15"
      }
  }

or install it via cli using

> composer require eftec/daoone

Install (manually)

Just download the file lib/DaoOne.php and save it in a folder.

Usage

Start a connection

$dao=new DaoOne("127.0.0.1","root","abc.123","sakila","");
$dao->connect();

where * 127.0.0.1 is the server where is the database. * root is the user * abc.123 is the password of the user root. * sakila is the database used. * "" (optional) it could be a log file, such as c:\temp\log.txt

Run an unprepared query

$sql="CREATE TABLE `product` (
    `idproduct` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) NULL,
    PRIMARY KEY (`idproduct`));";
$dao->runRawQuery($sql);  

Run a prepared query

$sql="insert into `product`(name) values(?)";
$stmt=$dao->prepare($sql);
$productName="Cocacola";
$stmt->bind_param("s",$productName); // s stand for string. Also i =integer, d = double and b=blob
$dao->runQuery($stmt);

> note: you could also insert using a procedural chain [insert($table,$schema,[$values])](#insert--table--schema---values--)

Run a prepared query with parameters.

$dao->runRawQuery('insert into `product` (name) values(?)'
    ,array('s','cocacola'));

Return data (first method)

It returns a mysqli_statement.

    $sql="select * from `product` order by name";
    $stmt=$dao->prepare($sql);
    $dao->runQuery($stmt);
    $rows = $stmt->get_result();
    while ($row = $rows->fetch_assoc()) {
        var_dump($row);
    }
    

> This statement must be processed manually.

Return data (second method)

It returns an associative array.

    $sql="select * from `product` order by name";
    $stmt=$dao->prepare($sql);
    $dao->runQuery($stmt);
    $rows = $stmt->get_result();
    $allRows=$rows->fetch_all(MYSQLI_ASSOC);
    var_dump($allRows);

Running a transaction

try {
    $sql="insert into `product`(name) values(?)";
    $dao->startTransaction();
    $stmt=$dao->prepare($sql);
    $productName="Fanta";
    $stmt->bind_param("s",$productName); 
    $dao->runQuery($stmt);
    $dao->commit(); // transaction ok
} catch (Exception $e) {
    $dao->rollback(false); // error, transaction cancelled.
}

startTransaction()

It starts a transaction

commit($throw=true)

It commits a transaction. * If $throw is true then it throws an exception if the transaction fails to commit. Otherwise, it does not.

rollback($throw=true)

It rollbacks a transaction. * If $throw is true then it throws an exception if the transaction fails to rollback. If false, then it ignores if the rollback fail or if the transaction is not open.

Fields

throwOnError=true

If true (default), then it throws an error if happens an error. If false, then the execution continues

isOpen=true

It is true if the database is connected otherwise,it's false.

Query Builder (DQL)

You could also build a procedural query.

Example:

$results = $dao->select("*")->from("producttype")
    ->where('name=?', ['s', 'Cocacola'])
    ->where('idproducttype=?', ['i', 1])
    ->toList();   

select($columns)

Generates a select command.

$results = $dao->select("col1,col2")->...

> Generates the query: select col1,col2 ....

$results = $dao->select("select * from table")->...

> Generates the query: select * from table ....

distinct($distinct='distinct')

Generates a select command.

$results = $dao->select("col1,col2")->distinct()...

> Generates the query: select distinct col1,col2 ....

>Note: ->distinct('unique') returns select unique ..

from($tables)

Generates a from command.

$results = $dao->select("*")->from('table')...

> Generates the query: select from table*

$tables could be a single table or a sql construction. For examp, the next command is valid:

$results = $dao->select("*")->from('table t1 inner join t2 on t1.c1=t2.c2')...

where($where,[$arrayParameters=array()])

Generates a where command.

  • $where is an array or a string. If it's a string, then it's evaluated by using the parameters. if any
$results = $dao->select("*")
->from('table')
->where('p1=1')...

> Generates the query: select from table* where p1=1

> Note: ArrayParameters is an array as follow: type,value. > Where type is i=integer, d=double, s=string or b=blob. In case of doubt, use "s" > Example of arrayParameters: > ['i',1 ,'s','hello' ,'d',20.3 ,'s','world']

$results = $dao->select("*")
->from('table')
->where('p1=?',['i',1])...

> Generates the query: select from tablewhere p1=?(1)*

$results = $dao->select("*")
->from('table')
->where('p1=? and p2=?',['i',1,'s','hello'])...

> Generates the query: select from tablewhere p1=?(1) and p2=?('hello')*

> Note. where could be nested.

$results = $dao->select("*")
->from('table')
->where('p1=?',['i',1])
->where('p2=?',['s','hello'])...

> Generates the query: select from tablewhere p1=?(1) and p2=?('hello')*

You could also use:

$results = $dao->select("*")->from("table")
    ->where(['p1'=>'Coca-Cola','p2'=>1])
    ->toList();

> Generates the query: select from tablewhere p1=?(Coca-Cola) and p2=?(1)*

order($order)

Generates a order command.

$results = $dao->select("*")
->from('table')
->order('p1 desc')...

> Generates the query: select from tableorder by p1 desc*

group($group)

Generates a group command.

$results = $dao->select("*")
->from('table')
->group('p1')...

> Generates the query: select from tablegroup by p1*

having($having,[$arrayParameters])

Generates a group command.

$results = $dao->select("*")
->from('table')
->group('p1')
->having('p1>?',array('i',1))...

> Generates the query: select * from table group by p1 having p1>?(1)

> Note: Having could be nested having()->having() > Note: Having could be without parameters having('col>10')

runGen($returnArray=true)

Run the query generate.

>Note if returnArray is true then it returns an associative array. > if returnArray is false then it returns a mysqli_result >Note: It resets the current parameters (such as current select, from, where,etc.)

toList()

It's a macro of runGen. It returns an associative array or null.

$results = $dao->select("*")
->from('table')
->toList()

toResult()

It's a macro of runGen. It returns a mysqli_result or null.

$results = $dao->select("*")
->from('table')
->toResult()

first()

It's a macro of runGen. It returns the first row (if any, if not, it returns false) as an associative array.

$results = $dao->select("*")
->from('table')
->first()

last()

It's a macro of runGen. It returns the last row (if any, if not, it returns false) as an associative array.

$results = $dao->select("*")
->from('table')
->last()

> Sometimes is more efficient to run order() and first() because last() reads all values.

sqlGen()

It returns the sql command.

$sql = $dao->select("*")
->from('table')
->sqlGen();
echo $sql; // returns select * from table
$results=$dao->toList(); // executes the query

> Note: it doesn't reset the query.

Query Builder (DML), i.e. insert, update,delete

There are four ways to execute each command.

Let's say that we want to add an integer in the column col1 with the value 20

__Schema and values using a list of values__: Where the first value is the column, the second is the type of value (i=integer,d=double,s=string,b=blob) and second array contains the values.

$dao->insert("table"
    ,['col1','i']
    ,[20]);

__Schema and values in the same list__: Where the first value is the column, the second is the type of value (i=integer,d=double,s=string,b=blob) and the third is the value.

$dao->insert("table"
    ,['col1','i',20]);

__Schema and values using two associative arrays__:

$dao->insert("table"
    ,['col1'=>'i']
    ,['col1'=>20]);

__Schema and values using a single associative array__: The type is calculated automatically.

$dao->insert("table"
    ,['col1'=>20]);

insert($table,$schema,[$values])

Generates a insert command.

$dao->insert("producttype"
    ,['idproducttype','i','name','s','type','i']
    ,[1,'cocacola',1]);

Using nested chain (single array)

    $dao->from("producttype")
        ->set(['idproducttype','i',0 ,'name','s','Pepsi' ,'type','i',1])
        ->insert();

Using nested chain multiple set

    $dao->from("producttype")
        ->set("idproducttype=?",['i',101])
        ->set('name=?',['s','Pepsi'])
        ->set('type=?',['i',1])
        ->insert();

or (the type is defined, in the possible, automatically by MySql)

    $dao->from("producttype")
        ->set("idproducttype=?",['i',101])
        ->set('name=?','Pepsi')
        ->set('type=?',1)
        ->insert();


Using nested chain declarative set

    $dao->from("producttype")
        ->set('(idproducttype,name,type) values (?,?,?)',['i',100,'s','Pepsi','i',1])
        ->insert();

> Generates the query: insert into productype(idproducttype,name,type) values(?,?,?) ....

update($$table,$schema,$values,[$schemaWhere],[$valuesWhere])

Generates a insert command.

$dao->update("producttype"
    ,['name','s','type','i'] //set
    ,[6,'Captain-Crunch',2] //set
    ,['idproducttype','i'] // where
    ,[6]); // where

$dao->update("producttype"
    ,['name'=>'Captain-Crunch','type'=>2] // set
    ,['idproducttype'=>6]); // where

$dao->from("producttype")
    ->set("name=?",['s','Captain-Crunch']) //set
    ->set("type=?",['i',6]) //set
    ->where('idproducttype=?',['i',6]) // where
    ->update(); // update

or

$dao->from("producttype")
    ->set("name=?",'Captain-Crunch') //set
    ->set("type=?",6) //set
    ->where('idproducttype=?',['i',6]) // where
    ->update(); // update

> Generates the query: update producttype set name=?,type=? where idproducttype=? ....

delete([$table],[$schemaWhere],[$valuesWhere])

Generates a delete command.

$dao->delete("producttype"
    ,['idproducttype','i'] // where
    ,[7]); // where
$dao->delete("producttype"
    ,['idproducttype'=>7]); // where

> Generates the query: delete from producttype where idproducttype=? ....

You could also delete via a DQL builder chain.

$dao->from("producttype")
    ->where('idproducttype=?',['i',7]) // where
    ->delete(); 
$dao->from("producttype")
    ->where(['idproducttype'=>7]) // where
    ->delete(); 

> Generates the query: delete from producttype where idproducttype=? ....

Sequence

Sequence is an alternative to AUTO_NUMERIC field. It uses a table to generate an unique ID. The sequence used is based on Twitter's Snowflake and it is generated based on time (with microseconds), Node Id and a sequence. This generates a LONG (int 64) value that it's unique

Creating a sequence

  • $dao->nodeId set the node value (default is 1). If we want unique values amongst different clusters, then we could set the value of the node as unique. The limit is up to 1024 nodes.
  • $dao->tableSequence it sets the table (and function), the default value is snowflake.
$dao->nodeId=1; // optional
$dao->tableSequence='snowflake'; // optional
$dao->createSequence(); // it creates a table called snowflake and a function called next_snowflake()

Using the sequence

  • $dao->getSequence([unpredictable=false]) returns the last sequence. If the sequence fails to generate, then it returns -1. The function could fails if the function is called more than 4096 times every 1/1000th second.
$dao->getSequence() // string(19) "3639032938181434317" 

$dao->getSequence(true) // returns a sequence by flipping some values.

Creating a sequence without a table.

  • $dao->getSequencePHP([unpredictable=false]) Returns a sequence without using a table. This sequence is more efficient than $dao->getSequence but it uses a random value to deals with collisions.
  • If upredictable is true then it returns an unpredictable number (it flips some digits)
$dao->getSequencePHP() // string(19) "3639032938181434317" 

$dao->getSequencePHP(true) // string(19) "1739032938181434311" 

Changelist

  • 3.30 2020-02-13 Some cleanup.
  • 3.28 2019-05-04 Added comments. Also ->select() allows an entire query.
  • 3.27 2019-04-21 Added new methods of encryption SIMPLE (short encryption) and INTEGER (it converts and returns an integer)
  • 3.26 2019-03-06 Now Encryption has it's own class.
  • 3.25 2019-03-06 Added getSequencePHP(), getUnpredictable() and getUnpredictableInv()
  • 3.24 2019-02-06 Added a new format of date
  • 3.22 2018-12-30 Added sequence
  • 3.21 2018-12-17 Fixed a bug with parameters, set() and insert(). There are several ways to do an insertar. Now NULL is self:null
  • 3.20 2018-12-15 Fixed bug with parameters and insert().
  • 3.19 2018-12-09 Now null parameters are considered null. We use instead PHP_INT_MAX to indicate when the value is not set.
  • 3.18 2018-12-07 Changed minimum stability.
  • 3.17 2018-12-01 set() now allows a single value for the second argument.
  • 3.16 2018-11-03 Added test unit and travis CI.
  • 3.15 2018-10-27
  • * Now it allows multiple select()
  • * function generateSqlFields()
  • 3.14 2018-10-16
  • * Added field throwOnError.
  • * Added more control on the error.
  • * Now methods fails if the database is not open.
  • * Added a container to messages (optional). It works with the function messages()
  • * Added field isOpen
  • * Added method storeInfo()
  • 3.13 2018-10-05 Changed command eval to bind_param( ...)
  • 3.12 2018-09-29 Fixed a bug with insert() it now returns the last identity.
  • 3.11 2018-09-27 Cleaned the code. If it throws an exception, then the chain is reset.
  • 3.9 2018-09-24 Some fixes
  • 3.7 Added charset.
  • 3.6 More fixes.
  • 3.5 Small fixed.
  • 3.4 DML new features. It allows nested operations + ->from()->where()->delete() + ->from()->set()->where()->update() + ->from()->set()->insert()
  • 3.3 DML modified. It allows a different kind of parameters.
  • 3.2 Insert, Update,Delete
  • 3.0 Major overhaul. It adds Query Builder features.
  • 2.6.4 Better correction of error.
  • 2.6.3 Fixed transaction. Now a nested transaction is not nested (and returns a false).
  • 2.6 first public version

  Files folder image Files  
File Role Description
Files folder imageexamples (13 files)
Files folder imagelib (2 files)
Files folder imagetests (2 files)
Accessible without login Plain text file .travis.yml Data Auxiliary data
Accessible without login Plain text file autoload.php Aux. Auxiliary script
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file phpunit.xml Data Auxiliary data
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  examples  
File Role Description
  Plain text file Collection.php Class Class source
  Accessible without login Plain text file medium_code.php Example Example script
  Accessible without login Plain text file testbuilder.php Example Example script
  Accessible without login Plain text file testcatch.php Example Example script
  Accessible without login Plain text file testdb.php Example Example script
  Accessible without login Plain text file testdberror.php Example Example script
  Accessible without login Plain text file testdberrorMessage.php Example Example script
  Accessible without login Plain text file testdbwithdate.php Example Example script
  Accessible without login Plain text file testgenerator.php Example Example script
  Accessible without login Plain text file testinsert.php Example Example script
  Accessible without login Plain text file testselect.php Example Example script
  Accessible without login Plain text file testsequence.php Example Example script
  Accessible without login Plain text file testtable.php Example Example script

  Files folder image Files  /  lib  
File Role Description
  Plain text file DaoOne.php Class Class source
  Plain text file DaoOneEncryption.php Class Class source

  Files folder image Files  /  tests  
File Role Description
  Accessible without login Plain text file bootstrap.php Aux. Auxiliary script
  Plain text file DaoOneTest.php Class Class source

 Version Control Unique User Downloads Download Rankings  
 100%
Total:212
This week:1
All time:8,340
This week:560Up