Re: Help to create multiple referrals using php and mysql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux