Insert Data with Phinx
Database patching is a wicked hard problem, one that’s got a bit easier in my world lately as I’ve been using Phinx on a few projects. I like Phinx because it avoids the numbered-patches problem by using date stamps as part of its patch naming and it is pretty smart about creating the correct forward and backward changesets from a single
change() description.
One thing I didn’t immediately find was how to insert data. Phinx has seed functionality but in this case I needed to put in a lookup table to go along with a data structure change. It’s actually simple to do so here’s the example (so I can find it again!):
$roles_table = $this->table('roles'); $roles_table // id created automatically ->addColumn('name', 'string') ->create(); $roles_table->insert(["name"], [["admin"]]); $roles_table->saveData();
The only gotcha here is that when changing a table structure you call save() and when saving data you call saveData(). The seed data is ideal for content, but if you do want to insert data along with a structure change, this is a good way to do it.
In version 0.5.0 Phinx. [code]->insert($aData)[/code] method expecting only one arguement.
http://docs.phinx.org/en/latest/seeding.html#using-the-table-object
Thanks for this – I didn’t know about the insert() method – guess that must be fairly new? I have been doing $this->query() and using raw SQL which isn’t ideal!
How do you tell if an ‘up’ or ‘down’ operation is happening when you only implement the ‘change’ method?
When you run the migration, the change function runs. When you run the rollback then phinx uses the change method to work out how to run the equivalent of the down method.
Having revisited this example today because I was struggling to insert data, it’s nothing like detailed enough!
The arguments to insert() are 1) an array of the columns to insert data into and 2) an array of arrays where the inner arrays each represent a row.
Yes the docs are not as clear as it should be.
From my own work on this what I’ve figured out is that your ‘change’ method has to be able to work in an ‘up’ scenario regardless of the circumstances. Phinx determines the ‘down’ from there. In a sense the ‘up’ changes have to be idempotent.
For example I had a migration that required inserting rows in some tables.
I ended up doing it like this:
[code]
if ($this->hasTable(‘log_type’)) {
$this->execute(‘DELETE FROM log_type WHERE id IN (1,2,3,4)’);
$rows = [
[1, ‘Notice’, ‘notice’],
[2, ‘Warning’, ‘warning’],
[3, ‘Error’, ‘error’],
[4, ‘Success’, ‘success’]
];
$this->tables[‘log_type’]
->insert([‘id’, ‘name_’, ‘slug’], $rows)
->saveData();
}
[/code]
My apologies if that’s not easily readable.
How can I get last inserted ID in Phinx?
I found something like this $this->adapter->getConnection()->lastInsertId(); but it was at 2014 so is there a handler to do it in some simpler way?