SQL中的while循环只循环一次

Question

I have a rest service where I send a get request to a table in my database. I want to build an array with the response. I get the array-structure I want with this code but the problem is that it only loops once. Why is this? If I change the second $result to $result2 it returns false instead of the encoded array.

/**
 * @param int $id
 * @url periodicalitem
 * @return string
 */
public function getPeriodicalItem($id){

    $mysqli = $this->db-> getConnection();

    $query  = 'SELECT * FROM periodicalitem WHERE
        periodical_id  = ' . $id;

    $result = $mysqli->query($query);
    $arr = array();

    while ($row = $result->fetch_assoc()) {

        $query = 'SELECT * FROM inst_codes WHERE id = ' . $row['inst_code'] . '';
        $result = $mysqli->query($query);


        while ($row2 = $result->fetch_assoc()) {

            if($row['inst_code'] == $row2['id'] ){
                $arr[$row2['id']] = array('name' => $row2['name'],
                                          'data' => $arr[$row2['id']]['data'] ? array_push($arr[$row2['id']]['data'], $row) : array($row) );

            }
        }

    } 

    return json_encode($arr); 

}

Answer 1

You are over-writing $result = $mysqli->query($query); inside the loop. Use another variable

public function getPeriodicalItem($id){

    $mysqli = $this->db-> getConnection();

    $query  = 'SELECT * FROM periodicalitem WHERE
        periodical_id  = ' . $id;

    $result = $mysqli->query($query);
    $arr = array();

    while ($row = $result->fetch_assoc()) {

        $query = 'SELECT * FROM inst_codes WHERE id = ' . $row['inst_code'] . '';
        $result1 = $mysqli->query($query);


        while ($row2 = $result1->fetch_assoc()) {

            if($row['inst_code'] == $row2['id'] ){
                $arr[$row2['id']] = array('name' => $row2['name'],
                                          'data' => $arr[$row2['id']]['data'] ? array_push($arr[$row2['id']]['data'], $row) : array($row) );

            }
        }

    } 

    return json_encode($arr); 

}