How to share connection_id between 2 queries?

Question

I need to execute two SQL statements together because connection_id() in the first statement will be used in the Mysql view wp_statistics_benchmarks.

Without the connection_id(), the wp_statistics_benchmarks is an empty view. The following SQL works fine and get results:

replace into wp_params (`view_name` , `param1_val`, `connection_id`) 
                values ('benchmarks', 484         , connection_id())
;

select * from wp_statistic_benchmarks;

But, to work with wordpress, the following code doesn't work:

$mysqli  = new mysqli(.....);

$results = $this->_wpdb->query("
     replace into wp_params (`view_name`, `param1_val`, `connection_id`) 
                values ('benchmarks', $connected_from, $mysqli->thread_id);

     select * FROM `wp_statistic_benchmarks`;"
);

How can I convert these two mysql codes into Wordpress wpdb queries?

Answer 1

If I understand your requirements (and I do not know wordpress), you are inserting a row to wp_params with a column called connection_id. I would assume that this value will be unique on the table. I would be tempted to add an integer autoincrement id field to the table and then get the value of that (last insert id). Then use this id in a WHERE clause when selecting from the view.

Answer 2

The ';' query delimiter is purely an SQL shell convenience and is not a part of the MySQL dialect so you're correct that your code doesn't work.

Here's the actual replacement code:

$mysqli = new mysqli(.....);
$this->_wpdb->query(
     "replace into wp_params
    (`view_name`, `param1_val`, `connection_id`) 
    values ('benchmarks', $connected_from, $mysqli->thread_id)");
$results = $this->_wpdb->query("select * FROM `wp_statistic_benchmarks`");

This is the same as Ярослав's answer above.

Update:

If your code is still not working you might have to enable persistent connections in Wordpress.

Update 2:

There was a missing space between in the second query's select statement and the * shorthand all columns selector. Interestingly this may or may not cause an issue for you, it doesn't seem to bother my MySQL 5.5 command line shell.

Answer 3

Use the wpdb object twice.

$this->_wpdb->query('replace into ...'); 
$rows = $this->_wpdb->get_results('select ...')

---

Let me put it another way, select * from wp_stat ... and replace into wp_params ... from your original "mysql codes" are separate statements without any relation to each other.

You think that you need to run them in sequence, whereas in fact you can have a cup of coffee or even travel around the earth in between those replace into and select statements and they would still do the same thing. If that is not the case, then your question lacks information necessary to provide a good answer because wp_params is not a standard table in wordpress and neither is the view. I don't think you understand your problem.

Besides, running them as I suggest is equivalent with your "mysql codes". Moreover, $wpdb->query returns the number of affected rows or false, so you will never be able to run a select statement with $wpdb->query() to retrieve a set of tuples.

Answer 4

How can I convert these two mysql codes into Wordpress wpdb queries?

You can't. That's because you're using wpdb and it only supports one query per ->query() call. However, if you're using Mysqli with wpdb, you can use the multi_query() method of it with wpdb. Here is how:

To use multiple queries, you need to ensure that wpdb uses Mysqli (e.g. define the USE_EXT_MYSQL constant as FALSE in your Wordpress config).

Then you can obtain the mysqli instance from the wpdb object, either with reflection or a helper class/module:

abstract class wpdb_dbh extends wpdb
{
    static function from(wpdb $wpdb) {
        return $wpdb->dbh;
    }
}

Mysqli is then available without creating a new instance:

$mysqli = wpdb_dbh::from($this->_wpdb);

As this is a valid Mysqli instance you can run multi query.

But just obtaining the same Mysqli instance as wpdb uses it probably the most important thing here as otherwise your open an additional connection with new mysqli(...) which you need to prevent.

Additionally take care that $mysqli->thread_id is a fitting replacement to connection_id() following the same formatting/encoding. You should be able to use connection_id() directly anyway, so I actually see not much reason to access the thread_id member, but it's perhaps only because you tried some alternatives and I'm just over-cautious.