A Lazy Dao for MySQL (will support PgSQL and MSSQL in the future).
mysql -h127.0.0.1 -uroot
use yodao;
desc users;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| age | int(10) unsigned | NO | | NULL | |
| create_time | int(10) unsigned | NO | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
include 'Yodao.php';
$dao = new Yodao\DB("mysql:dbname=yodao;host=127.0.0.1", 'root', '');
$tblDao = $dao->table('users');
$ret = $tblDao->select(
'*', // select fields
'name=:name', // where conditions
['name' => 'youwei'] // condition vars binding
);
$ret = $tblDao->select(['id', 'name', 'age'], 'name=:name', ['name' => 'youwei']);
$ret = $tblDao->select(
['id', 'name', 'age'], // fields
'[id in :idList]', // where conditions, like 'id in (1,2,3)', 'id' is the column, ':idList' is the variable
['idList' => [1, 2, 3]] // bind 'idList' variable
);
$ret = $tblDao->selectOne('*', 'name=:name', ['name' => 'youwei']);
$ret = $tblDao->selectOne(['id', 'name', 'age'], 'name=:name', ['name' => 'youwei']);
$insertId = $tblDao->insert(
[
'name' => 'test user',
'age' => 20,
'create_time' => time()
]
);
$tblDao->update(
[ // update fields
'name' => 'updated user',
'age' => 30,
],
'id=:id', // where condition
['id' => $insertId] // condition vars binding
);
$ret = $tblDao->insertOrUpdate(
[ // insert fields
'id' => $insertId,
'name' => 'new user',
'age' => 10,
'create_time' => time(),
],
[ // update fields if the row is duplicated
'age' => 11,
]
);
$tblDao->delete(
'id=:id or name=:name', // where conditions
[ // condition vars binding
'id' => 100,
'name' => 'new user'
]
);
$ret = $tblDao->insertFromSelect(
[ // fields want to copy
'name' => 'youwei updated',
'age' => '.', // '.' means copy the same value from 'source table'
'create_time' => '.',
],
'age=:age',
['age' => 30],
'', // source table, use the same table if omit
2, // limit
0 // offset
);
$tblDao->insertMulti(
[
[ // row 1
'name' => 'youwei1',
'age' => '10',
],
[ // row 2
'name' => 'youwei2',
'age' => '20',
],
],
[ // these fields will merge into every rows above.
'create_time' => time(),
]
);