Comparing large numbers in php and sql

Question

I need to compare a very large number in php (30 digits long) with 2 numbers in my database. Whats a good way to do this? I tried using floats but its not precise enough and I don't know of a good way to use large numbers in php.

Answer 1

You could compare strings instead.

Depending on how you're fetching the data from the database, you may want to explicitly cast the integer to a string type in the SQL statement.

Other than that, there are several libraries in PHP that handle large integers, like BCMath and GMP.

Answer 2

Have you tried using string comparison? Just make sure every number is padded with zeroes.

mysql> select "123123123123123123456456456"<"123123123123123123456456457";
+-------------------------------------------------------------+
| "123123123123123123456456456"<"123123123123123123456456457" |
+-------------------------------------------------------------+
|                                                           1 | 
+-------------------------------------------------------------+

Justed test this up to 200+ chars, works like a charm.

Answer 3

Check bcdcomp function

Answer 4

Handling large numbers in PHP is done through either of two libraries: GMP or BC Math.

I haven't done this myself, so it may not be correct, but I think you'd have to take the string result from GMP or BC Math, and feed that into the query. Make sure you store your numbers as bigint.

Interestin fact: You might think BigInt would be limited to about 20 digits, and you'd be right, except for the fact that it has Mysql Magic:

You can always store an exact integer value in a BIGINT column by storing it using a string. In this case, MySQL performs a string-to-number conversion that involves no intermediate double-precision representation.

Answer 5

If they're -very- big, I'd compare them as strings even. First, if one is longer than the other, it wins. If they're the same length, compare digit by digit left-to-right - if two digits differ, the number with the bigger digit wins. This of course for Positive integers.