CODEWITHSUNDEEP
phpmysqliprepared-statement
Brian Leishman
Brian Leishman

Reputation: 8555

MySQL Prepared Statement Binds do not Match Fields

I have a function to aid me in making prepared calls to the database, which I've never had a problem with until now, which is the following error:

Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in /var/www/file.php

This is my function

function GetPrepared($SQL, $Types = null, $Params = null) {
    global $dbs;
    # create a prepared statement
    $Statement = $dbs->prepare($SQL);

    # bind parameters for markers
    # but this is not dynamic enough...
    //$stmt->bind_param("s", $parameter);

    if (!empty($Statement)) {
        if ($Types && $Params) {
            $bind_names[] = $Types;
            for ($i = 0; $i < count($Params); $i++) {
                $bind_name = 'bind' . $i;
                $$bind_name = $Params[$i];
                $bind_names[] = &$$bind_name;
            }
            $return = call_user_func_array(array($Statement, 'bind_param'), $bind_names);
        }

        # execute query 
        $Statement->execute();

        # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
        $meta = $Statement->result_metadata();


        if (!empty($meta)) {
            while ($field = $meta->fetch_field()) {
                $var = $field->name;
                $$var = null;
                $parameters[$field->name] = &$$var;
            }

            call_user_func_array(array($Statement, 'bind_result'), $parameters);

            while ($Statement->fetch()) {
                return $parameters;
                //print_r($parameters);      
            }
        }
    }
    return false;
    # close statement
    $Statement->close();
}

And here's the query that I'm attempting to run, but getting that error above

$OptionData = GetPrepared("select * from `options` join `fields` on `fields`.`FieldID`=`options`.`FieldID` where `CompanyID`=? and (replace(`options`.`Name`, ' ', '') =? or `Slug`=?);", 'sss', array($_CompanyID, $_GET['id'], $_GET['id']));

The values of $_GET['id'] is text and $_CompanyID is a guid, both of which are set, because I can echo out both of their contents before the call is made. Also, if I go ahead and take this query into Workbench and replace the question marks with their values, the query works just fine. What am I missing?

Upvotes: 1

Views: 194

Answers (1)

Cristik
Cristik

Reputation: 32815

Since the code worked before and you didn't made any changes, the problem must come from the database results. Most likely the options and fields tables now have common fields (e.g. id or name), which cause your $parameters array to have in the end less items than there are columns, as the $parameters[$field->name] = &$$var; assignment can't handle multiple fields with the same name.

I'd recommend that you use the approach suggested here, and create a "regular" php array instead of an associative one:

while($field = $meta->fetch_field()) {
    $variables[] = &$data[$field->name]; // pass by reference
}

Upvotes: 1

Related Questions