Modify an existing database column in a Laravel migration

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
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' => 'test@test.com',
					'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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
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' => 'test2@test.com',
				'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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
// $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).

1
2
3
4
5
6
7
8
9
10
11
12
13
 
function pr($str){
 
        echo('<pre>');
        print_r($str);
        echo('</pre >');
 
        $d = debug_backtrace();
 
        echo $d[0]['file'] . '<br />Line: <b>' . $d[0]['line'] . '</b>';
        echo '<hr />';
 
}

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<ul id="menu">
    <li class="first"><a href="/news/q/date/2011/">2011</a></li>
    <li class="current last">
        <a href="/news/q/date/2010/">2010</a>
            <ul>
                <li class=""><a href="/news/q/date/2010/07/">July</a></li>
                <li class=""><a href="/news/q/date/2010/06/">June</a></li>    
                <li class=""><a href="/news/q/date/2010/11/">November</a></li>
                <li class=" last"><a href="/news/q/date/2010/10/">October</a></li>
                <li class=""><a href="/news/q/date/2010/09/">September</a></li>
                <li class=""><a href="/news/q/date/2010/08/">August</a></li>
                <li class="first"><a href="/news/q/date/2010/12/">December</a></li>
                <li class=""><a href="/news/q/date/2010/05/">May</a></li>
           </ul>
    </li>
</ul>

My (woeful) solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
$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 <ul>s
if(isset($trees[0])){
 
	foreach($trees[0] as $var){
 
		// store each node in an indexed array
		$array[$i] = $var; 
		// store the month number in an index array
		// based on the text node value of the <a> tag
		$order[$i] = date('m', strtotime((string) $var->a)); 
 
		$i++;
	}
 
	// sort the month number array descending, but maintaining the keys
	arsort($order); 
 
	// create a new XML Dom object to manipulate stuff
	$dom = new DomDocument();
	// create a holder node <ul>
	$ul = $dom->createElement('ul');
 
	// iterate through the array of simpleXML objects 
	// based on the order in which their keys appear in the re-ordered array
	foreach($order as $key => $value){
		// get the simpleXML objects into a string
		$node = dom_import_simplexml($array[$key]);
		// get the string into an actual DOM node
		$node = $dom->importNode($node, true);
		// append
		$ul->appendChild($node);	
	}
 
	//$dom->appendChild($ul);
 
	// unset the contents of the original <ul> node that we have resorted
	$parent = $trees[0]->xpath( 'parent::*' );
	$parent[0]->ul = NULL;
 
	// turn our simpleXML object into a DOM object
	$ixml = dom_import_simplexml($xml);
	$new = new DOMDocument('1.0');
	$ixml = $new->importNode($ixml, true);
	$ixml = $new->appendChild($ixml);
 
	// fire up a DOM xpath object
	$xpath = new DomXpath($new);
	// pull a node tree out using simpleXML xpath
	$tree = $xpath->query('/ul/li/ul');
 
	// add our newly created DOM node conatining the re-ordered <ul> after the existing node
	$tree->item(0)->parentNode->appendChild($new->importNode($ul, true));
	// delete the original empty node
	$tree->item(0)->parentNode->removeChild($tree->item(0));
 
	echo $new->saveHTML();
 
} else {
 
	echo $string;
 
}