正在将旧代码升级到更新的MySQL版本

Question

我非常喜欢这类事情，但我正在努力自学PHP和MySQL数据库。

我正在尝试构建我自己的本地副本，复制这里讨论的课程：https://www.dougv.com/2009/03/27/getting-all-zip-codes-in-a-given-radius-from-a-known-point-zip-code-via-php-and-mysql/

我可以让它几乎正常工作，但我认为它不能正常工作的原因是因为它是在2009年用过时的MySQL版本编写的。

有人能告诉我更新这个代码的正确方向吗？

<?php
if(isset($_POST['submit'])) {
 if(!preg_match('/^[0-9]{5}$/', $_POST['zipcode'])) {
      echo "<strong>You did not enter a properly formatted ZIP Code.</strong> Please  try again.n";
 }
 elseif(!preg_match('/^[0-9]{1,3}$/', $_POST['distance'])) {
      echo "<strong>You did not enter a properly formatted distance.</strong> Please try again.n";
 }
 else {
      //connect to db server; select database
      $link = mysql_connect('host_name', 'user_name', 'password') or die('Cannot connect to database server');
      mysql_select_db('database_name') or die('Cannot select database');
      //query for coordinates of provided ZIP Code
      if(!$rs = mysql_query("SELECT * FROM php_zip_code_distance WHERE zip_code = '$_POST[zipcode]'")) {
           echo "<strong>There was a database error attempting to retrieve your ZIP Code.</strong> Please try again.n";
      }
      else {
           if(mysql_num_rows($rs) == 0) {
                echo "<strong>No database match for provided ZIP Code.</strong> Please enter a new ZIP Code.n";
           }
           else {
                //if found, set variables
                $row = mysql_fetch_array($rs);
                $lat1 = $row['latitude'];
                $lon1 = $row['longitude'];
                $d = $_POST['distance'];
                //earth's radius in miles
                $r = 3959;
                //compute max and min latitudes / longitudes for search square
                $latN = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(0))));
                $latS = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(180))));
                $lonE = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(90)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));
                $lonW = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(270)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));
                //display information about starting point
                //provide max and min latitudes / longitudes
                echo "<table class=""bordered"" cellspacing=""0"">n";
                echo "<tbody><tr><th>City</th><th>State</th><th>Lat</th><th>Lon</th><th>Max Lat (N)</th><th>Min Lat (S)</th><th>Max Lon (E)</th><th>Min Lon (W)</th></tr>n";
                echo "<tr><td>$row[city]</td><td>$row[state]</td><td>$lat1</td><td>$lon1</td><td>$latN</td><td>$latS</td><td>$lonE</td><td>$lonW</td></tr>n";
                echo "</tbody></table>nn";
                //find all coordinates within the search square's area
                //exclude the starting point and any empty city values
                $query = "SELECT * FROM php_zip_code_distance WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY state, city, latitude, longitude";
                if(!$rs = mysql_query($query)) {
                     echo "<strong>There was an error selecting nearby ZIP Codes from the database.</strong>n";
                }
                elseif(mysql_num_rows($rs) == 0) {
                     echo "<strong>No nearby ZIP Codes located within the distance specified.</strong> Please try a different distance.n";
                }
                else {
                     //output all matches to screen
                     echo "<table class=""bordered"" cellspacing=""0"">n";
                     echo "<tbody><tr><th>City</th><th>State</th><th>ZIP Code</th><th>Latitude</th><th>Longitude</th><th>Miles, Point A To B</th></tr>n";
                     while($row = mysql_fetch_array($rs)) {
                          echo "<tr><td>$row[city]</td><td>$row[state]</td><td>$row[zip_code]</td><td>$row[latitude]</td><td>$row[longitude]</td><td>";
                          echo acos(sin(deg2rad($lat1)) * sin(deg2rad($row['latitude'])) + cos(deg2rad($lat1)) * cos(deg2rad($row['latitude'])) * cos(deg2rad($row['longitude']) - deg2rad($lon1))) * $r;
                          echo "</td></tr>n";
                     }
                     echo "</tbody></table>nn";
                }
           }
        }
    }
}
?>

非常感谢！

编辑：我应该提到，我把"从数据库中选择附近的邮政编码时出错"的消息改为echo "<p>" . mysql_error() . " | $query</p>n";，我得到了以下错误：

您的SQL语法有错误；查看与MySQL服务器版本相对应的手册，了解在"AND经度"附近使用的正确语法！=）AND城市！=''按州、城市、纬度、经度'在第1行排序|从邮政编码距离中选择*其中（纬度<=0.144722858078 AND纬度>=-0.144722858078 AND经度<=0.14472285808 AND经度>=-0.14472288078）AND（纬度！=AND经度！=）AND城市！=''按州、城市、纬度、经度订购

Answer 1

@Fred-ii-是对的。所有的mysql_*都需要更改为mysqli_*。

除此之外，您可能会遇到此SQL语句的问题。

SELECT * 
    FROM php_zip_code_distance 
        WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW)  
            AND (latitude != $lat1 AND longitude != $lon1) 
            AND city != '' 
    ORDER BY state, city, latitude, longitude

如果$lat1或$lon1为空，则SQL将失败。尝试将其更改为：

SELECT * 
    FROM `php_zip_code_distance` 
        WHERE (`latitude` <= $latN AND `latitude` >= $latS 
                AND `longitude` <= $lonE AND `longitude` >= $lonW)  
            AND (`latitude` != '$lat1' AND `longitude` != '$lon1') 
            AND `city` != '' 
    ORDER BY `state`, `city`, `latitude`, `longitude`

（我更喜欢用``包装字段名）

这样，如果$lat1或$lon1为空，则不会出现

AND (latitude !=  AND longitude != ) 

你最终会得到

AND (`latitude` != '' AND `longitude` != '')