[RESOLVED] Variables problem

nc_brox

Hi guys, I'be got a bit of a problem when I'm trying to do a select query on my table using predefined variables, it's a bit of a strange one.

The code for the statement is as follows:

$query3 = mysql_query("SELECT Pcode FROM postcodes WHERE Grid_N >= '$min_gridn' AND Grid_N <='$max_gridn'");

The problem is with the variable named $min_gridn. When this value is under 100,000 then nothing is selected. However, if I type into the query the actual value e.g. 99,999 where '$min_gridn' is currently, some records are returned. Anything where the variable is 100,000 or over returns fine....

Please help if you have any idea what is going on!!

Thanks 🙂

alimadzi

Have you echoed out the query to the screen to make sure $min_gridn is being set properly? Also you should probably use BETWEEN in your WHERE clause.

nc_brox

Hi, yes I've tried to echo out $min_gridn and it comes out correctly, also I checked everywhere for spelling mistakes etc etc and no luck there. Thanks for the tip on the BETWEEN, I've now changed my query for that...

NogDog

Silly question #1: are you positive that there are records have a Grid_N value greater than 100,000?

Silly question #2: are you sure you are not setting $min_gridn to a value greater than $max_gridn?

Assuming those are just silly questions, you might want to add a little debugging just as a sanity check that the query is being submitted to MySQL the way you think it is:

$sql3 = "SELECT Pcode FROM postcodes WHERE Grid_N >= '$min_gridn' AND Grid_N <='$max_gridn'";
$query3 = mysql_query($sql3) or die("Query failed<br>\n$sql3<br>\n" . mysql_error());
if(mysql_num_rows($query3) == 0)
{
   echo "<p class='error'>No rows returned. Query:<br>\n$sql3</p>\n";
}

nc_brox

Thanks I tried all of that and it returned the error. The funny thing is though how if I put the value of $min_gridn in my query then rows are returned, but if I put '$min_gridn' then it doesn't. Bizarre...
But it's frustrated me for so long that I've just worked out a different way of doing this query completely.... Thanks for the suggestions though both of you .

NogDog

I wonder if the quotes around the variables in the SQL were causing a string comparison instead of a numeric comparison (in which case '100000' would be considered less than '999', for instance)?

nc_brox

you had got it spot on NogDog, in the table that was holding the values Grid_N was defined as 'varchar' - I've now changed it to int and it's working perfectly!

thanks so much!