Question

Locked

Calculated fields in MySQL/PHP scripts

By Pgledhill ·
Looking more for advice than a script here folks. Trying to calculate distances between two post codes. Got that bit sorted. However, I want to calculate the distance from an origin to each result of a query. Can make the thing work for the first row, but the query then lists that figure for each subsequent row. Can't find any good tutorials for calculated fields in MySQL/php. It's those tutorials I'm looking for please. Any pointers?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Well that sounds like you need

by Tony Hopkinson In reply to Calculated fields in MySQ ...

To put you distance routine in a Function

MySql's Create Function

Then say
Select *,Distance(OriginPostCode,Destinations.PostCode)
From Destinations

Or some such should do the job.

Collapse -

Similar thinking

by Pgledhill In reply to Well that sounds like you ...

Thanks for your reply Tony. I'm working along those lines. The distance calculation is a function which I call at the beginning of each row of the query, - hence it calculates the distance specific to that row. However the main results are from a different query run off a different table. There are therefore no links at the point I query the server. I'm working on subquery or union query approach. I can do this with Access so I'm obviously creating my own confusion. I'll keep you informed.

First time I've used a forum. Help most appreciated.

Collapse -

You need to get a join in there realy

by Tony Hopkinson In reply to Similar thinking

Even if you alias one

Ther's another trick which Inthink now works on later versions of MySQL

Select F1,F2,Distance(F1,F2) From (SomeQuery) dummytablename

So you can do the union first (watch the fieldNames) and then do the calculating..

HtHs
If you want to post the query without the content of your distance function, one of us might spot it.

The other thing I do when I start disappearing up my own arse, is to do it bit by bit, screw up one intermediate result set somewhere an you can be looking in the wrong place for a long time.

Collapse -

Update

by Pgledhill In reply to You need to get a join in ...

Thanks for your reply on Saturday. Just to update you because I don't want you to think I have taken your help for granted.

The distance calculation is readily available and called the Haversine Formula and no secret on the internet. I can't take credit for that.

It took a while but the penny finally dropped about your first post. ie a MYSQL function. I was still thinking with my php head on. The problem is that the same mathematical functions don't seem to exist in MySQL. Or I can't find them anyway. However, you got me thinking. I created an expression in SQL - "Expression1 AS" etc so that I have a field to sort data on, which works. Now I am simply trying to create a less precise and quick (all server side remember) calculation that achieves the same order as the distances.

By the way, I'm happy to share my code when it's right, nothing that I am doing is particularly revolutionary anyway. I feel the logic here was more relevant than its execution and you have been a great help in getting me thinking even if I missed your point at first.

Many thanks.

PS. What I also realised was the AJAX could be the ideal thing here and when I have a bit of time, I'm going to do some swotting. Bet I end up re-writing this thing eventually!!

Collapse -

I had my Uk Postcode head on

by Tony Hopkinson In reply to Update

That solution is a bit more complex.

A thing to bear in mind is every bit of processing you can do in the mysql server through sql before you get near PhP is almost certainly going to be worth it

Which functions are you missing by the way ?

I had a quick look and they all seemed to be there.

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

Related Discussions

Related Forums