Download .zip |
Info | View files (23) | Download .zip | Reputation | Support forum | Blog | Links |
Last Updated | Ratings | Unique User Downloads | Download Rankings | |||||
2020-03-28 (19 hours ago) | Not yet rated by the users | Total: 140 This week: 6 | All time: 8,906 This week: 56 |
Version | License | PHP version | Categories | |||
daoone 1.0 | MIT/X Consortium ... | 5 | PHP 5, Databases |
Description | Author | |
This class can access MySQL databases using the MySQLi extension. |
|
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
Install via composer
> composer require eftec/pdoone
This library could works in tandem with eftec/daoone.
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;
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'
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;
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();
>
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
Just download the file lib/DaoOne.php and save it in a folder.
$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
$sql="CREATE TABLE `product` (
`idproduct` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
PRIMARY KEY (`idproduct`));";
$dao->runRawQuery($sql);
$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--)
$dao->runRawQuery('insert into `product` (name) values(?)'
,array('s','cocacola'));
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.
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);
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.
}
It starts a transaction
It commits a transaction. * If $throw is true then it throws an exception if the transaction fails to commit. Otherwise, it does not.
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.
If true (default), then it throws an error if happens an error. If false, then the execution continues
It is true if the database is connected otherwise,it's false.
You could also build a procedural query.
Example:
$results = $dao->select("*")->from("producttype")
->where('name=?', ['s', 'Cocacola'])
->where('idproducttype=?', ['i', 1])
->toList();
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 ....
Generates a select command.
$results = $dao->select("col1,col2")->distinct()...
> Generates the query: select distinct col1,col2 ....
>Note: ->distinct('unique') returns select unique ..
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')...
Generates a where command.
$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)*
Generates a order command.
$results = $dao->select("*")
->from('table')
->order('p1 desc')...
> Generates the query: select from tableorder by p1 desc*
Generates a group command.
$results = $dao->select("*")
->from('table')
->group('p1')...
> Generates the query: select from tablegroup by p1*
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')
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.)
It's a macro of runGen. It returns an associative array or null.
$results = $dao->select("*")
->from('table')
->toList()
It's a macro of runGen. It returns a mysqli_result or null.
$results = $dao->select("*")
->from('table')
->toResult()
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()
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.
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.
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]);
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(?,?,?) ....
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
=? ....
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 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
$dao->nodeId=1; // optional
$dao->tableSequence='snowflake'; // optional
$dao->createSequence(); // it creates a table called snowflake and a function called next_snowflake()
$dao->getSequence() // string(19) "3639032938181434317"
$dao->getSequence(true) // returns a sequence by flipping some values.
$dao->getSequencePHP() // string(19) "3639032938181434317"
$dao->getSequencePHP(true) // string(19) "1739032938181434311"
Files |
File | Role | Description | ||
---|---|---|---|---|
examples (13 files) | ||||
lib (2 files) | ||||
tests (2 files) | ||||
.travis.yml | Data | Auxiliary data | ||
autoload.php | Aux. | Auxiliary script | ||
composer.json | Data | Auxiliary data | ||
LICENSE | Lic. | License text | ||
phpunit.xml | Data | Auxiliary data | ||
README.md | Doc. | Documentation |
Files | / | examples |
File | Role | Description |
---|---|---|
Collection.php | Class | Class source |
medium_code.php | Example | Example script |
testbuilder.php | Example | Example script |
testcatch.php | Example | Example script |
testdb.php | Example | Example script |
testdberror.php | Example | Example script |
testdberrorMessage.php | Example | Example script |
testdbwithdate.php | Example | Example script |
testgenerator.php | Example | Example script |
testinsert.php | Example | Example script |
testselect.php | Example | Example script |
testsequence.php | Example | Example script |
testtable.php | Example | Example script |
Files | / | lib |
File | Role | Description |
---|---|---|
DaoOne.php | Class | Class source |
DaoOneEncryption.php | Class | Class source |
Files | / | tests |
File | Role | Description |
---|---|---|
bootstrap.php | Aux. | Auxiliary script |
DaoOneTest.php | Class | Class source |
Version Control | Unique User Downloads | Download Rankings | |||||||||||||||
100% |
|
|
Applications that use this package |
If you know an application of this package, send a message to the author to add a link here.