troubleshooting Question

Zip Code Search for PHP/MSSQL

Avatar of cbeaudry1
cbeaudry1 asked on
PHP
39 Comments1 Solution884 ViewsLast Modified:
I'm running Apache/PHP on Windows with a separate MSSQL Server. Everything is the latest version. I'm trying to code the PHP page that will do a reseller search but am having trouble connecting to the SQL Server.

The PHP code:

//Simulate data entry
      $zipCode = "19107";
      $radius = "25";

      //Connect to database
      $db = new COM("ADODB.Connection");
      $dsn = "DRIVER={SQL Server}; SERVER=server\dev;UID=uname;PWD=pass; DATABASE=database";
      $db->Open($dsn);
      
      function inRadius($zipCode,$radius)
      
       {
      global $db;
      global $zipCode;
      global $radius;
      
             $query=$db->Execute("SELECT * FROM PostalCodes WHERE PostalCode='$zipCode'");
             $result = mssql_query($query, $db->Open($dsn));

             if(mssql_num_rows($result) > 0) {
             $row = mssql_fetch_array($result, MSSQL_ASSOC);
             $lat=$row["Latitude"];
             $lon=$row["Longitude"];
             $query=$db->Execute("SELECT * FROM PostalCodes WHERE (POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius) ");
             $result = mssql_query($query);
             if(mssql_num_rows($result) > 0) {
             while($row = mssql_fetch_array($result, MSSQL_ASSOC)) {
             $zipArray[]=$row;
             }
             return $zipArray;
             }
             } else {
             return "Zip Code not found";
             }
       } // end func

 //$zipCode = $HTTP_POST_VARS["zipCode"];
 //$radius = $HTTP_POST_VARS["radius"];

 $zipArray = inRadius($zipCode,$radius);

 print "<h2>There are ".count($zipArray)." Zip codes within $radius Miles of $zipCode</h2>";
 foreach($zipArray as $row) {
 print "<br>ZipCode:$row[PostalCode] Lon:$row[Longitute] Lat:$row[Latitude] City: $row[CityName]";
 }

The result:

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Provider<br/><b>Description:</b> Type mismatch.' in D:\Inetpub\website\TEST2.php:27 Stack trace: #0 D:\Inetpub\website\TEST2.php(27): com->Open(NULL) #1 D:\Inetpub\website\TEST2.php(49): inRadius('63011', '25') #2 {main} thrown in D:\Inetpub\website\TEST2.php on line 27

The SQL type for the PostalCode field is nvarchar.

 Where am I going wrong?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 39 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 39 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros