Jump to content

mannyson

Members
  • Posts

    20
  • Joined

  • Last visited

Posts posted by mannyson

  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.  

    @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
              }
            }
          }
        }
      }
    }
    
  3. 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']);
    
  4. 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>
    
  5. 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.

  6.  

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

  8.  

    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.

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