Variable amount of columns returned in mysqli prep

问题:

I have a situation where a dynamic query is being generated that could select anywhere from 1 to over 300 different columns across multiple tables. It currently works fine just doing a query, however the issue I'm running into in using a prepared statement is that I do not know how to handle the fact that I don't know how many columns I will be asking for each time and therefor don't know how to process the results. The reason I believe a bind statement will help is because once this query is run once, it will most likely (though not always) be run again with the exact same parameters.

Currently I have something like this:

$rows = array();
$this->statement = $this->db->prepare($query);
$this->statement->bind_param('i',$id);
$this->statement->execute();
$this->statement->bind_result($result);
while($this->statement->fetch())
{
   $rows[] = $result;
}

I know this doesn't work as I want it to, my question is how do I get the data back out of the query. Is it possible to bring the columns back in an associative array by column name, like a standard mysqli query?

回答1:

I prever not to use eval, this is my solution (similar to ashurexm):

$md = $stmt -> result_metadata();
$fields = $md -> fetch_fields();
$result = new stdClass(); // Actual result object
$params = array(); // Array of fields passed to the bind_result method
foreach($fields as $field) {
    $result -> {$field -> name} = null;
    $params[] = &$result -> {$field -> name};
}
call_user_func_array(array($stmt, 'bind_result'), $params);

回答2:

Using VolkerK's suggestion of mysqli_statement->result_metadata() I was able to fashion together the following code that accomplishes what I'm looking for, though the performance isn't any faster than using a standard query. I get the statement->result_metadata() to build an associative array to call bind_result on. I build up a bind_result statement as a string and eval it. I know this isn't particularly safe but it is my first pass.

public function executePreparedStatement()
{
    if($this->statement->execute())
    {
        $this->record = array();
        $md = $this->statement->result_metadata();
        $fields = $md->fetch_fields();

        $bindResult = '$this->statement->bind_result(';
        foreach($fields as $field)
        {
            $bindResult .= "\$this->record['" . $field->name . "'],";
        }

        $bindResult = substr($bindResult,0,strlen($bindResult) - 1) . ');';

        eval($bindResult);
        return true;
    }
    else
    {
        $this->error = $this->db->error;
        return false;
    }
}

    ...
    $this->prepareStatement($query);
    $this->bindParameter('i',$runId);
    if($this->executePreparedStatement())
    {
        $report = new Report();
        while($this->statement->fetch())
        {
            $row = $this->record;
            $line = array();
            foreach($row as $key => &$value)
            {
                array_push($line,$value);
            }
            $report->addLine($line);
        }
        return $report          
    }

回答3:

This is the very reason why mysqli should never be used with prepared statements.
So, you have to use PDO instead, which will make your executePreparedStatement() into three lines:

function executePreparedStatement($sql,$params) {
    $stm = $this->db->prepare($sql);
    $stm->execute($params);
    return $stm->fetchAll();
}

used like this

$sql  = "SELECT * from news WHERE category=?";
$data = $this->executePreparedStatement($sql,$_GET['category']);