StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPHP + PDO + MySQL: how do I return integer and numeric columns from MySQL as integers and numerics in PHP?
text
Body
copied!<p><em>I've seen this question repeated a few times on Stack Overflow but none sufficiently explore the problem (or at least in a way that is helpful to me)</em></p> <p>The problem is that a DB query should return integer data types in PHP for integer columns. Instead the query returns every column as a string type.</p> <p>I've ensured that "PDO::ATTR_STRINGIFY_FETCHES" if <em>false</em> just to make sure results aren't being cast to string.</p> <p>Answers that I've seen:</p> <ul> <li>It can't be done <ul> <li>Nope, it's working on Mac OS X installed PHP/MySQL</li> </ul></li> <li>Type cast all your values in your code <ul> <li>Nope, I won't be doing that</li> </ul></li> <li>Don't worry about it, PHP is loosely typed <ul> <li>My data is output as JSON and is consumed by many other services, some require the data in the correct format</li> </ul></li> </ul> <p>From my research I understand that this is a driver implementation issue.</p> <p>Many sources claim that the MySQL native driver does not support returning numeric types. This doesn't seem true since it works on Mac OS X. Unless they mean to say that "the MySQL native driver on <strong>Linux</strong> doesn't support the feature".</p> <p>This implies that there is something special about the driver/environment I have installed on Mac OS X. I've been trying to identify the differences in order to apply a fix but I'm limited by my knowledge of how to check these things.</p> <h2>The differences:</h2> <ul> <li>PHP on OS X was compiled and installed via Home Brew</li> <li>PHP on Ubuntu was installed via "apt-get install php5-dev"</li> <li>PHP on OS X is connecting to a MySQL server also running on OS X <ul> <li>Server version: 5.1.71-log Source distribution</li> </ul></li> <li>PHP on Ubuntu is connecting to a Rackspace Cloud Database <ul> <li>Server version: 5.1.66-0+squeeze1 (Debian)</li> </ul></li> </ul> <h3>Ubuntu environment</h3> <ul> <li>Version: 10.04.1</li> <li>PHP 5.4.21-1+debphp.org~lucid+1 (cli) (built: Oct 21 2013 08:14:37)</li> <li><p>php -i</p> <p>pdo_mysql</p> <p>PDO Driver for MySQL => enabled Client API version => 5.1.72</p></li> </ul> <h3>Mac OS X environment</h3> <ul> <li>10.7.5</li> <li>PHP 5.4.16 (cli) (built: Aug 22 2013 09:05:58)</li> <li><p>php -i</p> <p>pdo_mysql</p> <p>PDO Driver for MySQL => enabled Client API version => mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $</p></li> </ul> <h3>PDO flags used</h3> <pre><code>PDO::ATTR_CASE => PDO::CASE_NATURAL, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL, PDO::ATTR_STRINGIFY_FETCHES => false, PDO::ATTR_EMULATE_PREPARES => false, </code></pre> <p>Any help and expertise would be appreciated :) I'll definitely be posting back here if I find the answer.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload