I have created a function which returns me all the rows returned by executing a given query. It works perfectly fine for all the Non Join Queries, but as soon as I call this function for a "SELECT * FROM A,B ..." kind of Join Queries, it gives me the error:
exception 'ErrorException' with message mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement'
So I looked around on stackoverflow, and started using get_result(..)
instead of bind_result(..)
.
My previous code which used bind_result(...)
is as follows :
function fetchRows($mysqli, $qry, $bindStr, $bindVarArr) {
if ($stmt = $mysqli->prepare($qry)) {
$rows = NULL;
if($bindVarArr != NULL && sizeof($bindVarArr) > 0) {
array_unshift($bindVarArr, $bindStr);
// $commaList = implode(', ', $bindVarArr);
// $stmt->bind_param($bindStr, $commaList);
call_user_func_array(array($stmt,'bind_param'), makeValuesReferenced($bindVarArr));
}
// Execute the prepared query.
if (! $stmt->execute()) {
throw new DBException(DBException::STMT_ERR_MSG, DBException::STMT_ERR_CODE, $qry);
}
$stmt->execute();
$stmt->store_result();
$num_rows = $stmt->num_rows;
if ($num_rows <= 0) {
// No rows returned, may be a valid case
} else {
// bind results to named array
$meta = $stmt->result_metadata();
$fields = $meta->fetch_fields();
foreach($fields as $field) {
$result[$field->name] = "";
$resultArray[$field->name] = &$result[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), $resultArray);
// create object of results and array of objects
while($stmt->fetch()) {
/*
* If no result object is specified then create a new stdclass object
*/
$resultObject = new stdClass();
foreach ($resultArray as $key => $value) {
$resultObject->$key = $value;
}
$rows[] = $resultObject;
}
}
$stmt->close();
return $rows;
} else {
throw new DBException(DBException::STMT_ERR_MSG, DBException::STMT_ERR_CODE, $qry);
}
}
My new code which used get_result(...)
is as follows :
function fetchRows($mysqli, $qry, $bindStr, $bindVarArr) {
if ($stmt = $mysqli->prepare($qry)) {
$rows = NULL;
if($bindVarArr != NULL && sizeof($bindVarArr) > 0) {
array_unshift($bindVarArr, $bindStr);
// $commaList = implode(', ', $bindVarArr);
// $stmt->bind_param($bindStr, $commaList);
call_user_func_array(array($stmt,'bind_param'), makeValuesReferenced($bindVarArr));
}
// Execute the prepared query.
if (! $stmt->execute()) {
throw new DBException(DBException::STMT_ERR_MSG, DBException::STMT_ERR_CODE, $qry);
}
$stmt->execute();
$stmt->store_result();
$num_rows = $stmt->num_rows;
if ($num_rows <= 0) {
// No rows returned, may be a valid case
} else {
$result = $stmt->get_result();
while ($resultArray = $result->fetch_assoc()) {
/*
* If no result object is specified then create a new stdclass object
*/
$resultObject = new stdClass();
foreach ($resultArray as $key => $value) {
$resultObject->$key = $value;
}
$rows[] = $resultObject;
}
}
$stmt->close();
return $rows;
} else {
throw new DBException(DBException::STMT_ERR_MSG, DBException::STMT_ERR_CODE, $qry);
}
}
But now, it gives me an error as follows (On all possible queries, which were working with previous code):
PHP Fatal error: Call to a member function fetch_assoc() on boolean
It clearly suggests that the statement's get_result may have failed, but what could be the problem? The same query works perfectly fine otherwise with previous code which uses bind_result.
I'm using PHP 5.5 if that helps