#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2017
    Posts
    2
    Rep Power
    0

    Issues converting php script to query an MSSQL database.


    Hello,

    I am hoping someone can help me with some PHP code.

    This PHP code I obtained while developing a map, using google maps. Basically the user of my site inputs a postcode and a mile radius. The postcode is passed through google API to produce a Longitude and Latitude and the the database is searched. As you can see from the below code, the query 'creates' a temporary column called "distance".
    Now in PHP and connecting to a MySQL database this works perfectly.

    But I need to connect this to a MSSQL database and for some reason this is not working, it doesnt seem to like the creation of the 'fake' column. I get this error:

    Code:
    Warning: mssql_query() [function.mssql-query]: message: Invalid column name 'distance'. (severity 16) in /var/sites/s/studio.saltdigital.co.uk/public_html/bookmymot/search.php on line 313

    Below is the code im using, any help would be appreciated :-)

    PHP Code:
                $GetList sprintf("SELECT Id, Name, AddressLine1, AddressLine2, AddressLine3, Postcode, Fax, Telephone, Latitude, Longitude, ( 3959 * acos( cos( radians('%s') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( Latitude ) ) ) ) AS distance FROM mymotbooking.Companies HAVING distance < '%s' ORDER BY distance",
                
    $center_lat,
                
    $center_lng,
                
    $center_lat,
                
    $SearchDistance);
                
    $ResultList mssql_query($GetList$dbcapp);
                
    $num mssql_num_rows($ResultList);

            if (
    $num 0) {
              
    $i 0;
              while (
    $i $num) {
                
                
    $garID stripslashes (htmlspecialchars (mssql_result($ResultList,$i,"Id")));
                
    $garName stripslashes (htmlspecialchars (mssql_result($ResultList,$i,"Name")));
                
    $garAddress1 stripslashes (htmlspecialchars (mssql_result($ResultList,$i,"AddressLine1")));
                
    $garAddress2 stripslashes (htmlspecialchars (mssql_result($ResultList,$i,"AddressLine2")));
                
    $garTown stripslashes (htmlspecialchars (mssql_result($ResultList,$i,"AddressLine3")));
                
    $garPostcode stripslashes (htmlspecialchars (mssql_result($ResultList,$i,"Postcode")));
                
    $garEmail stripslashes (htmlspecialchars (mssql_result($ResultList,$i,"Fax")));
                
    $garPhone stripslashes (htmlspecialchars (mssql_result($ResultList,$i,"Telephone"))); 
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,678
    Rep Power
    4288
    you have this --
    Code:
    SELECT stuff
         , expression AS distance 
      FROM mymotbooking.Companies 
    HAVING distance < '%s' 
    ORDER 
        BY distance
    try it like this --
    Code:
    SELECT *
      FROM ( SELECT stuff
                  , expression AS distance 
               FROM mymotbooking.Companies 
             HAVING distance < '%s' 
           ) AS query
    ORDER 
        BY distance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2017
    Posts
    2
    Rep Power
    0
    Thank you :-) but unfortunatly that still did not work.Exactly the same error, saying "distance" column does not exisit.


    Originally Posted by r937
    you have this --
    Code:
    SELECT stuff
         , expression AS distance 
      FROM mymotbooking.Companies 
    HAVING distance < '%s' 
    ORDER 
        BY distance
    try it like this --
    Code:
    SELECT *
      FROM ( SELECT stuff
                  , expression AS distance 
               FROM mymotbooking.Companies 
             HAVING distance < '%s' 
           ) AS query
    ORDER 
        BY distance
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,678
    Rep Power
    4288
    okay, last chance --
    Code:
    SELECT *
      FROM ( SELECT stuff
                  , expression AS distance 
               FROM mymotbooking.Companies 
           ) AS query
     WHERE distance < '%s' 
    ORDER 
        BY distance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,999
    Rep Power
    4058
    SQL Server doesn't support using an alias in the HAVING clause, so you either have to repeat the giant expression or do a sub-query / where clause like r937 did above.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud

IMN logo majestic logo threadwatch logo seochat tools logo