How to Read Stored Procedure Output Parameter Result Sets Using PDOStatement::nextRowset?

gitbox 2025-09-08

<span><span><span class="hljs-meta"><?php</span></span><span>
</span><span><span class="hljs-comment">// This part of the code is unrelated to the article content, mainly for demonstrating environment setup</span></span><span>
</span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">&#039;mysql:host=localhost;dbname=testdb;charset=utf8mb4&#039;</span></span><span>;
</span><span><span class="hljs-variable">$username</span></span><span> = </span><span><span class="hljs-string">&#039;root&#039;</span></span><span>;
</span><span><span class="hljs-variable">$password</span></span><span> = </span><span><span class="hljs-string">&#039;&#039;</span></span><span>;
</span><span><span class="hljs-variable">$options</span></span><span> = [
    PDO::</span><span><span class="hljs-variable constant_">ATTR_ERRMODE</span></span><span> => PDO::</span><span><span class="hljs-variable constant_">ERRMODE_EXCEPTION</span></span><span>,
    PDO::</span><span><span class="hljs-variable constant_">ATTR_DEFAULT_FETCH_MODE</span></span><span> => PDO::</span><span><span class="hljs-variable constant_">FETCH_ASSOC</span></span><span>,
];
</span><span><span class="hljs-keyword">try</span></span><span> {
    </span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>, </span><span><span class="hljs-variable">$username</span></span><span>, </span><span><span class="hljs-variable">$password</span></span>, </span><span><span class="hljs-variable">$options</span></span><span>);
} </span><span><span class="hljs-keyword">catch</span></span><span> (PDOException </span><span><span class="hljs-variable">$e</span></span><span>) {
    </span><span><span class="hljs-keyword">die</span></span><span>(</span><span><span class="hljs-string">"Database connection failed: "</span></span><span> . </span><span><span class="hljs-variable">$e</span></span>-></span><span><span class="hljs-title function_ invoke__">getMessage</span></span><span>());
}
</span><span><span class="hljs-meta">?></span></span><span>
<p><hr></p>
<p><h1>How to Use <code></span>PDOStatement::nextRowset<span>

PHP Code Example

&lt;?php
</span><span><span class="hljs-comment">// Bind parameters to call the stored procedure</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$pdo</span></span>-><span><span class="hljs-title function_ invoke__">prepare</span></span>(<span><span class="hljs-string">"CALL sp_get_user_info(:user_id)"</span></span>);
</span><span><span class="hljs-variable">$stmt</span></span>-><span><span class="hljs-title function_ invoke__">bindValue</span></span>(<span><span class="hljs-string">&#039;:user_id&#039;</span></span>, <span><span class="hljs-number">123</span></span>, PDO::<span><span class="hljs-variable constant_">PARAM_INT</span></span>);
</span><span><span class="hljs-variable">$stmt</span></span>-><span><span class="hljs-title function_ invoke__">execute</span></span>();

</span><span><span class="hljs-comment">// Read the first result set: user information</span></span><span>
</span><span><span class="hljs-variable">$userInfo</span></span> = <span><span class="hljs-variable">$stmt</span></span>-><span><span class="hljs-title function_ invoke__">fetchAll</span></span>();
</span><span><span class="hljs-keyword">echo</span></span> <span><span class="hljs-string">"User Information:\n"</span></span>;
</span><span><span class="hljs-title function_ invoke__">print_r</span></span>(<span><span class="hljs-variable">$userInfo</span></span>);

</span><span><span class="hljs-comment">// Move to the next result set</span></span><span>
</span><span><span class="hljs-keyword">if</span></span> (<span><span class="hljs-variable">$stmt</span></span>-><span><span class="hljs-title function_ invoke__">nextRowset</span></span>()) {
    <span><span class="hljs-comment">// Read the second result set: post count</span></span>
    <span><span class="hljs-variable">$postCount</span></span> = <span><span class="hljs-variable">$stmt</span></span>-><span><span class="hljs-title function_ invoke__">fetchColumn</span></span>();
    <span><span class="hljs-keyword">echo</span></span> <span><span class="hljs-string">"Post Count: <span class="hljs-subst">$postCount</span></span></span><span>\n";
}
?&gt;

Key Points

nextRowset() is used to move to the next result set, returning true if more result sets are available.
Use fetch() or fetchAll() to read data from the current result set.
A stored procedure can return multiple result sets, and sometimes output parameters are returned through SELECT statements. You can traverse them using nextRowset().
When working with PDO, remember to catch exceptions to ensure secure connections and executions.

Conclusion

With PDOStatement::nextRowset(), you can easily read multiple result sets returned by stored procedures. This is especially useful for handling output parameters or multiple queries. Using this method effectively makes PHP and stored procedure interactions more flexible and efficient.

Related Tags:
PDOStatement