Value not echoing

I-AM-OBODO · December 25, 2014

Hello guys,

I want to get the values of 3rd level of my referral but it did not echo any value but if i put the ref id directly on the code, it will echo the values. why is it so? or am i doing it the wrong way?

in my database i have a table test_referral

id   acct_name   ref_id   refer_id
1   john       J1234   0
2   bull       B3456   J1234
3   doe       D5567   J1234
4   frank       F7788   J1234
5   jimmy       J9990   J1234
6   tommy       T6784   F7788
7   tom       T9988   F7788
8   girly       G8866   D5567
9   fred       F0099   J9990
10   ronaldo       R7722   B3456

thanks

$stmt= $pdo->query("SELECT * FROM test_referral WHERE acct_name='Chidi Okafor'");
$parent = $stmt->fetch(PDO::FETCH_LAZY);
echo "<ul><li>";
echo $parent['acct_name'];
$ref_id  = $parent['ref_id'];
echo "</li><ul>";



$stmt= $pdo->query("SELECT * FROM test_referral WHERE refer_id='$ref_id'");
$child_count = $stmt->rowCount();
while($child = $stmt->fetch(PDO::FETCH_LAZY)){
echo "<li>";
echo $child['acct_name'];
$ref_ids  = $child['ref_id'];
echo "</li>";
}

//$reff = "F7788";
//$stmt= $pdo->query("SELECT * FROM test_referral WHERE refer_id='$F7788'");
$stmt= $pdo->query("SELECT * FROM test_referral WHERE refer_id='$ref_ids'");
$child_count2 = $stmt->rowCount();

while($children = $stmt->fetch(PDO::FETCH_LAZY)){
echo "<ul><li>";
echo $children['acct_name'];
echo "</li></ul>";

}

Edited December 25, 2014 by Mr-Chidi

Barand · December 25, 2014

Don't run queries in loops. To do this use a recursive function on your data stored in an array.

This code will store your data in the array $data.

$data[ref_id][referrers] will be an array of the chain of referrers for each user. EG

the code

$sql = "SELECT id
        , acct_name
        , ref_id
        , refer_id
        FROM test_referral";
$data = array();
$res = $mysqli->query($sql);
while ($row = $res->fetch_assoc()) {
    $data[$row['ref_id']] = array(
                        'acct_name' => $row['acct_name'],
                        'refer_id'  => $row['refer_id'],
                        'referrers' => array()
                        );
}

foreach ($data as $id => $user) {
    getRef($data, $data[$id]['refer_id'], $id);
}

//
// recursive function to get list of referrers
//
function getRef(&$data, $id, $startid) {
    if ($id=='0') return;
    $data[$startid]['referrers'][] = $id;
    getRef($data, $data[$id]['refer_id'] ,$startid);
}

That will work for any depth of referrals. Where you have a fixed depth then you can use a query with a couple of left joins

SELECT r.id
, r.acct_name
, r.ref_id
, r1.ref_id as first
, r2.ref_id as second
FROM test_referral r 
LEFT JOIN test_referral r1 ON r.refer_id = r1.ref_id
LEFT JOIN test_referral r2 ON r1.refer_id = r2.ref_id;

+----+-----------+--------+-------+--------+
| id | acct_name | ref_id | first | second |
+----+-----------+--------+-------+--------+
|  1 | john      | J1234  |       |        |
|  2 | bull      | B3456  | J1234 |        |
|  3 | doe       | D5567  | J1234 |        |
|  4 | frank     | F7788  | J1234 |        |
|  5 | jimmy     | J9990  | J1234 |        |
|  6 | tommy     | T6784  | F7788 | J1234  |
|  7 | tom       | T9988  | F7788 | J1234  |
|  8 | girly     | G8866  | D5567 | J1234  |
|  9 | fred      | F0099  | J9990 | J1234  |
| 10 | ronaldo   | R7722  | B3456 | J1234  |
+----+-----------+--------+-------+--------+

Edited December 25, 2014 by Barand

I-AM-OBODO · December 26, 2014

Thanks. But when i tried the above code it's giving undefined index somewhere here:

getRef($data, $data[$id]['refer_id'] ,$startid);

