Postcode Calculator

[billynastie2007]

Hi i am writing a balloon race site and i am having problems with some functions to calculate the distance the balloon travels firstly i am reading my info from the database using the fetch assoc function what i have is 2 table one for the race details and one for the postcode details what i am having problems with is listing all the races in the database then having a coloumn at the end which tell you how far the balloon has travelled i have the maths functions and in the race database i also have start and end postcodes what i need to know is how i can select a postcode from the race database based on row id and then match this with the postcode from the postcode database and also get the longitude and latitude details based on the postcode and is needs to do this for each row in the fetch assoc results this is what i have so far but i do know its not working and don't know how to correct it or if it is even possible.

<?php
$query = "SELECT * FROM `duchy`";
 
$result = mysql_query($query);
 
if ($result) {
 
    while ($array= mysql_fetch_assoc($result)) {
                echo $array['startcode']. " - ". $array['finderspostcode']. " - ". $array['ID'];
    echo "<br />";
 
 
    }
 
} else {
 
    print "<li>No results.</li>";
 
}
 
 
 
 
?>
<?
$sql = 'SELECT DISTINCT `lat` FROM postcode as P,duchy as T WHERE `code` LIKE T.startcode AND T.ID = $array[ID] '; 
$result=mysql_query($sql);
$lat1=mysql_fetch_assoc($result);
$sql2 = 'SELECT DISTINCT `lon` FROM `postcode` WHERE `code` LIKE `$postcode` LIMIT 0 , 30';
$result2=mysql_query($sql2);
$lon1=mysql_result($result2,"lon");
$sql3 = 'SELECT DISTINCT `lat` FROM `postcode` WHERE `code` LIKE `$endcode` LIMIT 0 , 30';
$result3=mysql_query($sql3);
$lat2=mysql_result($result3,"lat");
$sql4 = 'SELECT DISTINCT `lon` FROM `postcode` WHERE `code` LIKE `$endcode` LIMIT 0 , 30';
$result4=mysql_query($sql4);
$lon2=mysql_result($result4,"lon");
 
 
$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>";
  echo " The value after formating = $miles<br></td></tr>";
 
?>
<?
 
 
 
 
            echo '</table>'."\n";
 
 
 
 
?>

any help on this would be most appreciated.

[Atli]

Hi, billynastie2007, and welcome to TSDN!

First of all, they put a dot button on your keyboard for a reason! Please use it :)

I'm not sure what data you are working with. Could you show us the layout of those tables your using?

[RedSon]

Hi, billynastie2007, and welcome to TSDN!

First of all, they put a dot button on your keyboard for a reason! Please use it :)

I'm not sure what data you are working with. Could you show us the layout of those tables your using?

You mean the period button right?

[pbmods]

Heya, billynastie.

What is your code doing? Give an example.
What is your code supposed to be doing? Give an example.

[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 is underlined 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.

[billynastie2007]

Heya, billynastie.

What is your code doing? Give an example.
What is your code supposed to be doing? Give an example.

Hi Ive re-issued the code and a description for the help i need.