nix, shell, perl, php, mysql and mac os x tips and tricks

Thursday, February 26, 2009

Select and recurse thru an entire category tree in mysql (this is 5 levels deep)

SELECT
root.category_code AS root_category_code,
root.category_name AS root_category_name,
sub1.category_code AS sub1_category_code,
sub1.category_name AS sub1_category_name,
sub2.category_code AS sub2_category_code,
sub2.category_name AS sub2_category_name,
sub3.category_code AS sub3_category_code,
sub3.category_name AS sub3_category_name,
sub4.category_code AS sub4_category_code,
sub4.category_name AS sub4_category_name
FROM categories_loop AS root
LEFT JOIN categories_loop AS sub1 ON sub1.category_parent = root.category_code
LEFT JOIN categories_loop AS sub2 ON sub2.category_parent = sub1.category_code
LEFT JOIN categories_loop AS sub3 ON sub3.category_parent = sub2.category_code
LEFT JOIN categories_loop AS sub4 ON sub4.category_parent = sub3.category_code
WHERE root.category_parent = '0'
ORDER BY 
root_category_code, 
sub1_category_code, 
sub2_category_code, 
sub3_category_code,
sub4_category_code

Tuesday, February 24, 2009

Show a table structure, with comments in MySQL

SHOW FULL COLUMNS FROM myTable

Wednesday, February 4, 2009

Set auto-incement value in MySQL

ALTER TABLE tbl AUTO_INCREMENT = 1000;