Here is my deal. I have 3 tables called rbf_user, rb_raketracking_usermap, rb_raketracking
I am trying to write a query that will get all of the users from rbf_user who's referral id != NULL and who has at least 1 status = 1 in rb_raketracking_usermap and who has at least 1 row in rb_raketracking where net_rake != NULL and data_date = $data_date. But I only want to return the userid and the referralid from rbf_user.
Right now the only way I can think of to do this is through 3 queries but this is taking my script over a minute to finish. Here is sudo code of what I have now:
SELECT userid, referralid FROM rbf_user where referralid != ""
while $info = that query
SELECT * FROM rb_raketracking_usermap WHERE fk_vb_user_id = $info->referralid AND status = "1"
if rows returned
while $usermap = that query
SELECT * FROM rb_raketracking WHERE room_username = $usermap->room_username AND data_date = $data_date AND is_final = "1"
while $raketracking = that query
calculate $rakeback
else
skip this user
if $rakeback == 0
skip this user
end loop
The big problem is, is that there are over 5000 users who have people they referred and not all of those users who where referred by them are in the rb_raketracking_usermap or in the rb_raketracking. I just want the users who where referred by someone and who have a valid row in the rb_raketracking and a valid row in the rb_raketracking_usermap.
select user.id, user.referralid from user left join (map, tracking) on (map.userid = user.referralid and tracking.username = map.username) where map.status = 1 and tracking.date = $data and tracking.is_final = 1;
Aye but that does not work. It is not returning any proper results.
To maybe make things clearer, here is how my tables are layed out with only the relivant things:
rb_user:
userid - the id of the user
referrerid - the id of the user who referred this user
rb_raketracking_usermap:
fk_vb_user_id - the userid from the rb_user
fk_room_id - the id of the room
room_username - their username for the room that each entry is for
status - 0 for pending, 1 for active, 2 for rejected
rb_raketracking:
fk_room_id - the id of the room
room_username - the username for the room that each entry is for
data_date - the date that each row is for
Now when a user signs up they can then have as many entries in the rb_raketracking_usermap as they want. So each user can have like 5 usermap entries. Then for each user in the usermap, there can be hundreds of rows in the rb_raketracking table for that user in the usermap.
So what I am doing is selecting a date from a select box, then my real goal is to get all of that months information from the rb_raketracking for the users that where referred to the website by someone else. So I do this by:
-I get all of the users who where referred to the website by someone else
-I check the usermap table to see if they are in there, because they have to have at least (but not limited to) 1 entry in the usermap for them to be of any worth
-If they do have entry(ies) in the usermap, I get all of them and then check for each usermap entry if there is a rbf_raketracking entry for the selected month and if it is 'final'
-If there is, then I go through the entries in the rbf_raketracking table and start to calculate the 'rakeback'
-If the 'rakeback' calculates to be more than 0, their information is displayed.
The biggest hangup is that there are like 5000 users who where referred by someone else. So I have to load that huge array into memory, then start running more queries on that user to see if they in the end get any 'rakeback'. If I could just limit the first query to return the 50 or so users who where referred by someone else and do get 'rakeback' (without actually doing any real calculations yet, just knowing that the calculations won't be worthless) then my scripts would be much much faster. And that is where I need help.
I can post the entire function that is running if anyone wants to see the actual code that I am using and maybe has and idea on how to improve on it. But it is a big large so I will wait first to see if I was descriptive enough.
If you can't think of a way to condense all of that into less queries, then maybe you can't. You should consider saving the results of these somewhere for quick access or a different database design.