functions mysqli

[harkly]

I have converted my mysql to mysqli and am working on converting some functions.

 

My question - Can I not just convert the mysql to mysqli?

 

This is the full code:

 

class zipcode_class {

   var $last_error = "";            // last error message set by this class
   var $last_time = 0;              // last function execution time (debug info)
   var $units = _UNIT_MILES;        // miles or kilometers
   var $decimals = 2;               // decimal places for returned distance

  function get_zip_point($zip) {

     // This function pulls just the lattitude and longitude from the
     // database for a given zip code.

     $sql = "SELECT lat, lon from zip_code WHERE zip_code='$zip'";
     $r = $mysqli->query($sql);
     if (!$r) {
        $this->last_error = mysql_error();
        return false;
     } else {
        $row = mysql_fetch_array($r);
        mysql_free_result($r);
        return $row;
     }
  }

   function calculate_mileage($lat1, $lat2, $lon1, $lon2) {
     // used internally, this function actually performs that calculation to
     // determine the mileage between 2 points defined by lattitude and
     // longitude coordinates.  This calculation is based on the code found
     // at http://www.cryptnet.net/fsp/zipdy/

     // Convert lattitude/longitude (degrees) to radians for calculations
     $lat1 = deg2rad($lat1);
     $lon1 = deg2rad($lon1);
     $lat2 = deg2rad($lat2);
     $lon2 = deg2rad($lon2);

     // Find the deltas
     $delta_lat = $lat2 - $lat1;
     $delta_lon = $lon2 - $lon1;

     // Find the Great Circle distance
    $temp = pow(sin($delta_lat/2.0),2) + cos($lat1) * cos($lat2) * pow(sin($delta_lon/2.0),2);
    $distance = 3956 * 2 * atan2(sqrt($temp),sqrt(1-$temp));
      return $distance;
  }

  function get_zips_in_range($zip, $range, $sort=1, $include_base) {
      // returns an array of the zip codes within $range of $zip. Returns
     // an array with keys as zip codes and values as the distance from
     // the zipcode defined in $zip.

     $this->chronometer();                     // start the clock

     $details = $this->get_zip_point($zip);  // base zip details
     if ($details == false) return false;

     // This portion of the routine  calculates the minimum and maximum lat and
     // long within a given range.  This portion of the code was written
     // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases
     // the time it takes to execute a query.  My demo took 3.2 seconds in
     // v1.0.0 and now executes in 0.4 seconds!  Greate job Jeff!

     // Find Max - Min Lat / Long for Radius and zero point and query
     // only zips in that range.
     $lat_range = $range/69.172;
     $lon_range = abs($range/(cos($details[0]) * 69.172));
     $min_lat = number_format($details[0] - $lat_range, "4", ".", "");
     $max_lat = number_format($details[0] + $lat_range, "4", ".", "");
     $min_lon = number_format($details[1] - $lon_range, "4", ".", "");
     $max_lon = number_format($details[1] + $lon_range, "4", ".", "");

     $return = array();    // declared here for scope

     $sql = "SELECT zip_code, lat, lon FROM zip_code ";
     if (!$include_base) $sql .= "WHERE zip_code <> '$zip' AND ";
     else $sql .= "WHERE ";
     $sql .= "lat BETWEEN '$min_lat' AND '$max_lat'
              AND lon BETWEEN '$min_lon' AND '$max_lon'";

      $r = mysql_query($sql);

     if (!$r) {    // sql error
      $this->last_error = mysql_error();
      return false;
     } else {
      while ($row = mysql_fetch_row($r)) {

        // loop through all 40 some thousand zip codes and determine whether
        // or not it's within the specified range.

        $dist = $this->calculate_mileage($details[0],$row[1],$details[1],$row[2]);
        if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR;
        if ($dist <= $range) {
          $return[str_pad($row[0], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals);
        }
      }
      mysql_free_result($r);
     }

     // sort array
     switch($sort)
     {
      case _ZIPS_SORT_BY_DISTANCE_ASC:
        asort($return);
        break;

      case _ZIPS_SORT_BY_DISTANCE_DESC:
        arsort($return);
        break;

      case _ZIPS_SORT_BY_ZIP_ASC:
         ksort($return);
         break;

      case _ZIPS_SORT_BY_ZIP_DESC:
        krsort($return);
        break;
      }

      $this->last_time = $this->chronometer();

      if (empty($return)) return false;
      return $return;
   }
}

[scootstah]

My question - Can I not just convert the mysql to mysqli?

 

You can, in most cases. The procedural API for the mysqli extension is very similar to that of the mysql extension.

 

Where exactly are you having trouble?

[harkly]

Getting a

Fatal error: Call to a member function query() on a non-object

 

this is the line it is referencing

$result = $mysqli->query($sql);

 

the function it is in

