Posts Tagged ‘MySQL’

Haven’t updated the blog for a while so I thought I would create a few posts on what I have been up to in the early stages of 2009!

Back in February, I developed a microsite for Dr Martens, named ‘Dr Martens For Life’. The whole idea behind the site is to activate your guarantee for your Dr Martens Boots / Shoes for life!

Using CakePHP, I did some lovely custom validation to check if a guarantee code was valid (from a list of codes in a MySQL table) and also to see if it had not been redeemed before (only one code per product).

Take a look, if you fancy buying a pair of Dr Martens - take a look here too.

Looking for a special gift for someone but cannot think of what?

Well shhh.tv is here to sort that!! simply, answer a few questions and generate a gift for a friend or foe… its a shhhhhecret!

shhh.tv is a MAKE project for LOVE.

I am proud to say that I built the ‘back-end’ of the site…. ooooh err, using the CakePHP framework, MySQL and AMFPHP.

Enjoy and Merry Christmas!

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;

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;