Problem executing MSSQL query in PHP

trmbne2000 · 06-30-2009, 02:07 PM

Hello, I'm trying to run the following query against an MSSQL database in php:

PHP Code:


			
SELECT Lat,Lng FROM zip_lookup WHERE LOWER(ZIP) = 'k1a 0b1'

zip_lookup is a table with latitude an longitude stored in numeric format, and zip codes stored in varchar(7); holding both US and canadian zip codes. It is used to calculate the distance between two zip codes for a radius search. With American ZIP codes, it works fine. However when I put in a Canadian postal code like the one above, no rows are returned when I run the query in PHP. However if I run the same query in SQL Server manager, I get:

Lat Lng
45.4100 -75.7000

Here is the code I'm using:

PHP Code:


			
function zipCoord($zip) {

    $zip = trim(strtolower($zip));

    $zipSQL = "SELECT Lat,Lng FROM zip_lookup WHERE LOWER(ZIP) = '{$zip}'";

    $query = mssql_query($zipSQL);

    if (!$query) { die('MSSQL error: ' . mssql_get_last_message()); }

    if ($query && mssql_num_rows($query) > 0) {

        $row = mssql_fetch_array($query);

        $result[] = $row[0];

        $result[] = $row[1];

        return $result;

    } else {

        return false;

    }

}

...

$zipCoords = zipCoord('K1A 0B1');

I have used echo $zipSQL to ensure that the correct SQL is executing, not errors are given and $query evaluates to true, which (according to manual) means that the query was successful.
Does anyone have any ideas what could be going on here?

Thanks,
Andrew

codefreek · 06-30-2009, 02:44 PM

var_dump for more info..

trmbne2000 · 06-30-2009, 03:52 PM

OK, I actually ended up fixing up the problem by experimenting. This SQL solved it:

SELECT Lat,Lng FROM zip_lookup WHERE LOWER(ZIP) = 'k1a 0b1' AND ID > 0

Anyone know of any reasons why this would happen?

Thanks,
Andrew