SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Manipulating data from array into query

    Hello,
    I have a page that is querying a MySQL database for a list of courses available in a given city.
    The query is based on CITY NAME.
    Here is the WHERE statement:

    PHP Code:
    WHERE
         locations
    .locationcity '$locationcity' AND
         
    scheduledcourses.coursedate CURDATE()"; 
    Our needs have changed and I now need to do a search based on zip code. We want all classes to come up within 100 miles of the City zip code.
    I have tweaked a code which returns me all zip codes within 100 miles of the city zip code. This part works.
    The problem is that I cannot figure out how to get the results into the query.

    Example, I can echo the results of my zip code radius search
    Which looks like this
    PHP Code:
    foreach ($location1->getZipsInRange(0200) as $miles => $zip) {
        
        
    $miles round($miles1);
        foreach (
    $zip) { 
        echo 
    "$zip";

    This returns a long string of zip codes.
    Example:
    Code:
    330153301633017330183301933020
    Obviously in the echo statement I can place a space or an OR but I am not sure how to get this in to my MySQL Query Statement.

    Would need to be something like this
    PHP Code:
    WHERE
         locations
    .locationzip '33015 OR 33016 OR 33017 OR 33018' AND
         
    scheduledcourses.coursedate CURDATE()"; 
    I need to make the results of the zip code radius search into a variable that I can throw into my MySQL query.
    Can someone help?

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Code:
    WHERE 
       locations.locationzip IN(33015,33016,33017,33018)

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply. I know exactly how it would look in the query. Question is how do I structure the data out of the array?

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    $zip = array(111,222,333);

    $zips  "(" .  join(",",$zip) . ")" ;

    $sql " ... WHERE postcode in $zips";

    echo 
    $sql;

    //... WHERE postcode in (111,222,333) 

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok that seems to be the right track. The issue I am having is that I dont know how to build the array from my database.
    I need to find a way to construct the array from this:

    PHP Code:
    foreach ($location1->getZipsInRange(0200) as $miles => $zip) {
        
        
    $miles round($miles1);
        foreach (
    $zip) { 
        echo 
    "$zip";


  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    As you are showing very little code, we have to guess exactly what it is you are after.

    This loop you have instigated extracts the zips into a var called $zip.

    PHP Code:
    // here is the first loop
    foreach ($location1->getZipsInRange(0200) as $miles => $zip) {
        
        
    $miles round($miles1);

    // now you are doing a second loop, nested in the first

        
    foreach ($zip) { 
        echo 
    "$zip";  // this quoting is redundant
       


    // I think you must then end the first loop 
    Now, are you doing a db query for each of the first loops, so the sql query is being executed several times?

    OR

    Are you building up multiple zip variables for one single sql query?

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apologies for not showing the rest of the code. I will post,
    but basically the issue IM having is getting the list of zips into an array then using the array in the query. I will post some more when i get home


Bookmarks

Posting Permissions

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