1

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

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
Ouroboros
  • 1,432
  • 1
  • 19
  • 41
  • You can't use `bind_result` with `SELECT *`. You'll need to list the fields in your `SELECT` query, so it knows what to bind to. – gen_Eric Feb 18 '16 at 20:21
  • @RocketHazmat What's the alternative? I don't want to list down all the columns, yet be able to get the result with all the columns, something that I was able to do with solution which uses bind_result(...). – Ouroboros Feb 18 '16 at 20:23
  • @RocketHazmat, BTW, I've been able to use bind_result using SELECT * queries, which dont use Joins. – Ouroboros Feb 18 '16 at 20:25
  • You were correct in trying to switch to `get_result`, but the query may be failing. You should check for errors and print them out: `if($result === FALSE){ printf("Error code: %d\n Error: %s", $stmt->errno, $stmt->error); }`. – gen_Eric Feb 18 '16 at 20:26
  • I'm getting the following Error code: 0\n Error: So looks like there's some problem. The same query works like a charm with bind_result. Unfortunately no error is printed. How to debug further, any leads? – Ouroboros Feb 18 '16 at 20:38

0 Answers0