Re: Help to create multiple referrals using php and mysql

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

Fwd: Help to create multiple referrals using php and mysql

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