Dear All, I am new to this mailing list and new to LAMP .please correct me any thing wrong .And also tell me how to achive this effieient way with less query ,and less expansive . I have one requirement to create multiple referral for my project .I found some information from php-db archive .I follow the same to design the database .here is the my data base information This the query used to create table: mysql> create table customers ( cust_id int unsigned not null auto_increment primary key, cust_name VARCHAR(25) default NULL ); Query OK, 0 rows affected (0.00 sec) Then inserted following values to the my table: mysql> insert into customers(cust_id,cust_name)values (1,'guru'); Query OK, 1 row affected (0.08 sec) mysql> insert into customers(cust_id,cust_name)values (2,'gopal'); Query OK, 1 row affected (0.01 sec) mysql> insert into customers(cust_id,cust_name)values (3,'sathish'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (4,'suresh'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (5,'uma'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (6,'ram'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (7,'raj'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (8,'kumar'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (9,'mohan'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (10,'ahmed'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (11,'ruba'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (12,'sai'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values(13,'mahul'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (14,'karthik'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (15,'kamaraj'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (16,'ravi'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (17,'prakash'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (18,'selvam'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (19,'raja'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (20,'ragu'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (21,'ramesh'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (22,'rajesh'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (23,'nandini'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (24,'raman'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (25,'somu'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (26,'mani'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (27,'murugan'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (28,'maran'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (29,'usha'); Query OK, 1 row affected (0.01 sec) mysql> insert into customers(cust_id,cust_name)values (30,'radha'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (31,'rathika'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (32,'vishnu'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (33,'mala'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (34,'malar'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (35,'magesh'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (36,'senthil'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (37,'arul'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (38,'lakshmi'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (39,'sarasvathi'); Query OK, 1 row affected (0.00 sec) mysql> insert into customers(cust_id,cust_name)values (40,'linus'); Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from customers; +---------+------------+ | cust_id | cust_name | +---------+------------+ | 1 | guru | | 2 | gopal | | 3 | sathish | | 4 | suresh | | 5 | uma | | 6 | ram | | 7 | raj | | 8 | kumar | | 9 | mohan | | 10 | ahmed | | 11 | ruba | | 12 | sai | | 13 | mahul | | 14 | karthik | | 15 | kamaraj | | 16 | ravi | | 17 | prakash | | 18 | selvam | | 19 | raja | | 20 | ragu | | 21 | ramesh | | 22 | rajesh | | 23 | nandini | | 24 | raman | | 25 | somu | | 26 | mani | | 27 | murugan | | 28 | maran | | 29 | usha | | 30 | radha | | 31 | rathika | | 32 | vishnu | | 33 | mala | | 34 | malar | | 35 | magesh | | 36 | senthil | | 37 | arul | | 38 | lakshmi | | 39 | sarasvathi | | 40 | linus | +---------+------------+ 40 rows in set (0.00 sec) Then created another table referral with: create table referrals ( cust_id int unsigned not null, ref_id int unsigned not null, ref_level tinyint unsigned not null, primary key (cust_id, ref_level), index (ref_id, ref_level) ); Manually I am calculated the reference level then inserted it to the above table : insert into referrals (cust_id,ref_id,ref_level)values(1,0,0); insert into referrals (cust_id,ref_id,ref_level) values(2,1,1); insert into referrals (cust_id,ref_id,ref_level) values(3,2,2); insert into referrals (cust_id,ref_id,ref_level) values(4,0,0); insert into referrals (cust_id,ref_id,ref_level) values(5,4,1); insert into referrals (cust_id,ref_id,ref_level) values(6,3,3); insert into referrals (cust_id,ref_id,ref_level) values(7,6,4); insert into referrals (cust_id,ref_id,ref_level) values(8,0,0); insert into referrals (cust_id,ref_id,ref_level) values(9,6,4); insert into referrals (cust_id,ref_id,ref_level) values(10,7,5); insert into referrals (cust_id,ref_id,ref_level) values(11,0,0); insert into referrals (cust_id,ref_id,ref_level) values(12,10,6); insert into referrals (cust_id,ref_id,ref_level) values(13,9,5); insert into referrals (cust_id,ref_id,ref_level) values(14,10,6); insert into referrals (cust_id,ref_id,ref_level) values(15,13,6); insert into referrals (cust_id,ref_id,ref_level) values(16,9,5); insert into referrals (cust_id,ref_id,ref_level) values(17,13,6); insert into referrals (cust_id,ref_id,ref_level) values(18,10,6); insert into referrals (cust_id,ref_id,ref_level) values(19,15,7); insert into referrals (cust_id,ref_id,ref_level) values(20,12,7); insert into referrals (cust_id,ref_id,ref_level) values(21,14,7); insert into referrals (cust_id,ref_id,ref_level) values(22,20,8); insert into referrals (cust_id,ref_id,ref_level) values(23,9,5); insert into referrals (cust_id,ref_id,ref_level) values(24,17,7); insert into referrals (cust_id,ref_id,ref_level) values(25,24,8); insert into referrals (cust_id,ref_id,ref_level) values(26,22,9); insert into referrals (cust_id,ref_id,ref_level) values(27,26,10); insert into referrals (cust_id,ref_id,ref_level) values(28,27,11); insert into referrals (cust_id,ref_id,ref_level) values(29,25,9); insert into referrals (cust_id,ref_id,ref_level) values(30,29,10); insert into referrals (cust_id,ref_id,ref_level) values(31,30,11); insert into referrals (cust_id,ref_id,ref_level) values(32,31,12); insert into referrals (cust_id,ref_id,ref_level) values(33,32,13); insert into referrals (cust_id,ref_id,ref_level) values(34,33,14); insert into referrals (cust_id,ref_id,ref_level) values(35,34,15); insert into referrals (cust_id,ref_id,ref_level) values(36,35,16); insert into referrals (cust_id,ref_id,ref_level) values(37,36,17); insert into referrals (cust_id,ref_id,ref_level) values(38,28,12); insert into referrals (cust_id,ref_id,ref_level) values(39,38,13); insert into referrals (cust_id,ref_id,ref_level) values(40,0,0); +---------+--------+-----------+ | cust_id | ref_id | ref_level | +---------+--------+-----------+ | 1 | 0 | 0 | | 2 | 1 | 1 | | 3 | 2 | 2 | | 4 | 0 | 0 | | 5 | 4 | 1 | | 6 | 3 | 3 | | 7 | 6 | 4 | | 8 | 0 | 0 | | 9 | 6 | 4 | | 10 | 7 | 5 | | 11 | 0 | 0 | | 12 | 10 | 6 | | 13 | 9 | 5 | | 14 | 10 | 6 | | 15 | 13 | 6 | | 16 | 9 | 5 | | 17 | 13 | 6 | | 18 | 10 | 6 | | 19 | 15 | 7 | | 20 | 12 | 7 | | 21 | 14 | 7 | | 22 | 20 | 8 | | 23 | 9 | 5 | | 24 | 17 | 7 | | 25 | 24 | 8 | | 26 | 22 | 9 | | 27 | 26 | 10 | | 28 | 27 | 11 | | 29 | 25 | 9 | | 30 | 29 | 10 | | 31 | 30 | 11 | | 32 | 31 | 12 | | 33 | 32 | 13 | | 34 | 33 | 14 | | 35 | 34 | 15 | | 36 | 35 | 16 | | 37 | 36 | 17 | | 38 | 28 | 12 | | 39 | 38 | 13 | | 40 | 0 | 0 | +---------+--------+-----------+ using this Is it possible to get up to 5 level in one query? I mean for example this my customer and who was referred by each and every customer . I want to display up to five level up and down referral using one query .but without using joint . For example : Let us consider cust_id 17 is prakash . prakash was refered by mahul (cust_id :13) mahul was refered by mohan(cust_id:9) mohan was refered by ram(cust_id:6) Three level up (it may be increase in future ) prakash refered one person raman (cust_id:24) raman refered one person somu (cust_id:25) somu refered one person usha(cust_id :29) Three level down Is it possible to get it in one or two query? If yes ,means please give me the idea to do this . Thanks for your time L.mohan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
