PHP MySQLi prepared instructions and select a subset of columns

Question

I use MySQLi and PHP to call a MySQL stored procedure 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 full set of returned columns:

Note that all columns must be connected after mysqli_stmt_execute() and before mysqli_stmt_fetch() called.

Do I need to add code for those columns that I'm not interested in? If so, the application crashes if an extended result set is created in MySQL in the future, or even columns are rebuilt. Is there a workaround for this?

Answer 1

I assume that you simply do not want to write out all of these variables for the bind_result () function. You can use the function as shown below instead of the bind_result () function. Pass it your $ stmt object, and you will return an array of standard objects with the fields you need.

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

Answer 2

Try using php_fields php method:

 array mysqli_fetch_fields ( mysqli_result $result ) 

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

Answer 3

Jonathan Mayhack's answer led me in the right direction. In PHP, the bind_result page , nieprzeklinaj provides a function called fetch() . It is working; use it as follows:

 $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 inside the same PHP file do not work with this method.