How to set MYSQLI_OPT_INT_AND_FLOAT_NATIVE option
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.
Leave an answer