How do I make sure that values from MySQL keep their type in PHP?

Question

I am having a problem in my PHP script where values called from MySQL are being returned as strings, despite being marked in the database as int and tinyint.

This is a problem because when converting an array based on MySQL date into JSON data, values that should be integers are placed in double quotes, which is causing trouble in both Javascript and iPhone apps that use that JSON data. I am getting JSON values that look like "key" : "1", when what I want is "key" : 1.

After doing some research, it seems that it should be possible to get the values as their native type so long as one has PHP 5.3, and the mysqlnd module installed. I have 5.3.3 and phpinfo() seems to indicate I have the mysqlnd module installed and running:

mysqlnd enabled
Version mysqlnd 5.0.10 - 20111026

However, my values are still being returned as strings.

I have looked at the PHP manual entry for mysqlnd, and it's always possible I'm missing the obvious, but I don't see anything that indicates I need to do anything specific in my code to get the native values.

What exactly do I do to get my MySQL functions in PHP to give me the MySQL results in their native type?

---

In order to fascillitate an answer below, this is the command I use to connect to the database:

private function databaseConnect()
{
    $this->mysqli = new mysqli(Database::$DB_SERVER, Database::$DB_USERNAME, Database::$DB_PASSWORD);
    $this->mysqli->set_charset("utf8");
    return true;
}

private function dbConnect()
{
    Database::$USE_MYSQLI = extension_loaded('mysqli');
    if (!$this->databaseConnect())
    {
        echo "Cannot Connect To The Database Server";
        throw new Exception();
    }
    if (!$this->databaseSelectDB())
    {
        echo "The database server connected, but the system could not find the right database";
        throw new Exception();
    }
}

private function databaseQuery($query)
{
    return $this->mysqli->query($query);
}

public function doQuery($query)
{
    $result = $this->databaseQuery($query);
    if ($result == FALSE)
    {
        //ErrorHandler::backtrace();
        die("This query did not work: $query");
    }
    return $result;
}

private function getRows($table, $matches, $orderBy = array(), $limit = array())
{
    $calcFoundRows = '';
    if (count($limit) > 0)
    {
        $calcFoundRows = ' SQL_CALC_FOUND_ROWS';
    }
    $query = 'SELECT ' . $calcFoundRows . ' * FROM ' . $table;
    if (count($matches) > 0)
    {
        $query .= ' WHERE ';
        $keys = array_keys($matches);
        $first = true;
        foreach ($keys as $key)
        {
            if (!$first)
            {
                $query .= ' AND ';
            }
            $first = false;

            // now he is safe to add to the query
            // the only time this is an array is when this is called by getSelectedUsers or getSelectedArticles
            // in that case it is an array of array's as the key (which is the column name) may have more than
            // one condition
            if (is_array($matches[$key]))
            {
                $firstForColumn = true;
                foreach ($matches[$key] as $conditions)
                {
                    if (!$firstForColumn)
                    {
                        $query .= ' AND ';
                    }
                    $firstForColumn = false;

                    // if the value is an array we generate an OR selection
                    if (is_array($conditions[1]))
                    {
                        $firstOr = true;
                        $query .= '(';

                        foreach ($conditions[1] as $value)
                        {
                            if (!$firstOr)
                            {
                                $query .= ' OR ';
                            }
                            $firstOr = false;
                            // clean this guy before putting him into the query
                            $this->cleanMySQLData($value);
                            if ($conditions[0] == Selection::$CONTAINS)
                            {
                                //$query .= 'MATCH (' . $key . ') AGAINST (' . $value . ') ';
                                $value = trim($value, "'");
                                $value = "'%" . $value . "%'";
                                $query .= $key . ' LIKE ' . $value;
                            }
                            else
                            {
                                $query .= $key . ' ' . $conditions[0] . ' ' . $value;
                            }
                        }

                        $query .= ')';
                    }
                    else
                    {
                        // clean this guy before putting him into the query
                        $var = $conditions[1];
                        $this->cleanMySQLData($var);
                        if ($conditions[0] == Selection::$CONTAINS)
                        {
                            //$query .= 'MATCH (' . $key . ') AGAINST (' . $var . ') ';
                            $var = trim($var, "'");
                            $var = "'%" . $var . "%'";
                            $query .= $key . ' LIKE ' . $var;
                        }
                        else
                        {
                            $query .= $key . ' ' . $conditions[0] . ' ' . $var;
                        }
                    }
                }
            }
            else
            {
                // clean this guy before putting him into the query
                $this->cleanMySQLData($matches[$key]);
                $query .= $key . " = " . $matches[$key];
            }
        }
    }
    if (count($orderBy) > 0)
    {
        $query .= " ORDER BY ";
        $first = true;
        foreach ($orderBy as $orderCol)
        {
            if (!$first)
            {
                $query .= ',';
            }
            $query .= $orderCol;
            $first = false;
        }
    }

    if (count($limit) > 0)
    {
        $query .= ' LIMIT ' . $limit[0];
        if (count($limit) > 1)
        {
            $query .= ',' . $limit[1];
        }
    }


    $result = $this->doQuery($query);
    $data = array();
    while ($row = $this->databaseFetchAssoc($result))
    {
        $data[] = $row;
    }
    if (strlen($calcFoundRows) > 0)
    {
        $numRows = $this->databaseCountFoundRows();
        $key = '^^' . $table . '_selectionCount';
        Session::getSession()->putUserSubstitution($key, $numRows);
    }

    return $data;
}

