/Yodao

A Lazy Dao for mysql, pgsql and mssql.

Primary LanguagePHP

Yodao

A Lazy Dao for MySQL (will support PgSQL and MSSQL in the future).

Example

Database and table

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)

Init

include 'Yodao.php';
$dao = new Yodao\DB("mysql:dbname=yodao;host=127.0.0.1", 'root', '');

Specify a table

$tblDao = $dao->table('users');

Select all fields

$ret = $tblDao->select(
    '*',  // select fields
    'name=:name', // where conditions
    ['name' => 'youwei'] // condition vars binding
);

Select specified fields

$ret = $tblDao->select(['id', 'name', 'age'], 'name=:name', ['name' => 'youwei']);

Select rows by specified ids

$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
);

Select One

$ret = $tblDao->selectOne('*', 'name=:name', ['name' => 'youwei']);
$ret = $tblDao->selectOne(['id', 'name', 'age'], 'name=:name', ['name' => 'youwei']);

Insert

$insertId = $tblDao->insert(
    [
        'name' => 'test user',
        'age' => 20,
        'create_time' => time()
    ]
);

Update

$tblDao->update(
    [ // update fields
        'name' => 'updated user',
        'age' => 30,
    ],
    'id=:id', // where condition
    ['id' => $insertId] // condition vars binding
);

Insert Or update

$ret = $tblDao->insertOrUpdate(
    [ // insert fields
        'id' => $insertId, 
        'name' => 'new user',
        'age' => 10,
        'create_time' => time(),
    ],
    [ // update fields if the row is duplicated
        'age' => 11,
    ]
);

Delete rows

$tblDao->delete(
    'id=:id or name=:name',  // where conditions
    [ // condition vars binding
        'id' => 100, 
        'name' => 'new user'
    ]
);

Insert From Select

$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
);

Insert Multiple Rows

$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(),
    ]
);