Databases
StackOverflow2013
Postsdbo
POTrying to assign something to a "rule" using logic in PHP and MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrying to assign something to a "rule" using logic in PHP and MySQL
    primarykey
    data
    text
    <p><strong>The premise of the project is this</strong>: 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.</p> <p>At this moment, I have the package information in a table: </p> <pre><code> package_id, pickup_city, pickup_state, pickup_country, dropoff_city, dropoff_state, dropoff_country. </code></pre> <p>There's also a cities table, a countries table, a states table, and a regions table.</p> <p>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.</p> <p>My problems comes in when I try to determine which rule is assigned to each package.</p> <p>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.</p> <p>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.</p> <p>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?</p> <p>Thanks for any help you can give. </p> <p><strong>edit</strong> 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:</p> <pre><code>+ 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 + </code></pre> <p>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.</p> <p>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.</p> <p>I'm sure this is all clear as mud. Thanks for any advice.</p> <p><strong>edit 2</strong><br> @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. </p> <p>Theres also a "regions" table that is key/value pairs: </p> <pre><code>+ region_id + country_id + ++++++++++++++++++++++++++ + 1 + JP + + 1 + CN + + 2 + US + + 2 + CA + </code></pre> <p>In this scenario, Region 1 is an Asia region with Japan and China, and Region 2 is North Amaerica with US and Canada</p> <p>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.</p> <p><strong>EDIT 3</strong> </p> <p>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:</p> <pre><code>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. </code></pre> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTerryMatula
    1. USTerryMatula
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COHi Terry, could you clarify what you are actually wanting to return from the rules table? In the example above it sounded like you already knew the 'LA' and 'Japan' part that you were using to lookup 'something' in your rules table. I couldn't understand from what you put why you couldn't just lookup `($from . ' to ' . $to)` directly and skip out all the loops altogether. Perhaps I am missing something?
      singulars
      1. POTrying to assign something to a "rule" using logic in PHP and MySQL
      1. USeyaka1
    2. COI suppose that all of these rules are stored in one table in the database and the rule determines whether a package could be delivered from pickup to dropoff. Therefore I'd recommend using one SQL query with complex joins and conditions that will return `1` if the rule was found or `false` when no rule was found. No need of bunch of if/else would be needed... If you provide us some DB tables schema I'll try to create the query for You...
      singulars
      1. POTrying to assign something to a "rule" using logic in PHP and MySQL
      1. USshadyyx
    3. CO@eyaka1 I'm trying to return the ID from rules table that matches the pickup and dropoff location. The reason for the loops...if, in my scenario, the picxup was Boise, Idaho and dropoff was Osaka, Japan, I would need to make sure that Boise was not in a cities pickup rule, and then backtrack to use the US pickup rule... and the generic Japan dropoff rule.
      singulars
      1. POTrying to assign something to a "rule" using logic in PHP and MySQL
      1. USTerryMatula
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload