18

When data is returned from MySQL, it is automatically returned as strings, regardless of the MySQL data type.

Is there any way to tell MySQL/PHP to maintain the data types (e.g. int), so if you query an int column, you get an integer in PHP instead of a string?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
fbwb
  • 199
  • 1
  • 2
  • 5
  • 2
    It's worth noting that PHP cannot hold integers larger than `PHP_INT_MAX` (2,147,483,647 in my computer). That can safely store a signed INT but not an unsigned INT or anything larger. – Álvaro González Feb 16 '11 at 09:25

9 Answers9

15

Well you can do Type casting to convert the type of returned data using PHP.

You can take a look at int, intval to convert to integers. You may also use settype:

settype($foo, "array");
settype($foo, "bool");
settype($foo, "boolean");
settype($foo, "float");
settype($foo, "int");
settype($foo, "integer");
settype($foo, "null");
settype($foo, "object");
settype($foo, "string");

Another way would be:

$foo = (array)$foo;
$foo = (b)$foo;      // from PHP 5.2.1
$foo = (binary)$foo; // from PHP 5.2.1
$foo = (bool)$foo;
$foo = (boolean)$foo;
$foo = (double)$foo;
$foo = (float)$foo;
$foo = (int)$foo;
$foo = (integer)$foo;
$foo = (object)$foo;
$foo = (real)$foo;
$foo = (string)$foo;
Sarfraz
  • 377,238
  • 77
  • 533
  • 578
  • If your integer is bigger than `PHP_INT_MAX` it'll be capped. For example, there is no way to get the full range of MySQL's `unsigned bigint` to PHP even if you're running a 64-bit PHP. – Pacerier Jul 27 '15 at 05:58
8

In MySQLi use bind_result: it sets the correct type and handles NULL.

aaz
  • 5,136
  • 22
  • 18
3

If you are running Ubuntu:

sudo  apt-get remove php5-mysql
sudo  apt-get install php5-mysqlnd
sudo service apache2 restart
Gaurav Gupta
  • 5,380
  • 2
  • 29
  • 36
2

You could use type casting once you've pulled the data from your MySQL database

$row['field'] = (int)$row['field'];
Stoosh
  • 2,408
  • 2
  • 18
  • 24
1

As PHP isn't a strongly typed language, this is somewhat meaningless, but you could of course simply cast the field in question to an int via settype, etc. prior to usage.

Irrespective, there's no way (that I know of) to maintain this "type" information automatically.

John Parker
  • 54,048
  • 11
  • 129
  • 129
  • 1
    In the quest for a meaning, I am trying to imagine possible performance gains through typecasting. I would think having numeric values as int or float would use up less memory, on the other hand the manual typecasting process itself would consume resources and also bloat the php code a little. So, manual typecasting seems like superficial microoptimizing to me, while having MySQL return results in the correct type would seem reasonable, if possible. – Schmoove Apr 19 '12 at 15:26
0

If you want to get the values ordening by Price, you can change the Data Type to FLOAT from column (ex: price)

EX:

TABLE eletronics

price        id
----------------
55.90        1
40.33        2
10.60        3
1596.90      4
56.90        5

PHP:

$sql = $pdo->query("SELECT * FROM eletronics ORDER BY price DESC");

//result

1596.90      4
56.90        5
55.90        1
40.33        2
10.60        3

//The order is being per prices //OBS: You can change the dots with "," using the str_replace() //result: 1596,90 - 56,90 - 55,90 - 40,33 - 10,60 etc.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Renan Coelho
  • 1,360
  • 2
  • 20
  • 30
0

PHP's default library for MySQL doesn't have any options of this kind. Use type casting like (int) or intval, (float) etc

Alex
  • 106
  • 1
  • 8
0

in php, you can use ([type]) to make sure $something was integer , this help sql injection if you khow about it.

ex:

(int) $something;
quocnguyen
  • 192
  • 2
  • 10
0

I don't know in what context you asked this question. Because the data type of a php variable is dependent on what data type a column in your table has. If the column has data type "int" in mysql, you get an integer in php (not a string).

But anyways, you can use either is_numeric(), is_int() or gettype() php functions to know the data type of a returned value from Mysql. The problem with is_numeric() is that it returns true even if a variable contains a numeric string e.g. "2". But gettype() will return "string" and is_int() will always return false in the example mentioned i.e. "2".

Once you know the data type of a returned value you can bind the php variable to maintain that type by type casting like this:

// Suppose this is the returned value from mysql and
// you do not know its data type.
$foo = "2";

if (gettype($foo) == "string"){
    $foo = (string) $foo;
}

You can make several checks for what data type the variable has and then cast it accordingly.

I hope this reply would be helpful. Good luck! :)

Syed
  • 329
  • 2
  • 6
  • 18