I-AM-OBODO · December 29, 2014

Don't run queries in loops. To do this use a recursive function on your data stored in an array.

This code will store your data in the array $data.

$data[ref_id][referrers] will be an array of the chain of referrers for each user. EG

the code

$sql = "SELECT id
        , acct_name
        , ref_id
        , refer_id
        FROM test_referral";
$data = array();
$res = $mysqli->query($sql);
while ($row = $res->fetch_assoc()) {
    $data[$row['ref_id']] = array(
                        'acct_name' => $row['acct_name'],
                        'refer_id'  => $row['refer_id'],
                        'referrers' => array()
                        );
}

foreach ($data as $id => $user) {
    getRef($data, $data[$id]['refer_id'], $id);
}

//
// recursive function to get list of referrers
//
function getRef(&$data, $id, $startid) {
    if ($id=='0') return;
    $data[$startid]['referrers'][] = $id;
    getRef($data, $data[$id]['refer_id'] ,$startid);
}

That will work for any depth of referrals. Where you have a fixed depth then you can use a query with a couple of left joins

SELECT r.id
, r.acct_name
, r.ref_id
, r1.ref_id as first
, r2.ref_id as second
FROM test_referral r 
LEFT JOIN test_referral r1 ON r.refer_id = r1.ref_id
LEFT JOIN test_referral r2 ON r1.refer_id = r2.ref_id;

+----+-----------+--------+-------+--------+
| id | acct_name | ref_id | first | second |
+----+-----------+--------+-------+--------+
|  1 | john      | J1234  |       |        |
|  2 | bull      | B3456  | J1234 |        |
|  3 | doe       | D5567  | J1234 |        |
|  4 | frank     | F7788  | J1234 |        |
|  5 | jimmy     | J9990  | J1234 |        |
|  6 | tommy     | T6784  | F7788 | J1234  |
|  7 | tom       | T9988  | F7788 | J1234  |
|  8 | girly     | G8866  | D5567 | J1234  |
|  9 | fred      | F0099  | J9990 | J1234  |
| 10 | ronaldo   | R7722  | B3456 | J1234  |
+----+-----------+--------+-------+--------+

I tried implementing this on my program, it worked perfectly. I even added more joins but the problem is relating the table to a user. The problem i am having is trying to co-relate the third person to the first. Assuming i invited paul and paul invited peter. On my account, i could see just paul and can't see peter. I've been trying this for days now but to no avail. Truth is i was hoping to get this done before the new year. I couldnt figure out the logic to make them co-relate, so that even when peter should invite someone else, i could still see the person on my downline.

$sql = "SELECT r.id , r.acct_name , r.ref_id , r1.acct_name as first , r2.acct_name as second, r3.acct_name as third, r4.acct_name as fourth FROM ca_categories r LEFT JOIN ca_categories r1 ON r.refer_id = r1.ref_id LEFT JOIN ca_categories r2 ON r1.refer_id = r2.ref_id LEFT JOIN ca_categories r3 ON r2.refer_id = r3.ref_id LEFT JOIN ca_categories r4 ON r3.refer_id = r4.ref_id WHERE r.referrer_uname = '$_SESSION[username]' ORDER by id";

Please and thanks

Barand · December 29, 2014

I have run your query against the test_referral table that you provided (after adding adding a couple of extra referrals to give a chain of 4 names) and it works fine.

I can only assume the problem is when you run it against your ca_categories table, which I cannot help with.

I-AM-OBODO · December 29, 2014

I have run your query against the test_referral table that you provided (after adding adding a couple of extra referrals to give a chain of 4 names) and it works fine.

I can only assume the problem is when you run it against your ca_categories table, which I cannot help with.

Hi. I didn't say It's not working. I said when I use it with a username it's not displaying my indirect down-stream eg if I refer someone and the person refers another, all I see is referral without seeing my referral's referral.

if I could get the logic right, it'll work.

Thanks

Barand · December 29, 2014

These are my results

