mysqli_query and multiple resultsets

[soulseekah]

The WordPress Database API does not expose a way to work with multiple resultsets.

Multiple resultsets are returned by queries that have several sets of results available and this is often the case with stored procedures and the usual way is to call next_result and use/store_result on the mysqli connection, however the connection is abstracted away behind the undocumented $wpdb->dbh field.

-- Test procedure for out of sync mysqli commands

DROP PROCEDURE IF EXISTS `mysqli_procedure_test`;
DELIMITER ;;

CREATE PROCEDURE `mysqli_procedure_test`()
BEGIN
    SELECT * FROM `wp_posts` LIMIT 1;
    SELECT * FROM `wp_posts` LIMIT 1;
END;;
DELIMITER ;

When calling this procedure (apart from the issues outlined in ticket #28155) you can only access the first resultset using the documented APIs. To fetch the second one one would have to use the mysqli API directly.

Need to come up with additional public methods to work with these via the Database API instead. There must be a way for a user to fetch the next resultset if there's one, or make this transparent somehow, perhaps using a $wpdb->call( $procedure, $arguments ) invocation in the case of procedures? And something like $wpdb->next_results for everything else?

Needs brainstorming.

[chriscct7]

@pento I think I like the idea of this. Any thoughts

[pento]

I'm in two minds about this.

On the one hand, there's obvious value to this, particularly in the original example of having a procedure that returns multiple result sets. It's probably not so useful for multi_query, but I'm not too fussed about that.

On the other hand, this is super niche usage, I wonder if it belongs in a DB drop-in, instead. I certainly don't see it being used in Core anytime soon.