CODEWITHSUNDEEP
phpmysqlmysqli
AlexHeuman
AlexHeuman

Reputation: 1006

Returning multi-dimensional associative array with mysqli prepared statements

I am trying to return a multidimensional associative array from my MySql Database with the Format

$array[0]['user']
$array[0]['dateCompleted']
$array[0]['etc']
$array[1]['user']
$array[1]['dateCompleted']
$array[1]['etc']

...

Here is my code:

$mysqli = new mysqli(DBHOST, DBUSER, DBPASSWORD, DBDATABASE);
//Clean input
$idUser = trim($_SESSION['tmp01']);
/* create a prepared statement */
$stmt = $mysqli->prepare("SELECT user, dateCompleted, workType, workPrice FROM workDone WHERE user=? ORDER BY dateCompleted DESC");

/* bind parameters for markers */
$stmt->bind_param("i", $idUser);

$stmt->execute();

$data = $stmt->result_metadata();
$fields = array();
$row = array();
$rows = array();

$fields[0] = &$stmt;
$count = 1;

// this dynamically creates an array where each key is the name of the field. 
while ($field = mysqli_fetch_field($data)) {
    $fields[$count] = &$row[$field->name];
    $count++;
}

// this calls bind_result() to each member of this $row array  
call_user_func_array(array($stmt, 'bind_result'), $row);       //<--problem                      
while ($stmt->fetch())
    array_push($rows, $row);

$results = (count($rows) == 0) ? false : $rows;
//print_r($results);
return $results;

$stmt->close();

It works when I use the SQL statement "SELECT * FROM users ..."), but I overrun my servers memory by doing that. Instead I use the above code, but what it does it return a the same instance for each row I have.

In other words if I have four distinct rows in my MySQL database, it will return the first row four times.

P.S.

My server does not have mysqlnd enabled.

Upvotes: 0

Views: 1623

Answers (1)

user1726343
user1726343

Reputation:

Your approach looks to be correct, but you seem to be passing incorrect arguments to call_user_func_array. Try this:

$data = $stmt->result_metadata();
$fields = array();
$currentrow = array();
$results = array();

// Store references to keys in $currentrow
while ($field = mysqli_fetch_field($data)) {
  $fields[] = &$currentrow[$field->name];
}

// Bind statement to $currentrow using the array of references
call_user_func_array(array($stmt,'bind_result'), $fields);

// Iteratively refresh $currentrow array using "fetch", store values from each row in $results array
$i = 0;
while ($stmt->fetch()) {
  $results[$i] = array(); //this is supposed to be outside the foreach
  foreach($currentrow as $key => $val) {
    $results[$i][$key] = $val;
  }
  $i++;
}

return $results;

Upvotes: 2

Related Questions