Modify an existing database column in a Laravel migration

Update

The information in this post is no longer correct.

It is now possible to update columns using migrations by adding the Doctrine DBAL library to your Laravel site. https://laravel.com/docs/5.6/migrations#modifying-columns

p.s. Current Laravel is a joy to work with – I am most definitely now a fan :)

I’ve been messing around with Laravel – the ‘Clean & Classy PHP Framework’… I’m not totally sold yet, but there is a lot to like, so I am persevering.

One useful feature that Laravel includes is migrations – in essence the ability to place your database schema under version control along with your code.

Laravel handles migrations though it’s CLI ‘Artisan

Rather inexplicably (to me at least*) the migrations tool – Schema Builder does include the ability to modify columns once they have been created, however you can use use raw SQL as a workaround.

*Schemas evolve. I find myself often adding columns, tweaking existing columns regularly and dropping columns rather rarely…

Let’s build some migrations…

First things first – create the migrations table. This is used to keep track of migration within your application.

1. Open a terminal in the root of your application.

2. Create the migrations table:

php artisan migrate:install
 

3. Create an empty migrations script

php artisan migrate:make create_users
 

create_users is simply a descriptive name for your migration – you can call it anything you want…

This will create a skeleton file in /path_to_laravel/application/migrations that includes the timestamp in it’s name e.g.

2013_05_23_201939_create_users.php

class Create_Users {

	/**
	 * Make changes to the database.
	 *
	 * @return void
	 */
	public function up()
	{
		//
	}

	/**
	 * Revert the changes to the database.
	 *
	 * @return void
	 */
	public function down()
	{
		//
	}

}
 

This isn’t much use – we need to:

4. Write our migration – edit the file that we just created. Create the table and insert some test data.

class Create_Users {

	/**
	 * Make changes to the database.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('users',function($table){
			$table->increments('id');
			$table->string('username', 20); 
			$table->string('email', 255); 
			$table->string('password', 255); 
			$table->boolean('active');
			$table->timestamps(); 
		});
		
		DB::table('users')->insert(array(
					'username' => 'admin',
					'email' => '[email protected]',
					'password' => Hash::make('123456'),
					'active' => 1
				
		));
	}

	/**
	 * Revert the changes to the database.
	 *
	 * @return void
	 */
	public function down()
	{
		//
	}
 

5. Run the migration:

php artisan migrate
 

6. Ooops – forgotten to set a default on one of the fields.

Unfortunately you can’t change this via the Schema Builder, but we can still use another migration:

php artisan migrate:make update_users
 

This will create a file called something like 2013_05_23_202930_update_users.php

7. We need to write our migration. This needs to be actual SQL which we call with DBquery e.g.

class Modify_Users {

	/**
	 * Make changes to the database.
	 *
	 * @return void
	 */
	public function up()
	{
		DB::query("ALTER TABLE `ltest`.`users` CHANGE COLUMN `active` `active` tinyint(1) NOT NULL DEFAULT '1';");
		
		DB::table('users')->insert(array(
				'username' => 'test2',
				'email' => '[email protected]',
				'password' => Hash::make('123456'),
				
		
		));
	}

	/**
	 * Revert the changes to the database.
	 *
	 * @return void
	 */
	public function down()
	{
		//
	}

}

I’ll insert another user and see if the default for the ‘active’ column is set correctly.

8. Run the migrations again.

php artisan migrate
 

Done!

XPath to the rescue. Again?

It’s odd. I don’t think about think about XPath from one month to the next. But once in a while, when my usual solutions have all come up blank. Ta-Da XPath to the rescue!

Recently as part of a site I was working on, the design basically required that I inject a block of content into a nested list (part of an elaborate menu actually) – bit of a fiddle because modifying the code that generated the list was not an option.

My first avenues of attack were just simple str_replace() and a regex replace, but I just couldn’t get it work consistently – there were two many variables – additional attributes, white-space etc. The two constants were that the code fragment validated as xhtml (hence xml) and I would always have a class that I could use as a hook.

// $menu - fragment of html consisting of nested UL's 

$xml = simplexml_load_string($menu);
                                                
$nodes = $xml->xpath('//*[@class = "current"]');

if(!empty($nodes)){

	$nodes[0]->addChild('div', 'text_to_replace');
	$menu = $xml->asXML();
	echo str_replace('text_to_replace', $str, $menu);
		
} else {
	echo $menu;
}

//*[@class = "current"] finds nodes at any level with where the class attribute contains ‘current’.

node[0] is the first instance of a node with this attribute and inject some place-holder text.

$menu = $xml->asXML(); our modified list.

Hey presto!

Quick debug function

It’s not pretty and it’s not elegant, but we’ve all got a bag of quick and dirty functions that help make life just a little bit easier.

This is a simple print_r() but I finally got fed up of loosing my quick debug statements and so added the line name and file number from a stack trace (substitute var_export() to taste).

function pr($str){
        
        echo('
');
        print_r($str);
        echo('
'); $d = debug_backtrace(); echo $d[0]['file'] . '
Line: ' . $d[0]['line'] . ''; echo '
'; }

Reorder a nested HTML list in PHP

Recently I was working on website where I had to re-order a nested list (part of a navigation menu) – unfortunately I only had access to fragment of HTML so I couldn’t just manipulate the arrays from which it was built. The menu was compiled from various arrays and months within years sometimes came out all wrong.

So I thought, I’d just treat it as a bit of XML (which obviously it is) and re-order it using PHPs native XML handling classes. XML is one of those things that I use frequently, but never really do anything with, and finding a solution took me rather longer than I had expected. It is a mixture of simpleXML and XMLDom.

One of the main problems was the lack any real examples.

If anyone can suggest a more elegant solution, I would love to hear it.

Here is my code:

The UL to re-order

As you can see the month names are in the wrong order



My (woeful) solution

$xml = simplexml_load_string($string);
	
// pull a node tree as an Array out using simpleXML xpath
$trees = $xml->xpath('/ul/li/ul');

$array = array();
$order = array();

$i = 0;

// we only need to delve into XML if there are any nested