CODEWITHSUNDEEP
phpmysqlloops
Keatonic
Keatonic

Reputation: 23

PHP if zero results from first MySQL query, loop another query until until results found

I've got a problem that's probably pretty basic, but completely puzzling me.

I have a website that pulls nearby zip codes from a location (i.e. if I make my location Detroit, Michigan then I get the zip codes 48201, 48272, 48260, 48226, 48255, 48275, 48267, 48231, 48268, 48278, etc.) The amount it grabs is variable (Detroit gets 28, Baltimore, MD gets 15).

The website is grabbing nearby locations for deliveries of flowers (In this case, the location is funeral homes).

If there are no funeral homes in the current zip code (i.e. 48201) it should then check neighboring zip codes from $zipListArray (48272, etc) until 5 results are found.

I have tried two different bits of code...

$num_rows = mysql_num_rows($result); //This is from the previous query, where it checks the immediate zip code
$i = 0;
while ($num_rows < 5) {
   $result = mysql_query('SELECT funeralh_name, address1, city, state_abbr, zipcode, phone FROM funeral_homes WHERE city != "" AND zipcode = "'.$zipListArray[$i].'" AND state_abbr = "'.$abbr.'"');
   $num_rows = mysql_num_rows($result);
   $i++;
}

This code would be an infinite loop on any smaller city or group of cities where there are not 5 neighboring funeral homes.

$inc = 0;
if ($num_rows == 0) {
   foreach ($zipListArray as $zip) {
      $result = mysql_query('SELECT funeralh_name, address1, city, state_abbr, zipcode, phone FROM funeral_homes WHERE zipcode = "'.$zip[$inc].'"');
      $total = 0;
      $num_rows = mysql_num_rows($result);
      $total += $num_rows;
      if ($total == 5){
         break;
      }
      $inc++;
      }
}

This one I couldn't get to work at all.

Upvotes: 1

Views: 906

Answers (4)

Mike Brant
Mike Brant

Reputation: 71384

Rather than making multiple queries queries against the database in a loop does not seem like a good approach. PeeHaa makes some good suggestion in his answers, but if you don't want to worry about geolocation technologies, why not just run a single query like

SELECT funeralh_name, address1, city, state_abbr, zipcode, phone
FROM funeral_homes WHERE zipcode IN ('12345', '23456', ...)
ORDER BY zipcode ASC

This means you would then have to work with a larger set of data and manually inspect the result set to first find the ones with exact zip code match and then look at alternates. But maybe this is less trouble for you than trying to work with geospatial data.

Alternatively, You could first do a LIMIT 5 search on just your zip code and if that returns less than 5 records, You could do a second query against all the other zips in the list, limiting the number of results to the number needed to give you 5 total. This would give a maximum of 2 queries against the database.

Personally I would use Google API or MySQL spatial extensions (http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html) to do this work.

Upvotes: 0

PeeHaa
PeeHaa

Reputation: 72671

This sounds like a terrible idea (querying the database until you have what you got).

Why don't you just improve the query so that it will get the nearest zip code(s) in 1 query (look into the abs() function)?

Or add the coordinates in the database in use the GEO functions of the database engine. Or if that it not avaiable the haversine formula.

P.S.

Nearest zipcodes are often not nearest location. Use some GEO / maps API for that.

Update

I kinda misread your question, but my initial statement still stands. It is a stupid idea to do multiple queries for this kind of thing

I only now see you are already having a ziplist to check against. Although I still think this may not be the optimal approach if you really want to go that route you should use in() operator.

Upvotes: 3

Logan Serman
Logan Serman

Reputation: 29880

if ($num_rows == 0) {
    $zips = implode(', ', $zipListArray);
    $result = mysql_query('SELECT funeralh_name, address1, city, state_abbr, zipcode, phone FROM funeral_homes WHERE zipcode IN (' . $zips . ') LIMIT 5');
}

Upvotes: -1

Marc B
Marc B

Reputation: 360742

You'd be better off doing a WHERE ... IN search for all of those possible zipcodes, e.g.

$zips = implode(',', $zipListArray);
$sql = "SELECT ... WHERE ... AND zipcode IN ($zips)";
                                         ^^^^^^^^^^

This way you only run one query, you get all possible zips, and you can add LIMIT clauses to restrict the results to only 5 rows - or only fetch a max of 5 rows. Either way, you eliminate that for() loop so you don't end up with the infinite loop in case there aren't 5+ nearby flower places in the first place.

Upvotes: 0

Related Questions