mysql> select * from test_referral;
+----+-----------+--------+----------+
| id | acct_name | ref_id | refer_id |
+----+-----------+--------+----------+
|  1 | john      | J1234  | 0        |
|  2 | bull      | B3456  | J1234    |
|  3 | doe       | D5567  | J1234    |
|  4 | frank     | F7788  | J1234    |
|  5 | jimmy     | J9990  | J1234    |
|  6 | tommy     | T6784  | F7788    |
|  7 | tom       | T9988  | F7788    |
|  8 | girly     | G8866  | D5567    |
|  9 | fred      | F0099  | H7654    |
| 10 | ronaldo   | R7722  | B3456    |
| 11 | henry     | H7654  | K1234    |
| 12 | kim       | K1234  | J9990    |
+----+-----------+--------+----------+

SELECT r.id
  , r.acct_name
  , r.ref_id
  , r1.acct_name as first 
  , r2.acct_name as second
  , r3.acct_name as third
  , r4.acct_name as fourth 
  FROM test_referral r 
  LEFT JOIN test_referral r1 ON r.refer_id = r1.ref_id 
  LEFT JOIN test_referral r2 ON r1.refer_id = r2.ref_id 
  LEFT JOIN test_referral r3 ON r2.refer_id = r3.ref_id 
  LEFT JOIN test_referral r4 ON r3.refer_id = r4.ref_id 
  WHERE r.acct_name = 'fred' 
  ORDER by id;
+----+-----------+--------+-------+--------+-------+--------+
| id | acct_name | ref_id | first | second | third | fourth |
+----+-----------+--------+-------+--------+-------+--------+
|  9 | fred      | F0099  | henry | kim    | jimmy | john   |
+----+-----------+--------+-------+--------+-------+--------+

I-AM-OBODO · December 29, 2014

Thank you so very much for your time and help. Maybe i'm not makingmyself clearer enough. Everything is working just fine but the problem is that i do not know how to all the people on my downline.

Fot instance in the example table, John is the parent, how to select everyone that comes from John (directly or indirectly).
To attempt this i created another colunm called parent_id. On the first level, it easy to insert a value to John as parent. If frank refer tommy, frank becomes tommy's parent. If tommy refers someone, he becomes parent as well. The problem is if i do an insert to table_name, the parent is the new parent. How co-relate the parents together.

 
//if no one referred you, you become the parent of yourself else your referrer is your parent
$parent_id = "J123";

//since john referred frank, franks refer_id is john's ref_id which is J123
$refer_id = $ref_id;

$stmt=$pdo->prepare("INSERT INTO table(parent_id, acct_num, ref_id, refer_id) VALUES(:parent_id, :acct_num, :ref_id, :refer_id");
$stmt->bindValue(':parent_id', $parent_id, PDO::PARAM_STR);
$stmt->bindValue(':acct_num', $acct_num, PDO::PARAM_STR);
$stmt->bindValue(':ref_id', $ref_id, PDO::PARAM_STR);
$stmt->bindValue(':refer_id', $refer_id, PDO::PARAM_STR);
$stmt->execute();

the problem now is that if frank becomes the parent, frank's ref_id will be in the parent_id colunm, i need something to distinguish between john and other children and grand children so that when doing a select, I will say where username = john and the_relationship = relationship so that every person on the downline will display

Thanks

Barand · December 29, 2014

It's just the same problem in reverse. If you use the query method, reverse the fields in the joins

$sql = "SELECT r.id
 , r.acct_name
 , r.ref_id
  , r1.acct_name as first 
  , r2.acct_name as second
  , r3.acct_name as third
  , r4.acct_name as fourth 
  FROM test_referral r 
  LEFT JOIN test_referral r1 ON r1.refer_id = r.ref_id 
  LEFT JOIN test_referral r2 ON r2.refer_id = r1.ref_id 
  LEFT JOIN test_referral r3 ON r3.refer_id = r2.ref_id 
  LEFT JOIN test_referral r4 ON r4.refer_id = r3.ref_id 
  WHERE r.acct_name = 'john' 
  ORDER by id";
