Is there a reason why ORDER BY LIMIT not working inside a foreach loop?

[mannyson]

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
      }
    }
  }
}

[Psycho]

First some notes/comments:

1. You should not run queries in loops. You should run ONE query to get the data you need.

2. Do not select all the fields (e.g. * in Select statement). Just select the fields that you need.

3. Data should be trimmed before it is saved to the DB.

4. Don't intermix PHP & HTML. Use the PHP code to generate output into variables. Then when you start the HTML, just output the variables. Makes code much more manageable.

 

OK, the code makes no sense. The inner query is exactly the same as the outer query with an ORDER BU and LIMIT.  I assume you meant to use the $ref_user_id in that inner query. But, based on what I think you are trying to achieve, you should just need one simple query.

 

EDIT: If you need to get the first 5 users referred by each referrer - that can/should be done with a single query - not looping queries. But, it is a little more complicated. But, based on what I was seeing that wasn't what you were trying to achieve.

 

Try this (not tested):

//Query to get the first 5 records referred by selected referrer
$query = "SELECT users.username, users.email, users.joined
          FROM users
          INNER JOIN referrals ON users.user_id = referrals.referred_user
          WHERE referrals.referred_by = :ref_by
          ORDER BY referrals.referral_id DESC LIMIT 5"
$get_records = $db->prepare($query);
$get_records->bindParam(':ref_by', $global_user_id);
$get_records->execute();
$result = $get_records->fetchAll(PDO::FETCH_ASSOC);
 
//Create output
$output = '';
foreach($result as $row)
{
    $ref_username  = trim($row['username']);
    $ref_email     = trim($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";
}
 
//Put THIS in the HTML page
echo $output;

Edited June 13, 2017 by Psycho

[mannyson]

 

First some notes/comments:

1. You should not run queries in loops. You should run ONE query to get the data you need.

2. Do not select all the fields (e.g. * in Select statement). Just select the fields that you need.

3. Data should be trimmed before it is saved to the DB.

4. Don't intermix PHP & HTML. Use the PHP code to generate output into variables. Then when you start the HTML, just output the variables. Makes code much more manageable.

 

OK, the code makes no sense. The inner query is exactly the same as the outer query with an ORDER BU and LIMIT.  I assume you meant to use the $ref_user_id in that inner query. But, based on what I think you are trying to achieve, you should just need one simple query.

 

EDIT: If you need to get the first 5 users referred by each referrer - that can/should be done with a single query - not looping queries. But, it is a little more complicated. But, based on what I was seeing that wasn't what you were trying to achieve.

 

Try this (not tested):

//Query to get the first 5 records referred by selected referrer
$query = "SELECT users.username, users.email, users.joined
          FROM users
          INNER JOIN referrals ON users.user_id = referrals.referred_user
          WHERE referrals.referred_by = :ref_by
          ORDER BY referrals.referral_id DESC LIMIT 5"
$get_records = $db->prepare($query);
$get_records->bindParam(':ref_by', $global_user_id);
$get_records->execute();
$result = $get_records->fetchAll(PDO::FETCH_ASSOC);
 
//Create output
$output = '';
foreach($result as $row)
{
    $ref_username  = trim($row['username']);
    $ref_email     = trim($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";
}
 
//Put THIS in the HTML page
echo $output;

 

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.

[benanamen]

I still need to have 2 different foreach loops

 

 

No, No, and No.

 

This is a classic XY Problem. Rather than tell US how it is supposed to be done (which is wrong), tell us what the overall task is that you are trying to accomplish, not how you think it needs to be accomplished.

[Psycho]

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.

 

No, you THINK you need the two queries because you are not aware of the right way to get the data. I'm not trying to belittle you, but I have quite a bit of experience in this and know what can and cannot be done. Like I stated in my EDIT comment, I had a suspicion that you perhaps wanted something different - but it wasn't evident in your code.

 

Rather than me trying to decipher your code, why don't you explain what you are trying to accomplish. Looking at your code again, here is what I gather.

 

Query #1 is getting a list of all users that were referred by a particular individual.

Query #2 is getting the first 5 users referred by the users in query #1

 

So, to put in plain English, you want to show the first 5 users that were referred by users that were originally referred by the target user. Is that correct? If so, that can/should be done by a single query.

 

What would make this much easier is if you could provide sample exports of the two tables.

Edited June 13, 2017 by Psycho

[ginerjm]

Let's see:

 

mannyson - 11 posts;

Psycho - 11,000+ posts.

 

Who do we think is correct here?

[mannyson]

No, you THINK you need the two queries because you are not aware of the right way to get the data. I'm not trying to belittle you, but I have quite a bit of experience in this and know what can and cannot be done. Like I stated in my EDIT comment, I had a suspicion that you perhaps wanted something different - but it wasn't evident in your code.

 

Rather than me trying to decipher your code, why don't you explain what you are trying to accomplish. Looking at your code again, here is what I gather.

 

Query #1 is getting a list of all users that were referred by a particular individual.

Query #2 is getting the first 5 users referred by the users in query #1

 

So, to put in plain English, you want to show the first 5 users that were referred by users that were originally referred by the target user. Is that correct? If so, that can/should be done by a single query.

 

What would make this much easier is if you could provide sample exports of the two tables.

 

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).

Edited June 13, 2017 by mannyson

[Jacques1]

Who do we think is correct here?

 

The OP, actually. Translating this into a single query is far from trivial and requires either a fully-featured database system like PostgreSQL or a fair amount of SQL gymnastics. The “simple solution” above is wrong.

 

If you have PostgreSQL (which you should), you can join referrals with itself and then use the ROW_NUMBER() window function to restrict the number of second-level referrals:

SELECT
  username,
  email,
  joined
FROM (
    SELECT
        users.username,
        users.email,
        users.joined,
        ROW_NUMBER() OVER (PARTITION BY global_referrals.referred_user ORDER BY user_referrals.referral_id DESC) AS row_num
    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
    WHERE
        global_referrals.referred_by = :global_user_id
) AS refs
WHERE
  row_num <= 5
;

If you're stuck with toy software like MySQL, things will get nasty. You basically have to implement your own row counter and reset it for every global referral.

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
;

Edited June 13, 2017 by Jacques1

[mannyson]

 

The OP, actually. Translating this into a single query is far from trivial and requires either a fully-featured database system like PostgreSQL or a fair amount of SQL gymnastics. The “simple solution” above is wrong.

 

If you have PostgreSQL (which you should), you can join referrals with itself and then use the ROW_NUMBER() window function to restrict the number of second-level referrals:

SELECT
  username,
  email,
  joined
FROM (
    SELECT
        users.username,
        users.email,
        users.joined,
        ROW_NUMBER() OVER (PARTITION BY global_referrals.referred_user ORDER BY user_referrals.referral_id DESC) AS row_num
    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
    WHERE
        global_referrals.referred_by = :global_user_id
) AS refs
WHERE
  row_num <= 5
;

If you're stuck with toy software like MySQL, things will get nasty. You basically have to implement your own row counter and reset it for every global referral.

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
;

 

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

Edited June 13, 2017 by mannyson

[Jacques1]

Read the error message. It says your parameter is wrong. Now look at your parameter and compare it with the one in the query.

[mannyson]

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?

Edited June 13, 2017 by mannyson

[Psycho]

Also say I want to add the 3rd layer/loop to it, how would that look?

 

What do you mean by 3rd layer? Is the code you provided running within another loop?

[Jacques1]

Since you're a MySQL user: Don't overdo it. The complexity of those hacks quickly reaches a point where the query becomes unreadable. MySQL simply doesn't have the features required for more advanced tasks.

 

If you switch to a proper database system, you can have an arbitrary number of levels with a simple recursive query.

[mannyson]

What do you mean by 3rd layer? Is the code you provided running within another loop?

 

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?

[mannyson]

Since you're a MySQL user: Don't overdo it. The complexity of those hacks quickly reaches a point where the query becomes unreadable. MySQL simply doesn't have the features required for more advanced tasks.

 

If you switch to a proper database system, you can have an arbitrary number of levels with a simple recursive query.

 

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.

[Jacques1]

Good luck.

[Psycho]

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?

 

I have a simpler solution than what was provided (had to find a workaround for a bug). Please provide exactly what it is you want to output. All I saw in your original post was that you were trying to show the users that were referred by other that were referred by a target user. So, exactly, what data do you need to display?

[mannyson]

I have a simpler solution than what was provided (had to find a workaround for a bug). Please provide exactly what it is you want to output. All I saw in your original post was that you were trying to show the users that were referred by other that were referred by a target user. So, exactly, what data do you need to display?

 

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>

[Psycho]

Then, I don't know what this "3rd loop" is. I would imagine you want to show the referrer before each set of 5. And, if so, do you want to show a user that was referred by the $global_user_id who has not referred anyone themselves?

 

If you JUST want to show those three fields and you do not need to show who referred them, then this query will work

 

SELECT u.username, u.email, u.joined
 
FROM (
    SELECT *
          ,@rank := IF(@referred_by = referred_by, @rank + 1, 1) AS rank
          ,@referred_by := referred_by
    FROM (
        SELECT r.referred_by, r.referred_user as ref_user_id
 
        FROM referrals r
        JOIN referrals parent ON parent.referred_user = r.referred_by
        JOIN users u ON r.referred_user = u.user_id
 
        WHERE parent.referred_by = :ref_by -- VARIABLE
 
        ORDER BY r.referred_by, u.joined
        LIMIT 10000 -- A limit is needed here due to a bug
    ) subRefers
) ref
 
JOIN users u ON ref.ref_user_id = u.user_id
 
WHERE rank <= 5

 

But, if you need to show more data and/or data associated with who the intermediary referrer is, that can be added simple enough.

[Jacques1]

This is both more complex and wrong. You're assuming that the assignment expressions in the SELECT part are evaluated from left to right, but this is undefined, and the manual explicitly says not to use such constructs.

Edited June 13, 2017 by Jacques1

[mannyson]

Then, I don't know what this "3rd loop" is. I would imagine you want to show the referrer before each set of 5. And, if so, do you want to show a user that was referred by the $global_user_id who has not referred anyone themselves?

 

If you JUST want to show those three fields and you do not need to show who referred them, then this query will work

SELECT u.username, u.email, u.joined
 
FROM (
    SELECT *
          ,@rank := IF(@referred_by = referred_by, @rank + 1, 1) AS rank
          ,@referred_by := referred_by
    FROM (
        SELECT r.referred_by, r.referred_user as ref_user_id
 
        FROM referrals r
        JOIN referrals parent ON parent.referred_user = r.referred_by
        JOIN users u ON r.referred_user = u.user_id
 
        WHERE parent.referred_by = :ref_by -- VARIABLE
 
        ORDER BY r.referred_by, u.joined
        LIMIT 10000 -- A limit is needed here due to a bug
    ) subRefers
) ref
 
JOIN users u ON ref.ref_user_id = u.user_id
 
WHERE rank <= 5

But, if you need to show more data and/or data associated with who the intermediary referrer is, that can be added simple enough.

 

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']);

[Psycho]

@Jacques1: I was not aware of that. As it states "you might get the results you expect" which I did. But, if I am reading that page correctly, I can separate the statement that sets @referred_by and the one that reads it.

 

@OP: Try this

 

$query = "
SELECT ref.user_id, u.username, u.email, u.joined,
       ref.referred_by as ref_user_id, r.username as ref_username
 
FROM ( -- Returns the user id of the referrers and the 1st 5 referred users
    SELECT *
          ,@rank := IF(@referred_by = referred_by, @rank + 1, 1) AS rank
       --   ,@referred_by := referred_by
    FROM (
        SELECT r.referred_by, r.referred_user as user_id
              ,@referred_by := r.referred_by
        FROM referrals r
        JOIN referrals parent ON parent.referred_user = r.referred_by
        JOIN users u ON r.referred_user = u.user_id
 
        WHERE parent.referred_by = :ref_by -- VARIABLE
 
        ORDER BY r.referred_by, u.joined
        LIMIT 10000 -- A limit is needed here due to a bug
    ) sub
) ref
 
JOIN users u ON ref.user_id = u.user_id
JOIN users r ON ref.referred_by = r.user_id
 
WHERE rank <= 5
 
ORDER BY ref.referred_by, u.joined";

 

Here is a sample of how I created the output

 

$stmt = $db->prepare($query);
$stmt->bindParam(':ref_by', $global_user_id);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
 
$output = '';
$current_referrer = false;
foreach($result as $row)
{
    if($current_referrer != $row['ref_user_id'])
    {
        $output .= "<tr><th colspan='3'>Users referred by {$row['ref_username']}</th></tr>\n";
        $current_referrer = $row['ref_user_id'];
    }
    $output .= "<tr>\n";
    $output .= "<td>{$row['username']}</td>\n";
    $output .= "<td>{$row['email']}</td>\n";
    $output .= "<td>{$row['joined']}</td>\n";
    $output .= "</tr>\n";
}
 
?>
<html>
<body>
<table border='1'>
<?php echo $output; ?>
</table>
</body>
</html>

[mannyson]

 

@Jacques1: I was not aware of that. As it states "you might get the results you expect" which I did. But, if I am reading that page correctly, I can separate the statement that sets @referred_by and the one that reads it.

 

@OP: Try this

$query = "
SELECT ref.user_id, u.username, u.email, u.joined,
       ref.referred_by as ref_user_id, r.username as ref_username
 
FROM ( -- Returns the user id of the referrers and the 1st 5 referred users
    SELECT *
          ,@rank := IF(@referred_by = referred_by, @rank + 1, 1) AS rank
       --   ,@referred_by := referred_by
    FROM (
        SELECT r.referred_by, r.referred_user as user_id
              ,@referred_by := r.referred_by
        FROM referrals r
        JOIN referrals parent ON parent.referred_user = r.referred_by
        JOIN users u ON r.referred_user = u.user_id
 
        WHERE parent.referred_by = :ref_by -- VARIABLE
 
        ORDER BY r.referred_by, u.joined
        LIMIT 10000 -- A limit is needed here due to a bug
    ) sub
) ref
 
JOIN users u ON ref.user_id = u.user_id
JOIN users r ON ref.referred_by = r.user_id
 
WHERE rank <= 5
 
ORDER BY ref.referred_by, u.joined";

Here is a sample of how I created the output

$stmt = $db->prepare($query);
$stmt->bindParam(':ref_by', $global_user_id);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
 
$output = '';
$current_referrer = false;
foreach($result as $row)
{
    if($current_referrer != $row['ref_user_id'])
    {
        $output .= "<tr><th colspan='3'>Users referred by {$row['ref_username']}</th></tr>\n";
        $current_referrer = $row['ref_user_id'];
    }
    $output .= "<tr>\n";
    $output .= "<td>{$row['username']}</td>\n";
    $output .= "<td>{$row['email']}</td>\n";
    $output .= "<td>{$row['joined']}</td>\n";
    $output .= "</tr>\n";
}
 
?>
<html>
<body>
<table border='1'>
<?php echo $output; ?>
</table>
</body>
</html>

 

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
          }
        }
      }
    }
  }
}

Edited June 14, 2017 by mannyson

[mannyson]

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. 

Edited June 15, 2017 by mannyson

[Jacques1]

What you're saying makes no sense at all. But since this is clearly an amateur project, it probably doesn't matter.