converting mysql_results to mysqli?

Dboz · Apr 7, 2010

I have this app. that I got from an online zip code database site. It's purpose is to find zips in a given radius. My problem is it is written with mysql and my server does not recognize it. I have change over most of the code but the mysql_results function is a bit more confusing to someone new to programing.

bradgrafelman · Apr 7, 2010

There is no such function called [man]mysql_results/man... did you perhaps mean [man]mysql_result/man ?

If so, the manually pretty well explains what the function does... allows you to retrieve the data from one column in one row. It's commonly used if your result set contains one column in one row where it doesn't make sense to loop or retrieve the row as an array.

Dboz · Apr 7, 2010

Sorry yes it is mysql_result() . I know what it does, but the problem I am having is turning it into a mysqli function. Is there equivalent?

$zipLatitude = mysql_result($result,$i,"Latitude");
$zipLongitude = mysql_result($result,$i,"Longitude");
$zipState = mysql_result($result,$i,"State");
$zipZipCode = mysql_result($result,$i,"ZipCode");
$zipAreaCode = mysql_result($result,$i,"AreaCode");
$zipCity = mysql_result($result,$i,"City");
$zipCounty = mysql_result($result,$i,"County");
$zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "M"), 2);

bradgrafelman · Apr 7, 2010

Why not just [man]mysqli_fetch_assoc/man, e.g.:

$row = mysqli_fetch_assoc($result);

// now reference $row['Latitude'], $row['Longitude'], etc. etc.

Makes more sense to retrieve the entire row rather than one column at a time. It's more efficient, too.

halojoy · Apr 7, 2010

Like bradgrafelman told
this may not be the optimal way to pull data from this query.
When you use mysqli there are different, often better, ways
to do the same what you want to do.

For best advice and coding you should have posted everything from
SQL Statement and mysql_query and to this RESULT Fetch thing.

Anyway you can try this. It might work.
I suppose your code is within a for($i=) loop

<?php

//for($i=0;$i<$num_rows;$i++){

// set pointer to row $i in $result
mysqli_data_seek($result, $i);
// get this row into array
$row = mysqli_fetch_assoc($result);
// get values
$zipLatitude  = $row["Latitude"];
$zipLongitude = $row["Longitude"];
$zipState     = $row["State"];
$zipZipCode   = $row["ZipCode"];
$zipAreaCode  = $row["AreaCode"];
$zipCity      = $row["City"];
$zipCounty    = $row["County"];
// calc distance
$zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, 
						$zipLatitude, $zipLongitude, "M"), 2);

//}

?>

Dboz · Apr 7, 2010

Thanks
I will give that a try and update soon.

Dboz · Apr 7, 2010

I put the code in and it no longer gives me error messages, but it also gives me o results.
Here is the code in its entirety. I noticed that the program uses a while loop instead of a for loop like you recommended, but the programer said that the regular mysql version works so I have not changed that. I appreciate you looking at this. I have jumped in a little over my head, because I need the application in my website. I thought I was doing good to get the code with the database, but not if I can't make it work on my server.

<?php
//===================================================
// This script shows a sample of how to process Radius Searches in PHP.
// ** You may need to change the table name from ZIPCODES to your table name where this comment exists
// ^^ You will need to set the database connection parameters where this comment exists



//===================================================
// ^^ Set the database connection parameters here.
// Establish a MySQL Database Connection

// I have put my info in this require_once to keep my site consistant.
require_once ('connectvars.php');

if (!($conn=mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD)))  { 
   printf("Error connecting to database."); 
   exit; 
} 
// I had to switch these two arguments and I elliminated the variables in the die, so no one could see password.
$db=mysqli_select_db($conn,DB_NAME) or die("Unable to connect to database."); 





//===================================================
// Process the requested values from the form and set to variables.
$zip1 = $_POST['zip1'];
$zipMilesHigh = $_POST['zipMilesHigh'];


