While-loop in SQL only loops once

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

}

just curious, why do you loop two times instead of joining the tables in your sql query? — Raphael Müller
– Raphael Müller, Commented Jun 10, 2016 at 9:20
@RaphaelMüller I thought it was easier since the second query depends on the first one. But Im sure there might be a way to join them as well? — Nirakander
– Nirakander, Commented Jun 10, 2016 at 9:42

Aju John · Accepted Answer · 2016-04-27 14:41:06Z

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

}

answered Apr 27, 2016 at 14:41

Aju John

2,2441 gold badge14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nirakander Over a year ago

It appears that the while-loop is working properly. Its the array_push that is overwriting the value in each iteration, only displaying the last value.

Nirakander · Accepted Answer · 2016-04-29 08:36:50Z

The problem was that each iteration created a new array instead of appending the already existing one. Here is my working code.

/**
 * @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()) {
        $row = array_map("utf8_encode", $row);


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

        while ($row2 = $result2->fetch_assoc()) {
        $row2 = array_map("utf8_encode", $row2);

                   $current = array(
                                     'id'=>$row['id'], 
                                     'volume'=>$row['volume'],
                                     'code' =>$row['code'],
                                     'archive' => $row['archive']
                                  );


             if(!isset($arr[$row2['id']])){
                $arr[$row2['id']] = array();
                $arr[$row2['id']][] = array('name' => $row2['name'],
                                            'prefix' => $row2['prefix'],
                                            'show' => 'true');
            }


                if(isset($arr[$row2['id']]['data'])){ 
                    $arr[$row2['id']]['data'][] = $current;
                }else{
                    $arr[$row2['id']]['data'] = array($current); 
                }    
        }


    } 
    //$arr['2']['data'][] = array($current); 

    return json_encode($arr); 

}

Collectives™ on Stack Overflow

While-loop in SQL only loops once

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related