TalkPHP - View Single Post

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

06-30-2009, 02:07 PM	#1 (permalink)
trmbne2000 The Wanderer Join Date: Nov 2007 Posts: 13 Thanks: 0	Problem executing MSSQL query in PHP 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