SELECT STATEMENT and TRIG Functions

[finestjava]

This is from a store locator script I am trying to add functionality to. The original is you usual that finds nation wide locations. My current use is for a local version, where store type is more important than the distance but I want to keep the distance element.

 

MySQL Version 5.0

 

Original SELECT STATEMENT....This Works

 

$query = sprintf("SELECT address, name, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 50",

 

CREATE TABLE `markers` ( `name` varchar(255) NOT NULL, `address` varchar(255) NOT NULL, `lat` float(17,13) NOT NULL, `lng` float(17,13) NOT NULL, `type` varchar(255) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8

 

My attempt to add to this SELECT statement the ability to have only records that match 'type' and still compute the distance.

 

Here is the SELECT STATEMENT that does not work. I think it is trying to mix Strings with Trig functions

that is the issue but after 5 hours I am not sure of much here.

 

$query = sprintf("SELECT address, name, lat, lng, WHERE type= $type AND ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 50"0),

 

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE type= AND ( 3959 * acos( cos( radians('41.7917833366732') ) * cos( radian' at line 1

 

Sincerely appreciate any discussion on this.

 

Harry Osoff

Web Developmert

1537news

[fenway]

Then you should read about sprintf() and how it works -- but this is a PHP issue.

[finestjava]

Thanks for your reply... I am completely lost on this.

I will do as you suggested  and see if  the PHP forum can help.

 

Really did not think, just adding a WHERE  would be so difficult.

 

Cheers

[fenway]

I've deleted your double-post -- this problem has nothing to do with adding a WHERE clause.  In fact, if you simply hard-coded the type, it would work just fine.

[finestjava]

Hard code...I am not sure what you mean.

Do you mean putting in just one type of store?

 

Here is the link

http://1537news.com/SearchMap.html

 

You can see the distance works fine but totally ignores the type of store

in 60615 as your address.