mysqli结果的数量取决于内部的提取

Question

I am fetching rows from a mysql table (jobs). Inside of that fetch, I am also fetching from another table (accounts) [to receive account api keys all depending on what ID_ASSOC is attacted to the job]: below is the code

$sql = "SELECT * FROM jobs";
$query = mysqli_query($db_conx, $sql);
while($row = mysqli_fetch_assoc($query)){
   echo $row['action'];
   echo "<br/>";
   $job_poster_id = $row['id_assoc'];

        $sql = "SELECT * FROM accounts WHERE id_assoc='$job_poster_id'";
        $query = mysqli_query($db_conx, $sql);
        while($rows = mysqli_fetch_assoc($query)){
        $username = $rows['twitter_username'];
            $consumer_key = $rows['consumer_key'];
            $consumer_secret = $rows['consumer_secret'];
            $access_token = $rows['access_token'];
            $access_token_secret = $rows['access_token_secret'];
        }

  echo $job_poster_id ;
  echo "<br/>";
  echo $twitter_username;
  echo "<br/>";
  echo "----------------------------------";
  echo "<br/>";
}

OUTPUT:

specific-message 
4 
admin
----------------------------------

---

When I do this, I only get one row output..and I can't seem to find out why. I want the above out put to repeat as many times as it has rows, and it's only doing one row (with the account fetch in the code). However when I do it without the internal fetch (accounts fetch), it returns multiple rows just as desired. Why is this? (below is sample code WITHOUT the accounts fetch):

$sql = "SELECT * FROM jobs";
$query = mysqli_query($db_conx, $sql);
while($row = mysqli_fetch_assoc($query)){
  echo $row['action'];
  echo "<br/>";
  $job_poster_id = $row['id_assoc'];

  echo $job_poster_id ;
  echo "<br/>";
  echo "----------------------------------";
  echo "<br/>";
} 

OUTPUT:

specific-message
4
----------------------------------
specific-message
1
----------------------------------
specific-message
2
----------------------------------

Answer 1

$query = mysqli_query($db_conx, $sql);
while($row = mysqli_fetch_assoc($query)){
    echo $row['action'];
    echo "<br/>";
    $job_poster_id = $row['id_assoc'];

    $sql = "SELECT * FROM accounts WHERE id_assoc='$job_poster_id'";
    $query = mysqli_query($db_conx, $sql);

The problem is that you're using $query for the inner and the outer query.

When the inner query runs, and it steps through the loop, it's iterating to the end of the result set; when the outer while loop runs, mysqli_fetch_assoc($query) is returning false, because you're already at the end of the result set - just not the result set you were expecting.

You can fix this by renaming one of the $query variables.