   function get_zip_point($zip) {
      $sql = "SELECT lat, lon from zip_code WHERE zip_code='$zip'";
      $result = $mysqli->query($sql);

      if (!$result) {
         $this->last_error = $mysqli->error();
         return false;
      } else {
         $row = $result->fetch_array;
         $result->close();
         return $row;
      }
   }

 

the function is is being called by this other function, 3rd line down

 

function get_zips_in_range($zip, $range, $sort=1, $include_base) {
      $this->chronometer();                     // start the clock

      $details = $this->get_zip_point($zip);  // base zip details
      if ($details == false) return false;

      // This portion of the routine  calculates the minimum and maximum lat and
      // long within a given range.  This portion of the code was written
      // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases
      // the time it takes to execute a query.  My demo took 3.2 seconds in
      // v1.0.0 and now executes in 0.4 seconds!  Greate job Jeff!

      // Find Max - Min Lat / Long for Radius and zero point and query
      // only zips in that range.
      $lat_range = $range/69.172;
      $lon_range = abs($range/(cos($details[0]) * 69.172));
      $min_lat = number_format($details[0] - $lat_range, "4", ".", "");
      $max_lat = number_format($details[0] + $lat_range, "4", ".", "");
      $min_lon = number_format($details[1] - $lon_range, "4", ".", "");
      $max_lon = number_format($details[1] + $lon_range, "4", ".", "");

      $return = array();    // declared here for scope

      $sql = "SELECT zip_code, lat, lon FROM zip_code ";
      if (!$include_base) $sql .= "WHERE zip_code <> '$zip' AND ";
      else $sql .= "WHERE ";
      $sql .= "lat BETWEEN '$min_lat' AND '$max_lat'
               AND lon BETWEEN '$min_lon' AND '$max_lon'";

      $result = $mysqli->query($sql);

      if (!$result) {    // sql error

         $this->last_error = $mysqli->error();
         return false;

      } else {

         while ($row = $result->fetch_row()) {

            // loop through all 40 some thousand zip codes and determine whether
            // or not it's within the specified range.

            $dist = $this->calculate_mileage($details[0],$row[1],$details[1],$row[2]);
            if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR;
            if ($dist <= $range) {
               $return[str_pad($row[0], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals);
            }
         }
         mysql_free_result($r);
      }

      // sort array
      switch($sort)
      {
         case _ZIPS_SORT_BY_DISTANCE_ASC:
            asort($return);
            break;

         case _ZIPS_SORT_BY_DISTANCE_DESC:
            arsort($return);
            break;

         case _ZIPS_SORT_BY_ZIP_ASC:
            ksort($return);
            break;

         case _ZIPS_SORT_BY_ZIP_DESC:
            krsort($return);
            break;
      }

      $this->last_time = $this->chronometer();

      if (empty($return)) return false;
      return $return;
   }

 

[scootstah]

That's because $mysqli does not exist in that scope. You can't use variables globally in functions or classes. There is a number of ways to deal with this problem. Here is a couple of options:

 

- Make a database wrapper which uses the Singleton pattern. This allows you to call the class globally. Singletons are a bit controversial however; they are considered an anti-pattern. It makes mocking difficult/impossible and violates a few OOP principles.

 

- Use Dependency Injection to pass the mysqli object into your classes. This is probably considered the best option, but you may need to redesign parts of your application to make it work properly.

 

- Use a Service Locator pattern to manage your database object (and others). This is on the same idea as the Singleton pattern and has the same downfall.

 

- If all else fails, you can use the procedural API for the mysqli extension instead of the OOP one. This will allow you to globally call the functions anywhere in your application just like you did with the old mysql extension. If the concepts above are beyond your skill level at the time being, this may be an acceptable option. However, I encourage you to investigate the above options and try to extend your knowledge of how OOP works.

[harkly]

I would like to get into the OOP but I can't on this project.

 

I had this function that I had help converting from mysql to mysqli, is this not the using the procedural API for the mysqli extension? and would these other functions not working the same??

 

Before::

function usedEmail($email)  {
$sql_check = mysql_query("select email from user where email='".$email."'") or die(mysql_error());
if (mysql_num_rows($sql_check))
	return FALSE;
else
	return TRUE;
}

 

After::

function usedEmail($email, MySQLi $db) {
$bResult = false;
    	if ($sql = $db->prepare("SELECT email FROM user WHERE email=?"))	{
	$sql->bind_param('s', $email);
	$sql->execute();
	$sql->store_result(); 
	$bResult = $sql->num_rows > 0;
	$sql->close();
}
return $bResult;
}