Variable amount of columns returned in mysqli prepared statement

Question

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?

Answer 1

You can use mysqli-stmt->result_metadata() and mysqli_result->field_count. E.g.

$mysqli = new mysqli('localhost', 'localonly', 'localonly', 'test') or die('!mysqli');
$mysqli->query('CREATE TEMPORARY TABLE foo (x int,y int,z int)') or die('!temp table');

$stmt = $mysqli->prepare('SELECT * FROM foo');
$stmt->execute();
$result = $stmt->result_metadata();

echo 'The result set has ', $result->field_count, ' columns';

Answer 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          
    }