A MySQL Query to Replace a Character in a String

I wrote an import script for our old application to the new application database recently. While testing the import today, I noticed that a certain field used comma delimiters in the old database, but used ||| in the new, and my conversion script hadn't taken that into account. I needed a quick way to replace all the commas that came over in the input with pipes. Here is the query that did it for me:

UPDATE `table` SET fieldname = REPLACE(fieldname,',','|||');

Worked like a charm.

Unit Testing in Cake PHP 2.0

I had some trouble getting Unit Testing working with Cake PHP 2.0. But, after quite a bit of time of tinkering, I got it working.

1. The first step is to get PHP Unit installed. I used Pear, which worked with no problems. These Instructions worked for me on Windows.

2. I am using the latest version of Cake PHP 2.0 from git. Setting that up is essentially the same as setting up Cake PHP 1.3.x

3. I added a simple User model with the following table structure:


CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` char(50) DEFAULT NULL,
`password` char(40) DEFAULT NULL,
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


4. My goal for this first unit test was simple - get a unit test running and make it assert some trivial truth. I grabbed an example unit test from Cake PHP 1.3.x and tweaked it. And, this test should hopefully work for you as well to help establish that you have your environment working and so you can see the new naming conventions and directory structure.

In app/Test/Fixture, I created a file UserFixture.php with the following contents:

<?php



class UserFixture extends CakeTestFixture {



/**

 * name property

 *

 * @var string 'User'

 * @access public

 */

	public $name = 'User';



/**

 * fields property

 *

 * @var array

 * @access public

 */

	public $fields = array(

		'id' => array('type' => 'integer', 'key' => 'primary'),

		'username' => array('type' => 'string', 'null' => false),

		'password' => array('type' => 'string', 'null' => false),

		'created' => 'datetime',

		'updated' => 'datetime'

	);



/**

 * records property

 *

 * @var array

 * @access public

 */

	public $records = array(

		array('username' => 'mariano', 'password' => '5f4dcc3b5aa765d61d8327deb882cf99', 'created' => '2007-03-17 01:16:23', 'updated' => '2007-03-17 01:18:31'),

		array('username' => 'nate', 'password' => '5f4dcc3b5aa765d61d8327deb882cf99', 'created' => '2007-03-17 01:18:23', 'updated' => '2007-03-17 01:20:31'),

		array('username' => 'larry', 'password' => '5f4dcc3b5aa765d61d8327deb882cf99', 'created' => '2007-03-17 01:20:23', 'updated' => '2007-03-17 01:22:31'),

		array('username' => 'garrett', 'password' => '5f4dcc3b5aa765d61d8327deb882cf99', 'created' => '2007-03-17 01:22:23', 'updated' => '2007-03-17 01:24:31'),

	);

}

5. In app/Test/Case/Model I created the file UserTest.php with the following contents:

<?php

App::import('Model', 'User');

class UserTestCase extends CakeTestCase

{

    public $fixtures = array('user');

    public $User;





    public function setUp()

    {

        $this->User =& ClassRegistry::init('User');



    }



    public function testGetInstance()

    {





        $created = $this->User->field('created', array('User.username' => 'mariano'));

        $this->assertEquals($created, '2007-03-17 01:16:23', 'Created Date');



    }

}



?>

6. Running the test was as simple as in Cake 1.3.x - Go to your application and call test.php. Select App/Tests and run your User Test Case.