//===================================================
// Processes the form request.
// ** You may need to change the table name from ZIPCODES to your table name
if ($_POST['Submit'] <> ""){
	// Get base ZIP Code
	$sql = "SELECT * FROM zipcodes WHERE ZipCode = '". $zip1 ."'";
	//echo $sql;
	$result = mysqli_query($conn, $sql)or die("Unable to query local database for base data <b>". mysqli_error() ."</b><br>$sql"); 
    if (!$result){ 
        echo "database query failed."; 
    }else{
		$i = 0;
		$result_ar = mysqli_fetch_assoc($result);
//I just noticed this the $results_ar in all the satements below are not showing up as valid statements in my editer.
//I use Dreamweaver CS4 and it color codes everything.
		$zip1Latitude = $result_ar['Latitude'];
		$zip1Longitude = $result_ar['Longitude'];
		$zip1State = $result_ar['State'];
		$zip1City = $result_ar['City'];
		$zip1ZipCode = $zip1;
		//echo "<br>". $zip1City .", ". $zip1State .", Lat = ". $zip1Latitude .", Long = ". $zip1Longitude ."<br>";

   $sql = zipRadiusSQL("","", $zip1ZipCode, $zip1Latitude, $zip1Longitude, $zipMilesHigh);
   // I swithched the arguments in this line to.
   $result = mysqli_query($conn, $sql)or die("Unable to query local database for main data <b>". mysqli_error() ."</b><br>$sql"); 
}
}

//===================================================
// This function calculates the distance between two lat/long points and returns the value
function DistanceCalc($lat1, $lon1, $lat2, $lon2, $unit) { 

  $theta = $lon1 - $lon2; 
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); 
  $dist = acos($dist); 
  $dist = rad2deg($dist); 
  $miles = $dist * 60 * 1.1515;
  $unit = strtoupper($unit);

  if ($unit == "K") {
    return ($miles * 1.609344); 
  } else if ($unit == "N") {
      return ($miles * 0.8684);
    } else {
        return $miles;
      }
}

//===================================================
// This function generates the SQL Code for selecting the ZIP Codes in the radius.
// ** You may need to change the table name from ZIPCODES to your table name
function zipRadiusSQL($sqlFields, $varZip, $varLatitude, $varLongitude, $varMiles){

$varLatRange = $varMiles / ((6076 / 5280) * 60) + ($varMiles / 1000);
$varLongRange = $varMiles / (((cos($varLatitude * 3.141592653589 / 180) * 6076.) / 5280.) * 60) + ($varMiles / 1000);

	$zipRadiusSQL_str = "SELECT Latitude, Longitude, State, ZipCode, AreaCode, City, County FROM zipcodes WHERE City = CityAliasName AND (";
		$zipRadiusSQL_str = $zipRadiusSQL_str ."Longitude <= (". $varLongitude ." + ". $varLongRange .")";
		$zipRadiusSQL_str = $zipRadiusSQL_str ." AND ";
		$zipRadiusSQL_str = $zipRadiusSQL_str ."Longitude >= (". $varLongitude ." - ". $varLongRange .")";
	$zipRadiusSQL_str = $zipRadiusSQL_str .")";
	$zipRadiusSQL_str = $zipRadiusSQL_str ." AND (";
		$zipRadiusSQL_str = $zipRadiusSQL_str ."Latitude <= (". $varLatitude ." + ". $varLatRange .")";
		$zipRadiusSQL_str = $zipRadiusSQL_str ." AND ";
		$zipRadiusSQL_str = $zipRadiusSQL_str ."Latitude >= (". $varLatitude ." - ". $varLatRange .")";
	$zipRadiusSQL_str = $zipRadiusSQL_str .")";
	$zipRadiusSQL_str = $zipRadiusSQL_str ." AND Longitude <> 0 AND Latitude <> 0 ORDER BY ZipCode ASC";
return $zipRadiusSQL_str;
}
?>






<style type="text/css">
td{
font-size:11px;
color:#FFFFFF;
}
</style>

<p><b>Zip Code Radius Finder</b></p>

