Using column names as value for row in SQL

Question

I have the following sql script:

CREATE TABLE  `akalogixDB`.`users` (
`user_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`priv_id` INT NOT NULL DEFAULT  '3',
`username` VARCHAR( 40 ) NOT NULL ,
`password` VARCHAR( 40 ) NOT NULL ,
`email` VARCHAR( 40 ) NOT NULL ,
`first_name` VARCHAR( 40 ) NULL ,
`last_name` VARCHAR( 40 ) NULL ,
`avatar` VARCHAR( 100 ) NULL DEFAULT 'images/avatars/no_avatar.jpg',
`mini_avatar` VARCHAR( 100 ) NULL DEFAULT 'images/avatars/no_avatar_mini.jpg',
`about` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL ,
`user_folder` VARCHAR(100) NOT NULL DEFAULT ====> #here is the problem
UNIQUE (
`username` ,
`email`)
) ENGINE = MYISAM ;

I would like to give the default value for user_folder the following: 'users/folders/'+user_id + current timestamp How can I do this, I know that I can do it with PHP, but is there way of doing with sql instructions.

So you're saying that the default value of user_folder should be something like users/folders/123/2006-02-22 16:26:08.037. The value 123 is the auto-incremented value of the user_id. — p.campbell
– p.campbell, Commented Aug 15, 2011 at 17:54

Code Magician · Accepted Answer · 2011-08-15 17:57:11Z

1

You could use a trigger. Something like:

CREATE TRIGGER `before_users_insert`
    BEFORE INSERT ON `users` FOR EACH ROW
    BEGIN
        IF NEW.user_folder IS NULL THEN
            SET NEW.user_folder = CONCAT('users/folders/',NEW.user_id,  UNIX_TIMESTAMP());
        END IF;
    END

You'd need to figure out what value would trigger your default value as a NULL (as I've coded it) may not work because you have a NOT NULL on that column. It's a sound approach though and I hope this gives you the general idea.

answered Aug 15, 2011 at 17:57

Code Magician

24.1k7 gold badges63 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bpgergo · Accepted Answer · 2011-08-15 17:58:04Z

0

What you'll need is a triger: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html try something like this:

create trigger t_users_bi 
before insert on users 
for each row BEGIN
   SET new.user_folder = CONCAT('users/folders/', new.user_id, current_timestamp);
END;

answered Aug 15, 2011 at 17:58

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

Collectives™ on Stack Overflow

Using column names as value for row in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related