0


I'm having troubles with the mysqli driver used by wordpress, where it is converting each and every integer value to a string within $wpdb->get_results("SELECT...").
I know that it is possible so use the the option MYSQLI_OPT_INT_AND_FLOAT_NATIVE with the mysqli driver ($mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);) but I don't seem to find a way to set this option in wordpress.
How do I do that?

Thank you

EDIT

Based on the comments below, I'b better further explain what I'm trying to achieve.
I'm developing a plugin to add reactive/responsive functionalities to wordpress both in the backend and in the frontend (exactly like Gutenberg). In my plugin I need to access a variety of structures in the database, and I'm doing that using using AJAX along with VueJS which makes extensive use of observables.
Let's say, for example that I have a structure like this in the database:

CREATE TABLE IF NOT EXISTS `a_table` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(190) NOT NULL DEFAULT 'New name',
    `anInteger` INT NOT NULL DEFAULT '123',
    `aBoolean` BOOLEAN NOT NULL DEFAULT TRUE
    PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`));"

and that I'm accessing the table via AJAX to bind the data to something like this in Vue:

<template>
  <el-form
    :model="aTable"
    ref="tableForm">
    <el-row>
      <el-col :span="4">
        <el-form-item
          label="Name"
          prop="name">
          <el-input
            v-model="aTable.name">
          </el-input>
        </el-form-item>
      </el-col>
      <el-col :span="2">
        <el-form-item label="BoolVal" prop="aBoolean">
          <el-switch v-model="aTable.aBoolean"></el-switch>
        </el-form-item>
      </el-col>
      <el-col :span="8">
        <el-form-item label="NumberVal" prop="anInteger">
          <el-input-number v-model="aTable.anInteger"></el-input-number>
        </el-form-item>
      </el-col>
    </el-row>
  </el-form>
</template>

<script>
export default {
  props: {
    aTable: {
      type: Object,
      default: function() {
        return {};
      }
    }
  },
  data() {
    return {
    };
  },
  methods: {
  }
};
</script>

//AJAX and other glue code omitted

With this set up, the input filed "name" is correctly bound and displays the string "new name", while the boolean switch shows always false (VueJS is expecting a boolean or, at least, an integer, and is getting a string instead!). This forces me to manipulate the results I get from $wpdb before returning the dataset to the AJAX caller if I want VueJS understand correctly what I retrieve from the database, like this:

static function ajax_get_table()
{
    global $wpdb;
    $response = $wpdb->get_results("SELECT * FROM `a_table` WHERE 1=1 ORDER BY `id`");

    // MYSQLI_OPT_INT_AND_FLOAT_NATIVE Hack
    // mysql is returning each and every integer/boolean as a string
    // This hack is needed to retrieve correct data type
    array_walk($response, function(&$item, $key) {
        array_walk($item, function(&$subitem, $subkey) {
            // Integers
            if (in_array($subkey, ['id', 'anInteger'])) $subitem = intval($subitem);
            // Boolenas
            if (in_array($subkey, ['aBoolean'])) $subitem = boolval($subitem);
        });
    });

    // AJAX termination
    wp_send_json($response);
    die();
}

But this means that I have to know in advance each end every data type returned from the MYSQL query, which is by far an anti-pattern programming technique.

That's why I'm wondering why is Wordpress stuck to this pre-PHP-5.3 limitation.

  • This is the standard behaviour, so before you do anything you should consider that WordPress and its plugins are coded with the assumption that this option isn't enabled, so changing it could have unintended side-effects. – Jacob Peattie Nov 23 '18 at 10:39
  • @JacobPeattie. Ok, standard behavior, but this is breaking blatantly the functionality of the reactive framework I'm trying to integrate in Wordpress (much like the new Gutenberg editor). Could you please explain why and when deleting type information from a query result should be desirable? – Raffaele Candeliere Nov 23 '18 at 16:38
  • This is just how MySQL worked in PHP prior to 5.3, so it's probably a backwards compatibility thing. This would be why it's an option you have to explicitly set and not the default behaviour. Don't see what this has to do with WordPress or Gutenberg. – Jacob Peattie Nov 24 '18 at 3:03
  • @JacobPeattie See my edit – Raffaele Candeliere Nov 25 '18 at 10:00
0

That's why I'm wondering why is Wordpress stuck to this pre-PHP-5.3 limitation.

Because WordPress has made the decision to support PHP 5.2 due to the large number of hosts that continue to use it. This is an ongoing debate and there's no point rehashing it all here.

But this means that I have to know in advance each end every data type returned from the MYSQL query, which is by far an anti-pattern programming technique.

What? MySQL in PHP, by default, only returns strings. So there you go. That's "each and every data type" that you need to know.

Regardless, you should know what your query is likely to return before outputting it to a page. And you should be verifying and escaping it before outputting it in the browser or an API response.

Most things like this have some sort of model that represents the data that sits between the front-end and the database. What is an anti-pattern is having your front-end so tightly coupled to the minutiae of how the data is stored.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.