Find which array an item is in given multiple arrays and item only exists in one in PHP/SQL

I have an ecommerce website and I have it set up that countries are split into different zones for shipping costs.

I have 8 zones, these are all arrays of country codes, there can be no duplicates in the arrays or items can’t exist in multiple arrays.

I then want to calculate the shipping costs for a given country. So I basically want to find out which zone the country is in.

What I do at the moment is I query my shipping zones by the items supplier to get all of the arrays and then do an if(in_array()) on each of these like so:

$params = [$supplier_id];
$sql = "SELECT * FROM shipping_zones WHERE supplier_id=?";
$stmt = DB::run($sql,$params);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $z1 = unserialize($row['zone1']);
    $z2 = unserialize($row['zone2']);
    $z3 = unserialize($row['zone3']);
    $z4 = unserialize($row['zone4']);
    $z5 = unserialize($row['zone5']);
    $z6 = unserialize($row['zone6']);
    $z7 = unserialize($row['zone7']);
    $z8 = unserialize($row['zone8']);
}

if(in_array($country_code,$z1)){
    $shipping_zone = 'z1';
}else if(in_array($country_code,$z2)){
    $shipping_zone = 'z2';
}else if(in_array($country_code,$z3)){
    $shipping_zone = 'z3';
}else if(in_array($country_code,$z4)){
    $shipping_zone = 'z4';
}else if(in_array($country_code,$z5)){
    $shipping_zone = 'z5';
}else if(in_array($country_code,$z6)){
    $shipping_zone = 'z6';
}else if(in_array($country_code,$z7)){
    $shipping_zone = 'z7';
}else if(in_array($country_code,$z8)){
    $shipping_zone = 'z8';
}

There has to be a better way of doing this, I thought it might be easier to do it in the initial SQL query but the actual arrays are serialized in the DB.

Answer

From your question it looks like the schema for shipping_zones looks something like this:

CREATE TABLE shipping_zones (
  supplier_id INT,
  zone1 TEXT, -- Each of these contains a big serialized PHP array
  zone2 TEXT,
  zone3 TEXT,
  zone4 TEXT,
  zone5 TEXT,
  zone6 TEXT,
  zone7 TEXT,
  zone8 TEXT
);

It sounds like there are 2 things you’d like to accomplish here:

1. Maintain data integrity (“there can be no duplicates in the arrays or items can’t exist in multiple arrays”)
2. Make your code simpler/more maintainable (“There has to be a better way of doing this…”)

Assuming you have full control over the application and the database, I would recommend changing your database schema so that your RDBMS can do more work for you. I suggest a schema that looks more like this:

CREATE TABLE shipping_zones (
  supplier_id  INT,
  zone         ENUM('z1','z2','z3','z4','z5','z6','z7','z8') NOT NULL,
  country_code CHAR(2) -- Not really sure what your codes actually look like...
);

CREATE UNIQUE INDEX idx_unique ON shipping_zones(supplier_id, country_code);

This way you can’t accidentally put a country in multiple zones for a given supplier, and your task of getting the correct code for the given supplier becomes as simple as the following query:

SELECT 
  zone 
FROM 
  shipping_zones 
WHERE 
  supplier_id = :supplier_id 
  AND country_code = :country_code;