Mysql query

[rajasekaran1965]

Hi

 

I need a sql query help from you guys.

 

It is a sql query for get all upline referrer details from database for particular person

 

when new person register his details are storing in wp_members_tbl with uername, password, firstname, lastname, email, phone, address, referrer etc.

 

Below the query is for user

 

$wp_aff_members_db = $wpdb->get_row("SELECT * FROM $members_table_name WHERE refid = '".$_SESSION['user_id']."'", OBJECT);

 

And below the query is for this user's upline referrer

 

$wp_aff_members_db = $wpdb->get_row("SELECT * FROM $members_table_name WHERE refid = '$referrer'", OBJECT);

 

Now i need the query for find and get this referrer's upline referrer.

 

Please help me with a solution

 

Regards

Edited October 23, 2014 by rajasekaran1965

[jcbones]

This question is impossible to answer.  Mainly because we have no idea how these variables are coded, nor how the database retains the refid, of which is not in your structure details.

Things we need:

1. An actual structure dump of the database.  This can be obtained by:

 

DESCRIBE $members_table_name

or, by exporting it from a database software like phpmyadmin.

2. How you are tracking, setting, storing the refid, $referrer, etc.

 

Without those items, it just seems impossible to me.

[Psycho]

I believe the refid is the same as the member id from the same table (also not included in the details above). So, let's say the ID for my record is 25. If I then refer JCBones a new record will be created for him. His record will create a new ID (say 52) and the refID will be my ID of 25 since I referred him.

 

rajasekaran1965, do not use '*' in your queries. It is lazy and inefficient. List out the fields you need. And, don't put ID values in quotes in a query.

 

 

//Get the data of the users referred by the current user
$query = "SELECT id, firstname, lastname
          FROM {$members_table_name}
          WHERE refid = {$_SESSION['user_id']}";
 
//Get the data of all the users referred by the selected referrer ID
$query = "SELECT id, firstname, lastname
          FROM {$members_table_name}
          WHERE refid = {$referrer}";
 
 
//Get the data of user who referred the selected referrer
$query = "SELECT refid
          FROM {$members_table_name}
          WHERE id = {$referrer}";

[rajasekaran1965]

 

I believe the refid is the same as the member id from the same table (also not included in the details above). So, let's say the ID for my record is 25. If I then refer JCBones a new record will be created for him. His record will create a new ID (say 52) and the refID will be my ID of 25 since I referred him.

 

rajasekaran1965, do not use '*' in your queries. It is lazy and inefficient. List out the fields you need. And, don't put ID values in quotes in a query.

//Get the data of the users referred by the current user
$query = "SELECT id, firstname, lastname
          FROM {$members_table_name}
          WHERE refid = {$_SESSION['user_id']}";
 
//Get the data of all the users referred by the selected referrer ID
$query = "SELECT id, firstname, lastname
          FROM {$members_table_name}
          WHERE refid = {$referrer}";
 
 
//Get the data of user who referred the selected referrer
$query = "SELECT refid
          FROM {$members_table_name}
          WHERE id = {$referrer}";

Hi

 

Thank you very much for your reply.

 

Your first code is working fine for get the users referred by the current user.

//Get the data of the users referred by the current user
$query = "SELECT id, firstname, lastname
          FROM {$members_table_name}
          WHERE refid = {$_SESSION['user_id']}";

But second and third code not worked for me. here with i attached the required php files. 

 

my actual need is to create a new table and update the referrers names obtained from current table.

 

New table columns should be like below

 

Column 1  refid            =  user_id (should copy from wp_affiliates_tbl column (refid)

Column 2  email          =  email id (should copy from wp_affiliates_tbl column (email)

Column 3  referrer(1)  =  referrer of user_id (should copy from wp_affiliates_tbl column(referrer)

Column 4  referrer(2)  =  referrer of referrer(1) (this i need to get and update the table)

Column 5  referrer(3)  =  referrer of referrer(2) (this i need to get and update the table)

Column 6  referrer(4)  =  referrer of referrer(3) (this i need to get and update the table)

Column 7  referrer(5)  =  referrer of referrer(4) (this i need to get and update the table)

Column 8  referrer(6)  =  referrer of referrer(5) (this i need to get and update the table)

Column 9  referrer(7)  =  referrer of referrer(6) (this i need to get and update the table)

 

I dont know much about php, can u please help me regarding thiswp-affiliate-platform.zip

[Psycho]

You're kidding, right? I'm more than happy to "Help" you. I'm not going to build something for you.

 

Your original post only stated you weren't able to figure out how to create the query for what you needed.

 

 

Now i need the query for find and get this referrer's upline referrer

 

And, even though you really didn't provide all the necessary information I made some calculated guesses and provided some examples to point you in the right direction. Now, you come back simply dumping your current code with a statement that you need a new table and a requirement for new functionality to update data based upon some vague requirement.

 

Forget it. I'm out.

Edited October 24, 2014 by Psycho