PHP Mysql Maths Problem

[billynastie2007]

Hi

Thankyou for your replies.

What i am doing is writing a simple website for a balloon race and what i am trying to do is run a php maths function on a database query for each row in the database.

I can get it to output the maths function results per row but what i am trying to get it to do is write the results of the maths function back to the database relating to each row.

Heres what i have so far.

 

<?php
$hostname = "localhost"; // The Thinkhost DB server. 
$username = "tech0000_main"; // The username you created for this database. 
$password = "admin"; // The password you created for the username. 
$usertable = "test"; // The name of the table you made. 
$dbName = "tech0000_balloon"; // This is the name of the database you made. 

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database"); 
?> 
<?php
// Make a MySQL Connection
$query = "SELECT * FROM duchy"; 
     
$result = mysql_query($query) or die(mysql_error());

echo "<table border='3' cellpadding='3' bordercolor='#000000'>\n<tr>\n" .
             "\n\t<th>Distance Covered</th>" .
          
          
          "\n</tr>";
while($row = mysql_fetch_array($result)){
echo "\n<tr>";

    $lat1=$row['startlat'];
$lon1=$row['startlon'];
$lat2=$row['endlat'];
$lon2=$row['endlon'];

$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;
  echo"<td>" .number_format($miles,2);
  ?>
  <?
  echo"- Miles</font></td>";
  
  
    echo "<br />";

}

?>

The bit that starts $theta is the maths function this calculates the distance between 2 points based on longitude and latitude when this script is run against the database it outputs the mileage based on information in each row but i need to save the results this creates back to the database to create a leaderboard.

 

Here is the table struture for the mysql database.

 

-- phpMyAdmin SQL Dump

-- version 2.10.1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Jul 22, 2007 at 12:55 AM

-- Server version: 5.0.41

-- PHP Version: 5.2.2

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

 

--

-- Database: `tech0000_balloon`

--

 

-- --------------------------------------------------------

 

--

-- Table structure for table `duchy`

--

 

CREATE TABLE `duchy` (

`kidsfirstname` varchar(255) collate latin1_general_ci NOT NULL,

`kidslastname` varchar(255) collate latin1_general_ci NOT NULL,

`findersfirstname` varchar(255) collate latin1_general_ci NOT NULL,

`finderslastname` varchar(255) collate latin1_general_ci NOT NULL,

`findersaddress` varchar(255) collate latin1_general_ci NOT NULL,

`finderspostcode` varchar(255) collate latin1_general_ci NOT NULL,

`findersemail` varchar(255) collate latin1_general_ci NOT NULL,

`endlat` varchar(255) collate latin1_general_ci NOT NULL,

`endlon` varchar(255) collate latin1_general_ci NOT NULL,

`endloc` varchar(255) collate latin1_general_ci NOT NULL,

`finderscontact` varchar(255) collate latin1_general_ci NOT NULL,

`startcode` varchar(255) collate latin1_general_ci default 'M6',

`startloc` varchar(255) collate latin1_general_ci NOT NULL default 'Salford',

`startlat` varchar(255) collate latin1_general_ci NOT NULL default '53.492',

`startlon` varchar(255) collate latin1_general_ci NOT NULL default '-2.297',

`miles` varchar(255) collate latin1_general_ci NOT NULL,

`ID` int(25) NOT NULL auto_increment,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=37 ;

 

--

-- Dumping data for table `duchy`

--

 

INSERT INTO `duchy` VALUES ('John', 'doe', 'Jane', 'Doe', '10', 'E17', '', '51.586', '-0.019', 'Walthamstow', '0161', 'm6', 'Salford', '53.492', '-2.297', '36.510334425', 23);

INSERT INTO `duchy` VALUES ('Emiy', 'Harrison', '', '', '', 'LS10', '', '53.762', '-1.531', 'Leeds', '', 'm6', 'Salford', '53.492', '-2.297', '36.51', 24);

 

The full working site can be found here  Balloon Race race id is duchy.

 

Thanks for any help you can assist with its most appreciated

[Barand]

You could leave out the php calculation and

 

UPDATE duchy
SET miles =
    60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(startlat))
    * SIN(RADIANS(endlat)) +  COS(RADIANS(startlat))
    * COS(RADIANS(endlat)) * COS(RADIANS(startlon-endlon))))