Jump to content


Photo

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


  • Please log in to reply
5 replies to this topic

#1 CavySpirit

CavySpirit
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 13 September 2006 - 09:43 PM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 13 September 2006 - 09:50 PM

And is distance() a PHP function or a function you have defined in MySQL?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 CavySpirit

CavySpirit
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 13 September 2006 - 10:00 PM

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

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 13 September 2006 - 10:04 PM

Then your MySQL server knows nothing about it. You can only call a php function from within php.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 CavySpirit

CavySpirit
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 13 September 2006 - 10:08 PM

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

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 13 September 2006 - 10:14 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users