WordPress.org

Make WordPress Core

Opened 4 years ago

Last modified 2 years ago

#29938 new enhancement

mysqli_query and multiple resultsets

Reported by: soulseekah Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 3.9
Component: Database Keywords: needs-patch needs-unit-tests
Focuses: Cc:

Description (last modified by SergeyBiryukov)

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.

Change History (4)

#1 @SergeyBiryukov
4 years ago

  • Description modified (diff)

#2 @chriscct7
2 years ago

  • Keywords needs-patch needs-pento added

#3 @chriscct7
2 years ago

  • Keywords needs-unit-tests added

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

#4 @pento
2 years ago

  • Keywords needs-pento removed

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.

Note: See TracTickets for help on using tickets.