php - Returning large data sets with MySQLi -

i've been working on basic class handle of simple mysqli queries (e.g. simple select froms), , have run bit of issue related how i've been returning data far. here example of function in question:

public function selectfromwhere($conn, $columns, $table, $where, $vars, $limit) {     $sql = "select %s %s %s%s";     $l = ($limit == 0) ? '' : " limit " . $limit;     $sql = sprintf($sql, $columns, $table, $where, $l);     if($query = $conn->prepare($sql)) {         $a = $this->parameterizearray($vars);         if (call_user_func_array(array($query, "bind_param"), $this->refvalues($a))) {             $data = array();             $query->execute();             $row = $query->get_result();             while($r = $row->fetch_assoc()) {                 array_push($data, $r);             }             if (count($data) == 1 && $limit == 1) {                 $data = $data[0];             }             return $data;         }else{             return false;         }     }else{         return false;     } } 

the function works splendidly, taking array of variables , formatting them work bind_param. problem glaring one, fact add each row array, , return array. (if limit set 1, return simple associative array) know terrible practice , didn't think of when creating function, i've run obvious memory problem.. large data sets take way memory , cause issues. (e.g. hitting set php memory limit)

the idea return array each row inside array, can run foreach on when need to. avoid having bother same 4-5 mysqli lines each , every single query.

should return fetch_assoc() response , run while loop in code uses function instead of foreach loop? that's solution i'm seeing, wanted post here , see if has had similar adventures , share insight.

first answer question. http://php.net/manual/en/mysqli-result.fetch-all.php

$row->fetch_all(mysqli_assoc); 

or if question wanted returned simple array rather associative array

$row->fetch_all(mysqli_num); 

second should not repeating lines of code, had function not existed, or if misunderstand question

$row = $query->get_result(); return arraymydata($row) ... function arraymydata($row){   $data = array();   while($r = $row->fetch_assoc()) {     array_push($data, $r);   }   if (count($data) == 1 && $limit == 1) {     $data = $data[0];   }   return $data; } 

should representation of should doing, don't repeat yourself.

edit if want go further , deal memory problem assuming working on each line independently

$row = $query->get_result(); return arraymydata($row, 'mynumbercruncher') ... function arraymydata($row, $func){   $data = array();   while($r = $row->fetch_assoc()) {     /*array_push($data,*/      $func($r);     /*);*/   }   if (count($data) == 1 && $limit == 1) {     $data = $data[0];   }   return $data; } function mynumbercruncher{ ... }