Modify an existing database column in a Laravel migration

Note: This post is over a year old and the content may no longer be accurate.

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?

Note: This post is over a year old and the content may no longer be accurate.

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

Note: This post is over a year old and the content may no longer be accurate.

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

Note: This post is over a year old and the content may no longer be accurate.

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;
 
}