PHP MySQLi准备了语句并获取列的子集 [英] PHP MySQLi prepared statements and fetching subset of columns

问题描述

我正在使用MySQLi和PHP调用带有准备好的语句的存储的MySQL例程.它返回包含数十列的结果集.

I am using MySQLi and PHP to call a stored MySQL routine with prepared statements. It returns a result set with dozens of columns.

$stmt = $dbconnection->prepare("CALL SomebodysDbProcedure(?);");
$stmt->bind_param("s", $idvalue);
$stmt->execute();
$stmt->bind_result($col1, $col2, $col3, ...);

但是，我只对输出列的一个子集感兴趣.

However, I am only interested in a subset of the output columns.

文档说，bind_result()是处理返回列的完整集:

The documentation says bind_result() is required to handle the complete set of returned columns:

请注意，所有列必须在mysqli_stmt_execute()和 在调用mysqli_stmt_fetch()之前.

Note that all columns must be bound after mysqli_stmt_execute() and prior to calling mysqli_stmt_fetch().

我是否还需要为我不感兴趣的那些列添加代码?如果是这样，如果将来扩展MySQL存储的例程结果集甚至重新排列列，应用程序将中断.有解决方法吗?

Do I need to add code also for those columns I'm uninterested in? If so the application will break if the MySQL stored routine result set is expanded in the future, or even columns rearranged. Is there a workaround for this?

推荐答案

我假设您只是不想为bind_result()函数写出所有这些变量.您可以使用下面的函数代替bind_result()函数.将其传递给$ stmt对象，您将获得带有所需字段的标准对象数组.

I'm assuming that you just don't want to write out all those variables for the bind_result() function. You could use a function like below instead of the bind_result() function. Pass it your $stmt object and you'll get back an array of standard objects with the fields you want.

function getResult($stmt)
{
    $valid_fields = array('title', 'date_created'); // enter field names you care about

    if (is_a($stmt, 'MySQLi_STMT')) {
        $result = array();

        $metadata = $stmt->result_metadata();
        $fields = $metadata->fetch_fields();

        for (; ;)
        {
            $pointers = array();
            $row = new \stdClass();

            $pointers[] = $stmt;
            foreach ($fields as $field)
            {
                if (in_array($field->name, $valid_fields)) {
                    $fieldname = $field->name;
                    $pointers[] = &$row->$fieldname;
                }
            }

            call_user_func_array('mysqli_stmt_bind_result', $pointers);

            if (!$stmt->fetch())
                break;

            $result[] = $row;
        }

        $metadata->free();

        return $result;
    }
    return array();
}

这篇关于PHP MySQLi准备了语句并获取列的子集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！