+----+-----------+--------+-------+---------+-------+--------+
| id | acct_name | ref_id | first | second  | third | fourth |
+----+-----------+--------+-------+---------+-------+--------+
|  1 | john      | J1234  | frank | tommy   | NULL  | NULL   |
|  1 | john      | J1234  | doe   | girly   | NULL  | NULL   |
|  1 | john      | J1234  | bull  | ronaldo | NULL  | NULL   |
|  1 | john      | J1234  | jimmy | kim     | henry | fred   |
|  1 | john      | J1234  | frank | tom     | NULL  | NULL   |
+----+-----------+--------+-------+---------+-------+--------+

Or if you want to go to any depth, use the recursion method

$sql = "SELECT id
        , acct_name
        , ref_id
        , refer_id
        FROM test_referral";
$data = array();
$res = $mysqli->query($sql);
while ($row = $res->fetch_assoc()) {
    $names[$row['ref_id']] = $row['acct_name'];
    $data[$row['refer_id']][] = $row['ref_id'];
}

$id = 'J1234';

getRefs($data, $names, $id);

//
// recursive function to get list of referrees
//
function getRefs(&$data, &$names, $id, $level=0) {
    $indent = str_repeat('--- ', $level);
    echo "$indent {$names[$id]}<br>";
    if (isset($data[$id])) {
        foreach ($data[$id] as $refid) {
            getRefs($data, $names, $refid, $level+1);
        }
    }
}

/// OUTPUTS //////////////////////
john
--- bull
--- --- ronaldo
--- doe
--- --- girly
--- frank
--- --- tommy
--- --- tom
--- jimmy
--- --- kim
--- --- --- henry
--- --- --- --- fred

Edited December 29, 2014 by Barand

I-AM-OBODO · December 29, 2014

I tried the recursion method you sent earlier but It's giving "undefined index" here.

getRef($data, $data[$id]['

refer_id'] ,$startid);

Barand · December 29, 2014

Yes, I know. But there is no way that I can I help with that one line of code quoted out of context. The code was tested before posting.

BTW, the recursive code I just posted is not the same as that posted earlier in this thread.

I-AM-OBODO · December 29, 2014

Thank you so very much. you are a life saver. Will find a way of solving the undefined index issue. But i think at the long run, i'll settle with the query instead of the recursive cos i can format the output of that of sql query (join) than the recursive function, but both are very useful.

thanks

I-AM-OBODO · December 29, 2014

Yes, I know. But there is no way that I can I help with that one line of code quoted out of context. The code was tested before posting.

BTW, the recursive code I just posted is not the same as that posted earlier in this thread.

But should i want to assign point for each referral, where should i assign it eg once your downline is 1, you earn £1 and when 2 you earn 5cents, 3 you earn 2cents.

CroNiX · December 29, 2014

You really shouldn't keep adding to an issue after you mark it as solved. Most people won't read it if it has the big green ANSWERED tag in the title.

Edited December 29, 2014 by CroNiX

Barand · December 29, 2014

So given this situation

 frank
 --- tommy
 --- tom
 jimmy
 --- kim
 --- --- henry

Frank (2 refers) gets 1.05

Jimmy (1 refer) gets 1.00

Kim (1 refer) gets 1.00

Is that right?

I-AM-OBODO · December 30, 2014

So given this situation
 frank
 --- tommy
 --- tom
 jimmy
 --- kim
 --- --- henry
Frank (2 refers) gets 1.05

Jimmy (1 refer) gets 1.00

Kim (1 refer) gets 1.00

Is that right?

Assuming I set aside £50 for refering some one

Level 1 will have nothing (since you are level 1)

Level 2 will have £4.5 (9% of £50)

Level 3 will have £13.50 (27% of £50)

Level 4 will have £32 (64% £50)

Barand · December 30, 2014

So.

There a 3 level 2 users (Tom, Tommy, Kim) so they each receive £4.50/3 (ie £1.50 each)

There is 1 level 3 user (Henry) so he receives £13.50.

I-AM-OBODO · December 30, 2014

So.

There a 3 level 2 users (Tom, Tommy, Kim) so they each receive £4.50/3 (ie £1.50 each)

There is 1 level 3 user (Henry) so he receives £13.50.

sorry. my mistake.

what I mean is for level 2 the parent receives 9% of 50 and level 3, he receives 27% and 64% for level 4.

And so also will a referrer that have become a parent receive.

Sign In

Value not echoing

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information