Heterogeneous

Curious, why you need that for php? – Sweetheart

@zerkms: context. – Andrea

What is your problem? Where is the code demonstrating your problem? What is your exact PHP version? – Heck

@zerkms: The problem occurs when I use json_encode(). I have updated my question accordingly. – Heterogeneous

It would be much better if you also create a short example code that demonstrates your issue. Sure you can not offer your database here, but it would make visible at which point you run into that problem. Not that you try to fix it at some place where the problem does not occur. There is something called the X/Y problem and we sometimes fall into that. – Heck

@hakre: Thank you for commenting. Unless you are saying that I should be getting value types by default with my current configuration, it should be possible to definitively answer whether or not one can get typed values from MySQL in PHP and if so how. I understand the X/Y problem, but I believe I am asking a fair question that can be answered in general without relying on too many specifics. – Heterogeneous

@DaveMG: I don't say your motivation is wrong, but see my answer. It is really simple to do what you want to do. The problem with these simple answers is, even if they correctly answer your question, it's likely not the problem you have. And unless you don't outline what exactly you do, we can not outline where exactly you made a mistake. – Heck

@hakre: Fair enough. I've included more code in hopes of making the issue clearer. – Heterogeneous

let us continue this discussion in chat – Heck

Does this answer your question? MySQL integer field is returned as string in PHP – Misogamy

Answer 1

What exactly do I do to get my MySQL functions in PHP to give me the MySQL results in their native type?

You connect to the database, then you prepare your query, execute it, bind the result and then you fetch it.

Let's do these steps line-by-line:

$conn = new Mysqli('localhost', 'testuser', 'test', 'test');
$stmt = $conn->prepare("SELECT id FROM config LIMIT 1");
$stmt->execute();
$stmt->bind_result($id);
$stmt->fetch();
var_dump($id); # it's an int!

This works for me. As you wrote your code is more complex, you will need to locate the place where you query the database. Check that you're using Mysqli::prepare() and if not, introduce it.

You will also need to use Mysqli_Stmt::execute() and then Mysqli_Stmt::bind_result() otherwise the (here integer) type is not preserved for that result column.

Heck

Answer 2

However, my values are still being returned as strings.

You are in PHP, where it does not matter that your data are int, bool, strings... if they have such a type they are called scalar data and dynamic castings will allow you to make them behave as you want. For example, the string "12345"+"54321" will give you 66666. If you absolutely want your data to be of a particular type, as in every language, it is not the driver's job. In Java you've got something like .getString, .getInt methods in JDBC's interfaces, in PHP you do not have as it is not very useful. You will have to cast yourself your data with intval boolval strval... functions or (int), (bool)... casting operators.

As your post said you can have it by using server-side prepared statement:

Advantages of using mysqlnd for PDO

mysqlnd returns native data types when using Server-side Prepared Statements, for example an INT column is returned as an integer variable not as a string. That means fewer data conversions internally.

With PDO

You have to put this line after your connection

$PDO->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

then, when you want to query :

$s = $PDO->prepare('yourquery');
//here, binding params
$s->bindValue('paramName','paramValue');
$s->execute();

With Mysqli

As you use mysqli, the syntax will be a little different : Note: there is no way of client-side prepared statement so you won't need the configuration line that I put with PDO.

