Calling a function in a WHERE clause with data from the record in the parms!

[CavySpirit]

I was sure I had seen this being done somewhere else, but I sure as heck can't get it to work!

I have a mySQL query. Here's the important part:

SELECT other_fields,lat,lon FROM myfile WHERE distance(lat,lon,$centerlat,$centerlon,\"M\")<$radius

I have tried putting { } around the expression, tried making M a variable, etc. The big problem is lat and lon are NOT being passed in the expression and even if I just plug numbers in to test it, I'm getting column or syntax errors.

I am calling a function "distance" which returns the miles of the distance between two locations, the lat and longitude from the record and the one from the cookie. I am trying to only select the records that fall within the desired radius--say 50 miles. The function works. The center fields and radius are being input properly.

Please help???
Thanks,
Teresa

[Barand]

And is distance() a PHP function or a function you have defined in MySQL?

[CavySpirit]

It's a PHP function I have defined in my script.

[Barand]

Then your MySQL server knows nothing about it. You can only call a php function from within php.

[CavySpirit]

Since you put it that way  :o  That makes sense!  :D

I guess it's back to the drawing board for this cookie!
Thanks,
Teresa

[Barand]

If you have MySQL v5 you can define your own functions in which case it will work.

Otherwise you have to pull the lat and long and loop through the results, using that function on each record returned. to test if it's within the radius. Or read them all into an array and use an array filtering function to get the ones you want.