MySQL SELECT Statement not working when executed from PHP

Question

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.

Single / double quote difference? html-tags tag you can't see in the browsers output fouling up the query? — Wrikken
– Wrikken, Commented Jun 17, 2010 at 19:12
All, I solved my problem by re-writing my query a different way, which PHP and MySQL seem to like. I'm giving Jordan the accepted answer as he was the most active and tried to help. Thanks, all! — Andrew K
– Andrew K, Commented Jun 17, 2010 at 19:56
Glad you were able to get it. Can you post what you had to do, for the sake of history and the children? — Jordan
– Jordan, Commented Jun 17, 2010 at 19:59
Mistake #2: Using the mysql_ functions. Mistake #3, freaking huge numbers in PHP as anything but a string... — Charles
– Charles, Commented Jun 17, 2010 at 20:02

Jordan · Accepted Answer · 2010-06-17 19:04:14Z

0

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.

answered Jun 17, 2010 at 19:04

Jordan

32.7k6 gold badges59 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Andrew K Over a year ago

Hi Jordan, Thanks for your response. I've tried that, and I get no errors whatsoever. However, when I do this: $netnestrow = mysql_fetch_array($netnestresults) or die(mysql_error()); I get a blank page.

Jordan Over a year ago

Are you able to select anything from the DB? Maybe could you do "Select nested as nest From ipspace6 Limit 0, 1" to see if anything comes back?

Andrew K Over a year ago

Jordan, Yes that works fine, I get a valid result when I run that query.

Jordan Over a year ago

For giggles, can you change the query to this: "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";

Andrew K Over a year ago

I tried your new query, and I still don't get any results from MySQL.

Neel Basu · Accepted Answer · 2010-06-17 19:14:23Z

0

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

answered Jun 17, 2010 at 19:14

Neel Basu

13k12 gold badges88 silver badges157 bronze badges

Comments

Charles · Accepted Answer · 2010-06-17 20:07:31Z

0

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...)

answered Jun 17, 2010 at 20:07

Charles

51.5k13 gold badges107 silver badges146 bronze badges

1 Comment

Andrew K Over a year ago

Charles, I'm storing these numbers in a Decmial(39,0) UNSIGNED column. It seems to work just fine, but I ran into issues with MySQL trying to interpret these numbers as strings. See my other question (regarding exactly this) here: stackoverflow.com/questions/2993828/…

Collectives™ on Stack Overflow

MySQL SELECT Statement not working when executed from PHP

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related