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?

score 3 · Answer 1 · answered Mar 12 '13 at 09:13

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);

ashurexm · Accepted Answer · 2010-05-27T23:19:58.367

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          
    }

I would be interested to know if this would be considered a 'bad' way to solve my issue? Bad as in insecure (I assume so because of the eval, but given proper sanitation upstream would this be ok?), or bad as in poor performance. The query itself is a JOIN on up to 15 tables all linked by one column and ordered by another column they all share. As stated in the question, there could be as many as 300+ columns being requested. The database is MySQL InnoDB with indexes on the join and order by columns. — ashurexm, May 28 '10 at 03:19
I got an undefined property error on this line `$bindResult = "$stmt->statement->bind_result(";`, and had to add a \ at the begining, so it was like this: `$bindResult = "\$stmt->statement->bind_result(";` also I'm not using this in it's own function, so having a few other problems too. I'm new to PHP, and I'm just trying to show the contents of a table. It seems a lot harder than it should be. Any suggestions? — Travis Heeter, Oct 07 '12 at 17:37

score 0 · Answer 3 · edited May 23 '17 at 12:02

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']);

Variable amount of columns returned in mysqli prepared statement

3 Answers3

Linked