Page Contents

Executing Queries

Queries can be executed with the execute() and query() methods. The execute() method returns the number of affected rows whereas the query() method returns the result as a CakePHP Statement. Both methods accept an optional second parameter $params which is an array of elements, and if used will cause the underlying connection to use a prepared statement:

<?php

use Migrations\BaseMigration;

class MyNewMigration extends BaseMigration
{
    /**
     * Migrate Up.
     */
    public function up(): void
    {
        // execute()
        $count = $this->execute('DELETE FROM users'); // returns the number of affected rows

        // query()
        $stmt = $this->query('SELECT * FROM users'); // returns PDOStatement
        $rows = $stmt->fetchAll(); // returns the result as an array

        // using prepared queries
        $count = $this->execute('DELETE FROM users WHERE id = ?', [5]);
        $stmt = $this->query('SELECT * FROM users WHERE id > ?', [5]); // returns PDOStatement
        $rows = $stmt->fetchAll();
    }

    /**
     * Migrate Down.
     */
    public function down(): void
    {

    }
}

Fetching Rows

There are two methods available to fetch rows. The fetchRow() method will fetch a single row, whilst the fetchAll() method will return multiple rows. Both methods accept raw SQL as their only parameter:

<?php

use Migrations\BaseMigration;

class MyNewMigration extends BaseMigration
{
    /**
     * Migrate Up.
     */
    public function up(): void
    {
        // fetch a user
        $row = $this->fetchRow('SELECT * FROM users');

        // fetch an array of messages
        $rows = $this->fetchAll('SELECT * FROM messages');
    }

    /**
     * Migrate Down.
     */
    public function down(): void
    {

    }
}

Inserting Data

Migrations makes it easy to insert data into your tables. Whilst this feature is intended for the seed feature, you are also free to use the insert methods in your migrations:

<?php

use Migrations\BaseMigration;

class NewStatus extends BaseMigration
{
    /**
     * Migrate Up.
     */
    public function up(): void
    {
        $table = $this->table('status');

        // inserting only one row
        $singleRow = [
            'id'    => 1,
            'name'  => 'In Progress'
        ];

        $table->insert($singleRow)->saveData();

        // inserting multiple rows
        $rows = [
            [
              'id'    => 2,
              'name'  => 'Stopped'
            ],
            [
              'id'    => 3,
              'name'  => 'Queued'
            ]
        ];

        $table->insert($rows)->saveData();
    }

    /**
     * Migrate Down.
     */
    public function down(): void
    {
        $this->execute('DELETE FROM status');
    }
}

Note

You cannot use the insert methods inside a change() method. Please use the up() and down() methods.