Unable to get data from sql table in php script where parent column is also from sql query-CodePudding

I have a forum system where user A refers user B. Then user B refers user C. Only then it should be allowed to view. And I want to list user C under user A as well. Here is the code I tried to use. It generated uid of B successfully. But I am unable to get to C part. Here is the code.



// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT uid FROM mybb_users WHERE referrer='24'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    $abc= $row["uid"];   
  }
} else {
  echo "0 results";
}


$sql = "SELECT uid FROM mybb_users WHERE referrer='$abc'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
echo $row["uid"];   
  }
} else {
  echo "0 results";
}

It would be great to know what I am doing wrong?

I tried to select where clause but it returns to zero result and does not work as it should be working. The SQL I used is


$sql = "SELECT uid FROM mybb_users WHERE referrer='$abc'";

and it returns to error message 0.

CodePudding user response：

Because of one user can refer more than 1, so when get user by ref, the result might be an array.

So I think you should do like this :

Write a funciton to get user by referrer

    function getUserByRef($ref) {
       if (is_array($ref)) {
          $sql = "SELECT uid FROM mybb_users WHERE referrer IN ($ref)";
       } else {
          $sql = "SELECT uid FROM mybb_users WHERE referrer = '$ref')";
       }
       
       $users = [];
       for each row() {
            $users[] = $row['uid']
       }
       ......// return array of user

       return $users;
    }

Get detail User A => Return 1 record $userA
Get User B

$refUserLevel1 = getUserByRef($userA['uid']); => Return Array $uids

Get user C

$refUserLevel2 = getUserByRef($uids);

CodePudding user response：

I assume that A > B > C (A refers B, B refers C) and A's uid=24 , am I correct ? However, If A refers B AND D then the system may end up getting D in the 1st select query (since you have a while loop) , who (I mean D) refers nobody and so will echo "0 results";

So, better re-construct your loop so that the 2nd query will be executed for all the records selected in the 1st query:

<?php

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT uid FROM mybb_users WHERE referrer='24'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {

  while($row = mysqli_fetch_assoc($result)) {
    $abc= $row["uid"];   

    $sql = "SELECT uid FROM mybb_users WHERE referrer='$abc'";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
       while($row = mysqli_fetch_assoc($result)) {
       echo $row["uid"];   
      }
    } else {
       echo "0 results";
    }
  }
  
} else {
  echo "0 results";
}

?>

In real practice you may use break to jump out of the while loop when it is identified that the two referrals condition is already met. So feel free to amend the code to suit your real needs.

For the above case, the value '24' is hardcoded so is OK. But if it is supplied by user then you should change to use parameterized prepared statements in your select queries which are resilient against SQL injection. For details, please refer to the following links:

For Mysqli:

https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

For PDO:

https://www.php.net/manual/en/pdo.prepare.php