Trying to assign something to a "rule" using logic in PHP and MySQL

Question

The premise of the project is this: A mailed package is assigned to particular courier based on its pickup and dropoff locations. It could be FROM a specific city, a state, a country, or a region (a group of countries, set up in another table) and then TO any of those choice.

At this moment, I have the package information in a table:

    package_id, pickup_city, pickup_state, pickup_country, 
dropoff_city, dropoff_state, dropoff_country.

There's also a cities table, a countries table, a states table, and a regions table.

The way I set up my "rules" table is to have a pickup_type (city,state,country,region), pickup_country, pickup_location.... and dropoffs of the same.

My problems comes in when I try to determine which rule is assigned to each package.

My initial thought is to get the package's pickup city... loop through the "city" type rules... if there aren't any matches, loop through the "state" rules, then "country" rules, then "regions". If it matches a rule, then it loops through all the dropoff rules to see if there's a match THERE.

So what I have now is a huge block of code with a bunch of "if/else" loops and I'm thinking it could be done at least a little simpler.

I'm just looking for some direction if you've ever had a project like this. Am I starting the right way? Do I need to restructure my entire schema? Is there an easier way to match the "rules" with the package's locations?

Thanks for any help you can give.

edit for clarification, let me give a scenario... A package is going from Los Angeles to Tokyo, Japan. In the "rules" table, there are these rules:

+ name          +  pickup_type  + pickup_country + pickup_location + dropoff_type + dropoff_country + dropoff_location +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ US to Asia    + country       + US             + null            + region       + null            + asia_region      +
+ US to Japan   + country       + US             + null            + country      + Japan           + null             +
+ LA to Japan   + city          + US             + Los Angeles     + country      + Japan           + null             +
+ LA to London  + city          + US             + Los Angeles     + city         + UK              + London           +

So the third rule is the one that's applicable. My current process loops through the pickup cities to find a match (#3 and #4), then loop through those results on the dropoff city... and if it can't find any match, tries the states, then country, then region.

And just now, I was thinking, "What is it's LA to Australia?"... my way would find LA, do all the work to loop through the dropoff locations, and then have to back-track to the pickup location when it didn't find anything.

I'm sure this is all clear as mud. Thanks for any advice.

edit 2
@shadyyx I've got the rules table shown, and the fields for the package information are at the top... and the rules table also has an ID field, which is what I want to return.

Theres also a "regions" table that is key/value pairs:

+ region_id + country_id +
++++++++++++++++++++++++++
+ 1         + JP         +
+ 1         + CN         +
+ 2         + US         +
+ 2         + CA         +

In this scenario, Region 1 is an Asia region with Japan and China, and Region 2 is North Amaerica with US and Canada

So my process becomes 1) check if the pickup city is part of a rule, 2) if not, check if the pickup state is part of a rule, 3) if not, check if the pickup country is part of a rule, and finally 4) if not, see if the country is listed in the "regions" table and get the region_id. Once I find that rule(s), then look through the dropoff rules and find a match there. I really do appreciate the help, and I'll try to provide as much as info as needed. Thanks again.

EDIT 3

After some more research, I found one potential solution by doing multiple SQL queries, then put the results for the pickup and dropoff rules into arrays, then comparing them. So to check the cities:

SELECT id FROM rules WHERE pickup_type = 'city' AND pickup_country = '$pu_country' AND pickup_location = '$pu_city';
// put the results into a flat array, then check the countries
SELECT id FROM rules WHERE pickup_type = 'country' AND pickup_country = '$pu_country';
// add those results to the same array... and so on.

Then do the same for the Dropoff rules. Then run array_intersect() on the two arrays to find the matching ones. The only problem is if there is a US->UK rule and a US->London rule, how do you figure which is the top rule? I guess that's where @pjskeptic's idea would come in.

Answer 1

Perhaps I'm misunderstanding, but I have a question tied to my answer:

What's up with the complicated set of rules? Shouldn't you track pickups and drop offs by address (which encapsulates the street number, city, state, country, and region), and figure it out from there? If you have a pickup in Australia and a drop off in England, the same courier isn't going to handle both sides of that (unless they're traveling all the way with the package). Maybe that's what you intend, whereas I'm thinking you're looking for a solution similar to UPS/FedEx, etc.

With the package companies, cities are broken into routes, couriers are assigned one or more routes, and the courier who picks stuff up or drops it off depends on whether or not it's on their route. This applies even if a package is going just from one end of a city to the other. Only if a package is to be picked up and dropped off within the same route would it be handled by a single courier.

If one courier is out sick (or for some other reason cannot cover their route for the day), their route is simply assigned to another courier, and similarly a courier can have an arbitrary number of routes assigned to them, and shuffled around at any time.

Answer 2

Add a field called "type" that ranks your dropoff and pickup types where lower numbers represent better (more precise) options:

1. city/city
2. city/country
3. country/city
4. country/country
5. city/region
6. region/city
7. country/region
8. region/country
9. region/region

Since you're storing tables of cities, countries, states, and regions, you can use their ids in your rules table. Modify your rules table to have a schema similar to the following

• name
• type (from above)
• pickup_type
• pickup_city_id
• pickup_state_id
• pickup_country_id
• pickup_region_id
• drop_type
• drop_city_id
• drop_state_id
• drop_country_id
• drop_region_id

Now, if you know your pickup and destination cities, you should be able to load their state, country, and region ids from your other tables. Then, you can perform the following query:

 SELECT * FROM rules WHERE ((pickup_type = 'city' && pickup_city_id = '$pickup_city_id') OR
(pickup_type = 'state' && pickup_state_id = '$pickup_state_id') OR
(pickup_type = 'country' && pickup_country_id = '$pickup_country_id') OR
(pickup_type = 'region' && pickup_region_id = '$pickup_region_id')) AND ((drop_type = 'city' && drop_city_id = '$drop_city_id') OR
(drop_type = 'state' && drop_state_id = '$drop_state_id') OR
(drop_type = 'country' && drop_country_id = '$drop_country_id') OR
(drop_type = 'region' && drop_region_id = '$drop_region_id')) ORDER BY type ASC

That query will look for rules that match your dropoff city, state, country, or region and your pickup city, state, country, or region. Because you're sorting by type, the first result in the set should be the most convenient courier.