<form action="" method="post">
<table border="0" align="center" cellpadding="2" cellspacing="0" style="border:1px solid #FFB219;">
<tr>
  <td valign="right" style="border:none;" nowrap><strong>Find all ZIP Codes within</strong></td>
  <td valign="middle" style="border:none;">
    <select name="zipMilesHigh" id="zipMilesHigh">
      <option value="5"<? if ($zipMilesHigh == 5) echo " SELECTED" ?>>5</option>
	  <option value="10"<? if ($zipMilesHigh == 10) echo " SELECTED" ?>>10</option>
	  <option value="15"<? if ($zipMilesHigh == 15) echo " SELECTED" ?>>15</option>
	  <option value="20"<? if ($zipMilesHigh == 20) echo " SELECTED" ?>>20</option>
	  <option value="25"<? if ($zipMilesHigh == 25) echo " SELECTED" ?>>25</option>
    </select>
    miles
  </td>
</tr>
<tr>
<td width="50%" style="border:none;"><div align="right"><strong>of this ZIP Code:</strong></div></td>
<td width="50%" style="border:none;"><input type="text" name="zip1" id="from" maxlength="5" size="7" value="<?php echo $zip1 ?>"></td>
</tr>
<tr>
 <td colspan="2" align="center" valign="middle" style="border:none;"><input type="submit" name="Submit" value="Find Zip Codes"></td>
</tr>
</table>
</form>


<?
if ($_POST['Submit'] <> ""){
?>
<table width="100%" border="0" align="center" cellpadding="2" cellspacing="0" style="border:1px solid #FFB219;">
<tr>
<td><strong>#</strong></td>
<td><strong>Zip Code</strong></td>
<td><strong>Area Code</strong></td>
<td><strong>City</strong></td>
<td><strong>County</strong></td>
<td><strong>State</strong></td>
<td><strong>Distance</strong></td>
</tr>
<?
$i = 0;
$a = 0;

$num=mysqli_num_rows($result);
while ($i < $num) {
	//while($result_ar = mysql_fetch_assoc($result)){
	$a++;
	//$zipLatitude = $result_ar['Latitude'];
	//$zipLongitude = $result_ar['Longitude'];
	//$zipState = $result_ar['State'];
	//$zipZipCode = $result_ar['ZipCode'];
	//$zipAreaCode = $result_ar['AreaCode'];
	//$zipCity = $result_ar['City'];
	//$zipCounty = $result_ar['County'];


//for($i=0;$i<$num_rows;$i++){ 

// set pointer to row $i in $result 
mysqli_data_seek($result, $i); 
// get this row into array 
$row = mysqli_fetch_assoc($result); 
// get values 
$zipLatitude  = $row["Latitude"]; 
$zipLongitude = $row["Longitude"]; 
$zipState     = $row["State"]; 
$zipZipCode   = $row["ZipCode"]; 
$zipAreaCode  = $row["AreaCode"]; 
$zipCity      = $row["City"]; 
$zipCounty    = $row["County"]; 
// calc distance 
$zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, 
                        $zipLatitude, $zipLongitude, "M"), 2); 

if ($zipDistance < $zipMilesHigh){
	?><tr><td class="main"><b><? echo $a ?></b></td><td class="sub_nav_item_text"><? echo $zipZipCode ?></td><td><? echo $zipAreaCode ?></td><td><? echo $zipCity ?></td><td><? echo $zipCounty ?></td><td><? echo $zipState ?></td><td><? echo $zipDistance ?> miles</td></tr><?
}else{
	$a--;
	//nothing	
}
$i++;
}

if ($a > 25){
	?><tr><td><strong>#</strong></td><td><strong>Zip Code</strong></td><td><strong>Area Code</strong></td><td><strong>City</strong></td><td><strong>County</strong></td><td><strong>State</strong></td><td><strong>Distance</strong></td></tr><?
}
?>
</table>
<p align="center">Found <strong><? echo $a ?></strong> matches.</p>
<?
}else{
?>
<!-- Form not submitted -->
<?
}
?>

converting mysql_results to mysqli?

DDboz

Bbradgrafelman

DDboz

Bbradgrafelman

Hhalojoy

DDboz

DDboz