mysqli准备语句中返回的可变列数 [英] Variable amount of columns returned in mysqli prepared statement

问题描述

我遇到一种情况，正在生成一个动态查询，可以在多个表中从1到300多个不同的列中选择任意一个.目前，仅执行查询就可以正常工作，但是使用准备好的语句遇到的问题是我不知道该如何处理以下事实:我不知道每次将要查询多少列，因此不知道如何处理结果. 我认为bind语句会有所帮助的原因是，一旦此查询运行一次，它将 最有可能 (尽管并非总是如此)以完全相同的参数再次运行.

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.

目前我有这样的东西:

$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;
}

我知道这无法按我的意愿进行，我的问题是如何从查询中取回数据.是否可以像标准mysqli查询一样通过列名将列带回到关联数组中?

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?

推荐答案

使用VolkerK的mysqli_statement->result_metadata()建议，尽管性能并没有提高，但我可以将以下代码组合在一起以实现所需的功能而不是使用标准查询.我得到了statement->result_metadata()来建立一个关联数组来调用bind_result.我建立一个bind_result语句作为字符串，并eval.我知道这不是特别安全，但这是我的第一遍.

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          
    }

这篇关于mysqli准备语句中返回的可变列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！