Archive for November, 2008

Last night LOVE were nominated for the “Freshest Digital Campaign” for the work carried out on Warburtons We Love Bread website.

I am glad to say we won! Well done to all those involved, including: Adam, Jim and Stu.

I have just been refactoring a legacy database and needed to update some data in one table from another table in MySQL.

In this case the 2 tables had the same values for the identity colomn.

Syntax:

UPDATE tablename1 t1
SET colomn1 = (SELECT colomn1 from tablename2 t2 WHERE t1.id = t2.id);

For example

If i had tables: ‘products’ and ‘merchandise’ and needed to copy the product title into the merchandise title colomn, I would simply:

UPDATE merchandise m
SET title = (SELECT title from products p where m.id = p.id);

I have just been refactoring a legacy database and needed to copy some data from one table into a new table in MySQL.

Very easy task but very easily forgotten, just thought I would blog about it to help anyone searching for a quick answer…

Syntax:

INSERT INTO tablename (colomn1, colomn2 ...)
SELECT colomn1, colomn2 ...
FROM tablename;

For example

If i had a products table and needed to copy the product title into another table named ‘merchandise’ I would simply:

INSERT INTO merchandise (title)
SELECT title FROM products;

On Thursday night LOVE will be attending the Fresh Digital Awards 2008, unfortunately I won’t be there due to other commitments. We are nominated for 1 award - ‘Freshest Digital Campaign‘ for the work I was involved with for Warburtons ‘National Bread Week‘.

Here’s hoping that we scoop another award for this lovely website!

Something I seem to do quite regularly at the moment is resetting the auto increment value for a table in a MySQL database.

Really easy to do but also very easy to forget the syntax:

ALTER TABLE tablename AUTO_INCREMENT = value;

For example
If I had a products table and I created a few new products and then deleted them, to set the auto increment value back to ‘53′ i would simply:

ALTER TABLE products AUTO_INCREMENT = 53;

CakePHP’s validation class is pretty powerful, however there may be a time when you require a validation method that hasn’t been defined, this is where custom validation rules fit in.

Lets imagine you have a select box containing a users ‘title’ field in a form. You also have a ‘other_title’ text field which must be filled in if the ‘Other’ value is selected.

How would you go about validating this?

Simple: In your Model specify your validation criteria as so…

var $validate = array(
'title' => array(
'rule' => array('minLength',1),
'message' => 'Please select your title'
),
'other_title' => array(
'rule' => array('_compareFields', 'title', 'other'),
'message' => 'Please enter your other title'
)
);

As you can see when the Model validates, the ‘other_title’ field is validated against a custom rule named ‘_compareFields’ parsing in ‘title’ and ‘other’ as parameters.

‘title’ is the field to compare ‘title_other’ with and ‘other’ is the compare value that I am matching on.
Therefore the custom rule looks like:

function _compareFields($field=array(), $compare_field, $compare_value){
if(!empty($this->data[$this->name][$compare_field])&&(strtolower($this->data[$this->name][$compare_field]) == strtolower($compare_value))) {
$field_value = array_shift($field);
if(!empty($field_value)) {
return true;
} else {
return false;
}
} else {
return true;
}
}

I could of simply used ‘Other’ as the $compare_value but I like writing generic functions that can be re-used multiple times. This function will now cater for other fields with different $compare_fields and the ‘case’ of this value is not sensitive in the view.

Hope that helps someone out there!