Dear All, 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) some refered one person usha(cust_id :29) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
