I need help for MYSQL and PHP QUERY COMPUTATION and sorting

lordojazz

i have a database table called tbl_city i have attached a sample tbl_city which includes the latitude and the longtitude, w/ this database i wanna compute the distance from my city which is montreal to other cities and sort them ASC and DESC, given the latitude of montreal 45.5 and the longtitude is -73.5833333...

SELECT , ((degrees(acos(sign(radians(45.5)) sign(radians(latitude)) + cos(radians(45.5)) cos(radians(latitude)) cos(radians(-73.5833333 - longtitude))))) 60) 1.1515) * 1.609344 FROM tbl_city;

the answer is in kilometers, i have tried this sqlStmt but there's an error, it gives me null result, and i also want to sort it ascending or descending, can anyone help me with this... thanks....

CREATE TABLE tbl_city (
country_id char(3) default NULL,
city char(75) default NULL,
accent_city char(75) default NULL,
province_id char(3) default NULL,
latitude char(25) default NULL,
longtitude char(25) default NULL,
id int(11) NOT NULL auto_increment,
PRIMARY KEY (id)
) TYPE=MyISAM AUTO_INCREMENT=5538 ;

-- Dumping data for table `tbl_city`

INSERT INTO tbl_city (country_id, city, accent_city, province_id, latitude, longtitude, id) VALUES ('CA', 'abbey', 'Abbey', '11', '50.7333333', '-108.75', 1),
('CA', 'abbotsford', 'Abbotsford', '02', '49.05', '-122.3', 2),
('CA', 'abbotsford', 'Abbotsford', '10', '45.4333333', ' -72.8833333', 3),
('CA', 'abbottsford', 'Abbottsford', '02', '49.05', '-122.3', 4),
('CA', 'abee', 'Abee', '01', '54.2333333', '-113.0166667', 5),
('CA', 'aberdeen', 'Aberdeen', '11', '52.3166667', '-106.2833333', 6),
('CA', 'abord a plouffe', 'Abord â Plouffe', '10', '45.55', ' -73.7333333', 7),
('CA', 'abrahams cove', 'Abrahams Cove', '05', '48.5166667', ' -58.9', 8),
('CA', 'acadia valley', 'Acadia Valley', '01', '51.15', '-110.2', 9),
('CA', 'acme', 'Acme', '01', '51.5', '-113.5', 10),
('CA', 'actinolite', 'Actinolite', '08', '44.55', ' -77.3333333', 11),
('CA', 'acton', 'Acton', '08', '43.6333333', ' -80.0333333', 12),
('CA', 'acton vale', 'Acton Vale', '10', '45.6333333', ' -72.5666667', 13),
('CA', 'adams landing', 'Adams Landing', '01', '58.3833333', '-115.1166667', 14);

sneakyimp

the answer is in kilometers, i have tried this sqlStmt but there's an error, it gives me null result, and i also want to sort it ascending or descending, can anyone help me with this... thanks....

what is the error?

Roger_Ramjet

Dunno bout the equation, take your word for it.
Anyway, to debug it you break it down into bits.

"SELECT *, ((degrees(acos(sign(radians(45.5)) * sign(radians(latitude)) + cos(radians(45.5)) * cos(radians(latitude)) * cos(radians(-73.5833333 - longtitude))))) * 60) * 1.1515) * 1.609344 FROM tbl_city";

// becomes
"SELECT *, degrees(acos(sign(radians(45.5)))) AS a, sign(radians(latitude)) AS b, cos(radians(45.5)) AS c, cos(radians(latitude)) AS d, cos(radians(-73.5833333 - longtitude)) AS e FROM tbl_city";

See which bit is returning NULL. Then rbuild it expression by expression.