MySQL SELECT Statement not working when executed from PHP

Question

Hello all,

I have the following piece of code, executing a pretty simple MySQL query:

$netnestquery = 'SELECT (`nested`+1) AS `nest` FROM `ipspace6` WHERE `id`<='.$adaddr.' AND `subnet`<='.$postmask.' AND `type`="net" AND `addr` NOT IN(SELECT `id` FROM `ipspace6` WHERE `addr`<'.$adaddr.' AND `type`="broadcast") ORDER BY `id`,`subnet` DESC LIMIT 1';

$netnestresults = mysql_query($netnestquery);
$netnestrow = mysql_fetch_array($netnestresults);
$nestlvl = $netnestrow['nest'];

echo '<br> NESTQ: '.$netnestquery;

Now, when I execute this in PHP, I get no results; an empty query. However, when I copy and paste the query echoed by my code (for debug purposes) into the mysql command line, I get a valid result:

mysql> SELECT (`nested` + 1) AS `nest` FROM `ipspace6` WHERE `id`<=50552019054038629283648959286463168512 AND `subnet`<=36 AND `type`='net' AND `addr` NOT IN (SELECT `id` FROM `ipspace6` WHERE `addr`<50552019054038629283648959286463168512 AND `type`='broadcast') ORDER BY `id`,`subnet` DESC LIMIT 1;
+------+
| nest |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

Can anybody tell me what I'm doing wrong? I can't put quotes around my variables, as then MySQL will try to evaluate the variable as a string, when it is, in fact, a very large decimal. I think I might just be making a stupid mistake somewhere, but I can't tell where.

Answer 1

Can you modify the line to say $netnestresults = mysql_query($netnestquery) or die(mysql_error());

It may be giving you an unknown error, such as a bad connection, missing DB, etc.

Answer 2

do an echo $netnestquery before calling mysql_query also add a die(mysql_error()) there.

Answer 3

WHERE `id`<=50552019054038629283648959286463168512

That's a pretty big number there.

PHP has issues with big numbers. The maximum size of an integer depends on how PHP was compiled, and if it's on a 64-bit system.

Have you checked that the variable containing that number hasn't been capped to a 32-bit or 64-bit integer? If it has been capped, you're going to need to take steps to make sure it's only being stored as a string in PHP. MySQL accepts strings that are entirely numeric as numbers without complaining.

(That being said, I'm not sure that MySQL can do anything with a number larger than 64-bits. The largest integer column is BIGINT, which is 64-bits. There's also NUMERIC, but it's treated as a floating point number, and that might not be what you want to do...)