So your query will look like that:

$statement = $MySQli->prepare('your query');
$statement->bind_param('si', $stringParam, $intParam);
$statement->bind_result($var1, $var2 /*,...*/);
$statement->execute();
while($statement->fetch()){
  //process here, result will be in var1, var2...
}

You can see that, here, there is no built-in fetchAll method. To bind your data you need to use variables as it is not passed as value like in PDOStatement::bindValue() but by reference. Moreover the types are defined in the first arg (s for string, i for integer...) There are no named parameters only indexed ones. The fetch method works in a different way and needs you to call bind_result BEFORE the execute statement;

Rawboned

Thank you for answering. My database connection commands are spread out over a bunch of functions in an object oriented fashion. I use mysqli to connect to the database, though, so would I just add your code after that? I have added my connection command to my question for reference. – Heterogeneous

@DaveMG: The connection to your database looks fine. Are you using prepared statements and do you bind variables to fetch the result? That's far more important. – Heck

@hakre: Thank you for commenting. I have a function that I can pass an array to and assembles the MySQL query based on the values. It then passes the resulting query onto another function that makes the database connection and passes the query to the MySQL server. The code for doing all this is kind of expansive, though, which is why I didn't include it in the question. It seems like a lot to ask people to look through. At this point I am unsure where to implement artragis's comands. I tried adding his $PDO line after my mysqli line, but it made my code fail. – Heterogeneous

PDO is different to Mysqli. If you use Mysli, stick with Mysqli. I add you some example code to my answer that shows all of the steps you need to do. – Heck

@Heck and artragis: Thank you for your continued help. I feel this is on the right track, but it is also going over my head. I have endeavoured to try and include the code I think is relevant in my question. I am sorry for the imposition of so much code, but like I said, it's expansive. I hope I have edited down to something useful. – Heterogeneous

@DaveMG: Well you've created that mess in code, now maintain it ;) Well joke aside we've outlined how it works and now you see your real problem: The design of your database access layer. It stands in your own way to get the job done. Why not rewrite it? Following SOLID principles btw. will help you to prevent similar problems in the future: Don't be STUPID: GRASP SOLID! – Heck

Answer 3

This is possible with mysqlnd, but only after you enable it. To enable it you need to call the method options and set the option to true:

$this->mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true); 

Englut

Answer 4

It is enabled by default for prepared statements. If you use prepared statements all the time then you already have the data returned in the correct type.

$stmt = $mysqli->prepare('SELECT 1');
$stmt->execute();
$ret = $stmt->get_result();
echo gettype($ret->fetch_row()[0]);

The above code will return:

integer

However, if you used normal unprepared queries then all values are returned as strings even with mysqlnd. But you can enable the same setting to apply to unprepared queries, too.

$ret = $mysqli->query('SELECT 1');
echo gettype($ret->fetch_row()[0]);
// outputs:
// string

// Enable the same setting for unprepared queries
mysqli_options($mysqli, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);

$ret = $mysqli->query('SELECT 1');
echo gettype($ret->fetch_row()[0]);
// outputs:
// integer

The reason for this is the technical implementation of mysqlnd. The same applies to PDO_MYSQL as well, but in PDO this setting is enabled by default when you have emulated prepared statements switched off. There is a very good explanation by Ulf Wendel in one of the posts you linked.

This is the new default with PDO_MYSQL when compiled against mysqlnd, turning off the PDO prepared statement emulation and disabling PDO::ATTR_STRINGIFY_FETCHES. MySQL Prepared Statements use different binary communication protocol but non-prepared statements. The binary protocol avoids unecessary type casts to strings.

Julien Pauli explains the difference between textual and binary MySQL protocols in this presentation: https://www.slideshare.net/jpauli/mysqlnd

Amalburga

Answer 5

This probably is not the best answer but you can use something such as intval() to reinforce the data type. I've personally had this experience when using PDO, Mysqli, or even the old (now deprecated) mysql functions. You can run test cases with is_int() and is_string() to be 100% certain that the fetched data from the database really is a string versus and int. Sorry I couldn't be more help!

Die

I only meant for is_int for testing purposes but I see your point. I forgot about ctype_digit, I usually use is_numeric but thank you for that! – Die

Thank you for answering. I could also use settype(), but as my code stands, I would have to implement that in so many places it gets ridiculous. So I'm looking for a more global solution. – Heterogeneous