concat problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
zuzupus
Forum Commoner
Posts: 70
Joined: Thu Jul 17, 2003 4:54 am

concat problem

Post by zuzupus »

hi,
i got problem regarding concatination as i created new column called diradress and now i want to fetch value somethign like this

PARENT1 (pk_dir = 1, diradress= 1)
__CHILD1 (pk_dir = 23, diradress= 1|23)
_____GRANDCHILD11 (pk_dir = 345, diradress= 1|23|345)
_____GRANDCHILD12 (pk_dir = 346, diradress= 1|23|346)
_____GRANDCHILD13 (pk_dir = 347, diradress= 1|23|347)
__CHILD2 (pk_dir = 35, diradress = 1|35)
_____GRANDCHILD21 (pk_dir = 456, diradress= 1|35|456)
_____GRANDCHILD22 (pk_dir = 457, diradress= 1|35|457)
__CHILD3 (pk_dir = 46, diradress = 1|46)
_____GRANDCHILD31 (pk_dir = 506, diradress= 1|46|506)
________GRANDGRANDCHILD311 (pk_dir = 657, diradress= 1|46|506|657)

below query will be more clear and understandable



CREATE TABLE t_directories (
sys_pk int(11) NOT NULL auto_increment,
sys_del char(1) NOT NULL default 'f',
sys_state tinyint(2) NOT NULL default '0',
sys_dlm timestamp(14) NOT NULL,
sys_klm int(11) NOT NULL default '0',
sys_doc timestamp(14) NOT NULL,
sys_koc int(11) NOT NULL default '0',
fk_t_directories_parent int(11) NOT NULL default '0',
name varchar(50) default NULL,
sort smallint(4) NOT NULL default '0',
bild_path varchar(250) NOT NULL default '',
bild_width smallint(4) NOT NULL default '0',
bild_height smallint(4) NOT NULL default '0',
html_file varchar(250) NOT NULL default '',
info enum('f','t') NOT NULL default 't',
diradress varchar(50) default NULL,//new cloulmn
PRIMARY KEY (sys_pk),
KEY key_sys_state (sys_state),
KEY key_sys_dlm (sys_dlm),
KEY key_sys_doc (sys_doc),
KEY Key_fk_t_directories_parent (fk_t_directories_parent),
KEY Key_sort (sort)
) TYPE=MyISAM;



actaually i want to insert data in diradress column
and i update this table For the first two levels

First create an addiditon variable 'updated' with default = 0
Then, for the top level, you could use
update t_directories set diradress = sys_pk, updated = 1 where fk_t_directories_parent is Null

then insert all updated records in a new table (t_directories_copy),

or something like that.

Then, for the second level, you can run
update t_directories set updated=2, diradress = CONCAT(fk_t_directories_parent , '|',sys_PK) where diradress = Null and fk_t_directories_parent In ('select sys_pk from t_directories_copy)

thats why i just want to get the value in diradress but still im getting diradress as Null


thanks
Post Reply