Function within query statement?

slevytam

Hello,

I was wondering whether it is possible to have a function within a mysql query statement.

I am looking to do something like this:

$query = "select * from users LEFT JOIN usersmedia ON users.ID=usersmedia.ID AND usersmedia.importance='1' LEFT JOIN usersinfo ON users.ID=usersinfo.ID where users.seeking='$sex' AND users.age >= '$lowAge' AND users.age <= '$highAge' AND users.confirmed='1' AND distance($postal, users.postal) <= '$within' order by lastLogin";

This works fine until I add
AND distance ($postal, users.postal) <= '$within'

distance is a function which returns miles between postal codes ie 246.13

Thanks,

--SL

yeochins

It doesn't work because an SQL query assumes that the function is apart of the query itself, eg

mysql_query ("SELECT * FROM page WHERE (title LIKE '%$string%') AND (description LIKE '%$string%') LIMIT 20,30");

thats what it assumes even varibles not in " are proccessed. and because distance isn't a registered SQL argument it goes as a parse error or an invalid mysql_result_resource.

slevytam

Ok,

So how can I limit the query to zip codes which are within a certain distance from a given zip code?

Thanks for your help,

--SL

abnfire

mysql_query (" SELECT *
FROM page
WHERE (title LIKE '%$string%')
AND (description LIKE '%$string%')
and zip_code = "45654"
LIMIT 20,30");

this will solve your question

slevytam

Thanks for your reply; however, I don't see how this will solve my question.

LIMIT simply limites the query to specified rows.

I have a function distance and the start zip code(zipstart).

I need to pass each zip code the in table(zipdestination) to the function distance.

Then only include record with (zipdestination) in the query if distance between it and (zipstart) is less than a specified constant.

I don't know if I am making myself clear enough. Let me know.

Thanks,

--SL

abnfire

i really dont get it what you want to do. do you want only distination one to show or you want both the start and detination. make your question more clear.

slevytam

Ok

I have a table with all zips codes in the United States.
I have a table of all users which includes their zip code.
I have the zip code of my current user : $zip1.
I have a variable $within=1000;
I have function distance which calculates the distance between to zips.

What I need is to query table users and return only those users which reside within 1000 miles of current user.

Is this clear?

So basically i need this:
select * from users where zip1 < 1000miles from zip2;

So I tried putting the function in the query but that does not work
select* from users where distance($zip1, $zip2) <= $within;

I am stuck as to how I can make this work.

Thanks for your assistance,

--SL

abnfire

you need to have a table which as full usa miles with the state and cities. then you will calculate the start point with distination and get your result, but remember when i said full miles of usa thats not mean just get each miles of city.

example:
wronge.
NY miles = 300,000;
NJ miles = 258,987;

right:
NY miles = 300,000;
NJ miles = 528,987;

get the full usa miles with state and cities without getting sperated from each other.

this will take lots of time, i recommand to drop the option for getting within miles. just give them option for zip code to zip code.

slevytam

I know this can be done quite simply using queries. I just don't have the experience in writing queries.

Here is an example that gets the 30 closest zips. I am trying to alter it; however, I figured someone might be able to help figure it out quicker...

http://www.darkshire.org/~jhkim/programming/zipcodes/showsource.cgi?file=lookup.php

Thanks again for your time.

--SL