CODEWITHSUNDEEP
phpmysqlmysqli
Gruber
Gruber

Reputation: 4568

PHP MySQLi prepared statements and fetching subset of columns

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.

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

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

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?

Upvotes: 4

Views: 1323

Answers (3)

Gruber
Gruber

Reputation: 4568

The answer of Jonathan Mayhak guided me in the right direction. On PHP bind_result page, nieprzeklinaj provides a function called fetch(). It works; use it like this:

$stmt = $conn->prepare("CALL SomebodysDbProcedure(?);");
$stmt->bind_param("s", $idvalue);
$stmt->execute();
$sw = (array)(fetch($stmt));
$s = $sw[0]; // Get first row
$dateCreated = $s['date_created']; // Get field date_created

Edit: Unfortunately successive calls within the same PHP file don't seem to work with this method.

Upvotes: 1

Jonathan Mayhak
Jonathan Mayhak

Reputation: 12536

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

Upvotes: 1

osk386
osk386

Reputation: 514

Try using fetch_fields php method:

array mysqli_fetch_fields ( mysqli_result $result )

http://php.net/manual/en/mysqli-result.fetch-fields.php

Upvotes: 0

Related Questions