Select data by city or zip code

[Steveinid]

I have a database with a bunch of businesses. Each business has an address with zip code. The user will want to find businesses based on location such as city or zip. I can set up a form that will find by city and a separate form that will find by zip. But, I would like to have a form that does all in one.

A point in the right direction would be helpful.

Thank you.

 

 

[Barand]

$stmt = $pdo->prepare("SELECT <whatever columns you need>
                       FROM business
                       WHERE city = ?
                          OR zipcode = ?
                     ");
$stmt->execute([ $search, $search ]);
$results = $stmt->fetchAll();  
foreach ($results as $row)  {
    // output result row
}

 

[Steveinid]

I figured this might be a simple answer... Didn't think of the 'OR' operator.

I look at this and my initial question is how does it know which is which? However, after staring at it for too long am I right to guess (me being rather new at this) that it searches both columns and outputs based on the results? Since no city will ever match a zip code and vice versa it has no choice but to choose one. Just trying to understand fully what I'm doing.

Thank you for the answer. I can now go back and fix my code that I messed up yesterday.

 

[Phi11W]

18 hours ago, Steveinid said:

my initial question is how does it know which is which?

I suspect there might be a typo - most unexpected - in Barand's answer. 

Perhaps this makes it a little clearer:

$stmt->execute( [ $enteredCity, $enteredZipcode ] );

Personally, I'd choose to build the query dynamically, based on which search criteria were entered, then bind the entered values into that, but that may be a bit overkill in this case. 

 

Regards, 
   Phill  W.

 

 

[maxxd]

It's not a typo. Steveinid is pretty much correct in the assumption that the query will match either a city or a zip code. If you enter a zip code, the city obviously won't return a result and vice-versa. You're searching the contents of two columns using one criteria and returning anything that matches.

[phppup]

I would choose my query based on a careful evaluation of the data held in the database.

If all addresses have an accurate city and zip code and your user is searching for a nearby business you may want to prioritize the zip code search since some cities use more than one zip code to cover the geography.

You then need to know the boundaries. If a large city has six zip codes, are they set up like a 2 column grid where, 1 and 4 are at the top of each column, or in a circle with 1 in the center?

The USPS may not be as logical as you desire but if the user is in a specific zip code and there's a match with a business in the same zip code: BINGO.

Otherwise, a citywide result that lets the user determine where he wants to visit may be the best route.

Edited September 5, 2022 by phppup
Forgot item