Dev.Module Database

Database queries

Pi have encapsulated a class for user to operate tables of the installed database. And the API is in Pi.php file, so you should include its namespace:

use Pi;

Getting table instance

The Pi class provides us a method named model to fetch a table instance, but this method can only operate database of Pi. This method takes two parameters, the first one is the name of table, and the second is the name of module which can be ignored. This method finally return a Pi\Application\Model\ModelAbstract instance.

$model = Pi::model('user');
$model = Pi::model('login/user');
$model = Pi::model('user', 'login');

Supposing the prefix of table is xe, and current module is login, hence, the first line will return an instance which relates to table xe_core_user. The xe_core_user table is a system table. The second line and third line will return same result, and its table operated is xe_login_user.

The other method to operate table is getModel(), but this method can not operate tables create by system. For example, if you want to operate table xe_login_user, using the follow code:

$model = $this->getModel('user');

Note: we recommend you to use getModel() to fetch current module tables when codes, because the installed module name will change when the module is installed multi-times.

Select

Pi provides us a select() method to fetch data from table, this method inherits from select() method of Zend.

$select = $model->select();
$rowset = $model->selectWith($select);

$select = $model->select()->where(array('username' => 'root'));
$rowset = $model->selectWith($select);

// Alternative
$rowset = $model->select(array('username' => 'root'));

In the code, select() method is used to select data from table. The parameter of it is the condition, which represent by array.

The first block will return all data of table, the second and third block has the same effect, which will return rows that username equal to root. Here is some example of writing where array:

// 'username' != 'root'
$rowset = $model->select(array('username != ?' => 'root'));
$rowset = $model->select(array('username <> ?' => 'root'));
// 'age' > '23'
$rowset = $model->select(array('age > ?' => '23'));
// 'age' <= '45'
$rowset = $model->select(array('age <= ?' => '23'));

Using SQL IN:

$ids = array('1', '2', '4', '7');
$rowset = $model->select(array('id' => $ids));

This code will select the id equal to anyone in the $ids array.

Using order, limit:

$select = $model->select()->where(array('username' => 'root'))
                        ->order(array('username ASC'));
$rowset = $model->selectWith($select);

$select = $model->select()->where(array('username' => 'root'))
                        ->offset(1)
                        ->limit(20);
$rowset = $model->selectWith($select);

Using group:

$select = $model->select()->group(array('name', 'id'));
$select = $model->select()->group('name');

Selecting columns:

$select = $model->select()->where(array('username' => 'root'))
                        ->columns(array('username', 'age', 'email'));
$rowset = $model->selectWith($select);

Removing the repeat data when selects:

$select = $model->select()->where()->columns(array('username' => new \Zend\Db\Sql\Expression('distinct username')));
$rowset = $model->selectWith($select);

This method only can remove single field, if you add another field such as id and it does not have repeat value, this method will return all rows of field username and id.

For example, there is a table such as:

id	username	gender	age
1	root	male	23
2	root	female	45

If you use the following code to select data:

$select = $model->select()
                ->where()
                ->columns(array('username' => new \Zend\Db\Sql\Expression('distinct username'), 'id', 'gender'));
$rowset = $model->selectWith($select);

It will return:

id	username	gender
1	root	male
2	root	female

You may find there has repeat username root, if you want to remove the repeat data, using group() method as follows:

$select = $model->select()
                ->where()
                ->columns(array('*'))
                ->group('username');
$rowset = $model->selectWith($select);

Select from multi-table

Zend provides us a method call join() to join another table to compact.

$select = $select->join(array('abbreviation table name' => 'table full name'), 'where string');

In Pi, we can use $model->select() object to call the method.

$model = $this->getModel('table1');
$table1 = $model->getTable();
$table2 = $this->getModel('table2')->getTable();

$select = $model->select()->join(array('table2' => $table2), 'table2.element = ' . $table1 . '.element');
$rowset = $model->selectWith($select);

$data = array();
$data = $rowset->toArray();

The third parameter of join() method is the columns of the joined table to select, if you use $select() to select columns, it will select the elements of original table.

$model = $this->getModel('userid');
$userid = $model->getTable();
$userinfo = $this->getModel('userinfo')->getTable();

$select = $model->select()->join(array('info' => $userinfo), 'info.username = ' . $userid . '.username', array('email'));
$select->columns(array('id'));
$rowset = $model->selectWith($select);  

The SQL statement of this code will be as same as:

'SELECT userid.id AS id, userinfo.email AS email FROM userid INNER JOIN userinfo AS info ON info.username = userid.username'

Insert

Now we have a model instance, it can be used to call methods for inserting, updating and deleting data. The following codes will insert data into table.

$row = $this->getModel('userinfo')->createRow($data);
$row->save();
if (!$row->id) {
    return false;
}

In the codes, $data parameter of createRow() method is an array which contain data to insert, the key of the array is same as that of table:

$data = array(
    'name'      => 'John',
    'password'  => $password,
);

Then a save() method is called to insert data into table, and finally use $row->id to assure the data is insert correctly.

Update

The save() method also allow user to update data of tables. But something should be done before using save() method. Such as fetching the id of the data you want to update and getting instance of the rowset.

Supposing you create fields id, username and password in your table userinfo, you want to change the password according to username field.

// fetching id from table by username
$model = $this->getModel('userinfo');
$rowset = $model->select(array('username' => 'root'));
foreach ($rowset as $row) {
    $id = $row['id'];
}

// updating password
$row = $model->find($id);
$row->password = $password;
$row->save();

Certainly you can also use a simple method update() to update data.

$model->update(array('password' => $password), array('username' => 'root'));

Delete

In Pi, delete() method is used to delete data, this method also need you to call find() method.

$row = $this->getModel('userinfo')->find($id);
$row->delete();

You can also use delete() method provide by Zend:

$this->getModel('userinfo')->delete(array('id' => $id));