Hi all...

I'm working on an address search feature for my site, and can't figure out how to combine different functions in a MySQL query using PHP.

I'm trying to combine the functions of this query:

$query = sprintf("SELECT *, ( 3959 * acos( cos( radians( $center_lat ) ) * cos( radians( showtimes_venues.location_lat ) ) * cos( radians( showtimes_venues.location_lng ) - radians( $center_lng ) ) + sin( radians( $center_lat ) ) * sin( radians( showtimes_venues.location_lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius));


...into this query:

$query="
SELECT * FROM showtimes_showings
JOIN showtimes_venues
ON showtimes_showings.venue_id = showtimes_venues.venue_id
LEFT JOIN showtimes_casts
ON showtimes_showings.cast_id = showtimes_casts.cast_id
ORDER BY showtimes_venues.address_country, showtimes_venues.address_stateprov, showtimes_venues.address_city, showtimes_venues.name, showtimes_venues.subname, showtimes_showings.date_start DESC, showtimes_showings.date_end DESC
";

I'm not sure how to combine all of this so that the query actually works. Can one even use sprintf with JOINs?

-David

Could anyone even tell me how to integrate the equation into the query?

Can't see where some of your fields are coming from in the 1st select statement ie showtimes_venues.location_lat implies it's coming from the table showtimes_venues but this isn't referenced in the query.

How does the markers table join with other tables?

Do you just want to order by proximity as in the 1st select?

You can use spintf to build up any string, even strings that are really SQL statements and it doesn't matter whether that SQL includes joins or anything else for that matter.

Mike

Actually, I figured it out on my own. Thanks for tying to help though!

I got rid of sprintf and did a query within a query. Here's a link to the finished product:

RHPS Official Fan Site > Participation > Showtimes

-David