Numerics converted to a string with PHP and PDO to MYSQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
onecloudprop
Forum Newbie
Posts: 4
Joined: Tue Feb 09, 2016 4:43 am

Numerics converted to a string with PHP and PDO to MYSQL

Post by onecloudprop »

Hi

Iam using PHP (version 5.6) to act as the middle tier between a MySQL database (5.5.40) and returning the results in JSON format.

We are using the PHP extension (PDO) to do the database interaction, but have noticed that all numeric values get returned as a string. The database itself is running on a Linux installation, but during development, both the client and middle tier are running on Windows.

I set the connection parameter "ATTR_STRINGIFY_FETCHES" to false but that made no difference. e.g. $dbConnection->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

This is the PHP function that I am using to retrieve data and it is tied to the RESTful interface.

Code: Select all

function getAllObjects() 
{
    $sql = "SELECT * FROM Tbl_Objects ORDER BY 1";
    try {
	$db = getDB();
	$stmt = $db->query($sql);  
	$objects = $stmt->fetchAll(PDO::FETCH_OBJ);
	$db = null;
	echo json_encode($objects);
    } catch(PDOException $e) {
    echo '{"error":{"text":'. $e->getMessage() .'}}'; 
    }
}
I also tried "mysqli" but I get the same results in that the numeric values are all converted to Strings. If I use the "cast_query_results($rs)" function on the net, I do get numeric converted correctly, but it seems to me that this is rather a fudge, rather than a solution.

Any ideas on how to get this to work natively please?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Numerics converted to a string with PHP and PDO to MYSQL

Post by Celauran »

Have you tried turning off ATTR_EMULATE_PREPARES?
onecloudprop
Forum Newbie
Posts: 4
Joined: Tue Feb 09, 2016 4:43 am

Re: Numerics converted to a string with PHP and PDO to MYSQL

Post by onecloudprop »

Nope that's a new one on me and seems to work. Any downside in terms of performance?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Numerics converted to a string with PHP and PDO to MYSQL

Post by Celauran »

I haven't actually measured it, but it's certainly going to be faster to have the compiled C code do the conversion (if there even is one to be done) than to do it in userland.
onecloudprop
Forum Newbie
Posts: 4
Joined: Tue Feb 09, 2016 4:43 am

Re: Numerics converted to a string with PHP and PDO to MYSQL

Post by onecloudprop »

Thank-you for turning that one up. Very much appreciated.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Numerics converted to a string with PHP and PDO to MYSQL

Post by Weirdan »

onecloudprop wrote:Nope that's a new one on me and seems to work. Any downside in terms of performance?
There's additional roundtrip to server when emulate_prepares is off. Basically it sends the query (without parameters) to server first, obtains statement parsing result (either success or fail) and if prepare was successful client library proceeds with sending statement arguments. Whereas when emulate_prepares is on, arguments are interpolated into the query string on the client side and then entire query is sent to db server.

Either flag value shouldn't be a problem unless you have very high traffic or high ping times from webserver to db server.
onecloudprop
Forum Newbie
Posts: 4
Joined: Tue Feb 09, 2016 4:43 am

Re: Numerics converted to a string with PHP and PDO to MYSQL

Post by onecloudprop »

Ok, well if it resolves the issue for now that would be good. Many thanks.
Post Reply