Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: MySQL/PHP Query and 'sprintf' issue

    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

  2. #2
    Join Date
    May 2009
    Posts
    3
    Could anyone even tell me how to integrate the equation into the query?

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by dahveed76
    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

  4. #4
    Join Date
    May 2009
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •