Jump to content

mannyson

Members
  • Posts

    20
  • Joined

  • Last visited

mannyson's Achievements

Member

Member (2/5)

0

Reputation

1

Community Answers

  1. So I have a much simpler solution. The whole reason I wanted to find a new method for 3 loops is because I wanted to have a load more button. Unfortunately that only works if the last loop is has LIMIT set. Having said that I just found a better solution. I can still keep my original 3 loops to retrieve records. But instead of having hundreds of records load directly on the page, I can have a mini vertical scroll in the css table(overflow:scroll;). I know the page load time will be the same but atleast it'll be a much easier way to look at the records for mobile users. Instead of them keep scrolling the page itself, they will scroll the table itself instead.
  2. I think you have some users and referrals variables mixed. It's my fault for not specifying. Here are all the columns for each table. Users table: user_id, username, email, joined Referrals table: referral_id, referred_by, referred_user, referred_paid And here my original 3 tier foreach loop to give you some perspective. What would be your updated code based on all this? $get_records = $db->prepare("SELECT users.*, referrals.* FROM users LEFT JOIN referrals ON users.user_id = referrals.referred_user WHERE referrals.referred_by = :ref_by"); $get_records->bindParam(':ref_by', $global_user_id); $get_records->execute(); $result_records = $get_records->fetchAll(PDO::FETCH_ASSOC); if(count($result_records) > 0) { foreach($result_records as $row) { $ref_user_id = trim($row['user_id']); $get_records2 = $db->prepare("SELECT users.*, referrals.* FROM users LEFT JOIN referrals ON users.user_id = referrals.referred_user WHERE referrals.referred_by = :ref_by"); $get_records2->bindParam(':ref_by', $ref_user_id); $get_records2->execute(); $result_records2 = $get_records2->fetchAll(PDO::FETCH_ASSOC); if(count($result_records2) > 0) { foreach($result_records2 as $row) { $ref_user_id2 = trim($row['user_id']); $get_records3 = $db->prepare("SELECT users.*, referrals.* FROM users LEFT JOIN referrals ON users.user_id = referrals.referred_user WHERE referrals.referred_by = :ref_by"); $get_records3->bindParam(':ref_by', $ref_user_id2); $get_records3->execute(); $result_records3 = $get_records3->fetchAll(PDO::FETCH_ASSOC); if(count($result_records3) > 0) { foreach($result_records3 as $row) { $ref_username = trim($row['username']); $ref_email = trim($row['email']); $ref_joined = trim($row['joined']); $ref_paid = trim($row['referred_paid']); ?> <div class="table-row"> <ul> <li class="th-1"><?php echo $ref_username; ?></li> <li class="th-2"><?php echo $ref_email; ?></li> <li class="th-3"><?php echo $ref_joined; ?></li> <li class="th-4"><?php if($ref_paid == 1){echo 'Yes';} else {echo 'No';}; ?></li> </ul> </div> <?php } } } } } }
  3. The 3rd loop is the same as the first 2 loops except it'll be based on the output of the user_id variable(referrals.referred_by = :ref_by) from the 2nd loop. So currently I only have these three variables in my 2nd loop. $ref_username = trim($row['username']); $ref_email = trim($row['email']); $ref_joined = trim($row['joined']); But the fourth variable would be added like this. $ref_user_id2 = trim($row['user_id']); $ref_username = trim($row['username']); $ref_email = trim($row['email']); $ref_joined = trim($row['joined']);
  4. I basically want to output the following as seen in my original code. <div class="table-row"> <ul> <li class="th-1"><?php echo $ref_username; ?></li> <li class="th-2"><?php echo $ref_email; ?></li> <li class="th-3"><?php echo $ref_joined; ?></li> </ul> </div>
  5. I understand. Unfortunately for the current project I can not switch the database. All I need is equivalent of 3rd loop added to your single query. That'll be the end of this feather.
  6. Correct. In my original code, I had a loop inside a loop. But I would like to add another loop inside that. Jacques1's single query equals the 2 loops. I was wondering how it would look if it equals 3 loops?
  7. My bad, I was looking at the wrong parameter before. It works now. My question is, how good is this method? Will I have any issues with it if I am retrieving hundreds or thousands of results? Also say I want to add the 3rd layer/loop to it, how would that look?
  8. Wow that is some complex sql query. I have used your original query and updated it with this new query. I am not sure if it's right but here it is. $query = "SELECT username, email, joined FROM ( SELECT users.username, users.email, users.joined, global_referrals.referred_user AS global_referred_user FROM referrals AS global_referrals JOIN referrals AS user_referrals ON user_referrals.referred_by = global_referrals.referred_user JOIN users ON users.user_id = user_referrals.referred_user CROSS JOIN (SELECT @row_num := 1, @last_global_referred_user := NULL) AS init WHERE global_referrals.referred_by = :global_user_id ORDER BY global_referrals.referred_user, user_referrals.referral_id DESC ) AS refs WHERE (@row_num := IF(global_referred_user = @last_global_referred_user, @row_num + 1, (@last_global_referred_user := global_referred_user) > 0)) <= 5 "; $get_records = $db->prepare($query); $get_records->bindParam(':global_referred_user', $global_user_id); $get_records->execute(); $result_records = $get_records->fetchAll(PDO::FETCH_ASSOC); if(count($result_records) > 0) { //Create output $output = ''; foreach($result_records as $row) { $ref_username = escape($row['username']); $ref_email = escape($row['email']); $ref_joined = trim($row['joined']); $output .= "<div class='table-row'>\n"; $output .= " <ul>\n"; $output .= " <li class='th-1'>{$ref_username}</li>\n"; $output .= " <li class='th-2'>{$ref_email}</li>\n"; $output .= " <li class='th-3'>{$ref_joined}</li>\n"; $output .= " </ul>\n"; $output .= "</div>\n"; } } That gives me the following error. I am wondering is there another way to achieve the same result? Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' in C:\xampp\htdocs\aff\members\referral-network.php:122 Stack trace: #0 C:\xampp\htdocs\aff\members\referral-network.php(122): PDOStatement->bindParam(':global_referre...', '2') #1 {main} thrown in C:\xampp\htdocs\aff\members\referral-network.php on line 122
  9. You are correct. That's exactly what I am trying to accomplish. But the query example you showed only shows users retrieved by the target user($global_user_id).
  10. You make some very good points. But I still need to have 2 different foreach loops. 1st foreach loop returns a list of users from Column 2. 2nd foreach loop uses the "users" from Column 2 to retrieve users from Column 1. I know it might be little confusing since both queries are nearly identical but I do require a foreach loop within a foreach loop.
  11. I have a foreach loop inside a foreach loop. I would like to shown only certain amount of results from the second foreach loop, but so far it's not working. It's showing all the records instead of the specified(5) amount. Can you see why? Here's my code. $get_records = $db->prepare("SELECT users.*, referrals.* FROM users LEFT JOIN referrals ON users.user_id = referrals.referred_user WHERE referrals.referred_by = :ref_by"); $get_records->bindParam(':ref_by', $global_user_id); $get_records->execute(); $result_records = $get_records->fetchAll(PDO::FETCH_ASSOC); if(count($result_records) > 0) { foreach($result_records as $row) { $ref_user_id = trim($row['user_id']); $get_records2 = $db->prepare("SELECT users.*, referrals.* FROM users LEFT JOIN referrals ON users.user_id = referrals.referred_user WHERE referrals.referred_by = :ref_by ORDER BY referrals.referral_id DESC LIMIT 5"); $get_records2->bindParam(':ref_by', $ref_user_id); $get_records2->execute(); $result_records2 = $get_records2->fetchAll(PDO::FETCH_ASSOC); if(count($result_records2) > 0) { foreach($result_records2 as $row) { $ref_username = trim($row['username']); $ref_email = trim($row['email']); $ref_joined = trim($row['joined']); ?> <div class="table-row"> <ul> <li class="th-1"><?php echo $ref_username; ?></li> <li class="th-2"><?php echo $ref_email; ?></li> <li class="th-3"><?php echo $ref_joined; ?></li> </ul> </div> <?php } } } }
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.