CODEWITHSUNDEEP
phpmysql
Artur L
Artur L

Reputation: 45

Extracting a pack size from a column with mixed formats

I have a column pack_size in a table called product_master_test. The problem that I am facing is that the pack_size is in mixed formats, there is no uniformity to it.

For example:

I've been thinking about different approaches, but I can't think of anything that would work without having a lot of IF statements in the query/PHP code. Is there a solution that I am missing?

I do have the file in Excel, if there is an easier way to process it using PHP.

I am not including any code, as I'm not entirely sure where to start with this problem.

Upvotes: 1

Views: 95

Answers (2)

Nigel Ren
Nigel Ren

Reputation: 57131

Using a regex to split the pack size could at least give you the various components which you can then (possibly) infer more from...

$packs = ["4 x 2kg","48-43GM","12 x 1BTL","1 x 24EA", "12 X 1 EA"];

foreach ( $packs as $size ) {
if ( preg_match("/(\d*)(?:\s+)?[xX-](?:\s+)?(\d+)(?:\s+)?(\w*)/", $size, $match) == 1 )  {
        print_r($match);
    }
    else {
        echo "cannot determine - ".$size.PHP_EOL;
    }
}

(regex can probably be optimised, not my area of expertise). It basically splits it to be a number, some space with either a x or a - and then another number followed by the units (some text). The above with the test cases gives...

Array
(
    [0] => 4 x 2kg
    [1] => 4
    [2] => 2
    [3] => kg
)
Array
(
    [0] => 48-43GM
    [1] => 48
    [2] => 43
    [3] => GM
)
Array
(
    [0] => 12 x 1BTL
    [1] => 12
    [2] => 1
    [3] => BTL
)
Array
(
    [0] => 1 x 24EA
    [1] => 1
    [2] => 24
    [3] => EA
)
Array
(
    [0] => 12 X 1 EA
    [1] => 12
    [2] => 1
    [3] => EA
)

With the else part it should also give you the ones it cannot determine and perhaps allow you to change it accordingly.

Upvotes: 2

René Beneš
René Beneš

Reputation: 468

You could present an associative array of all the strings from the table as keys corresponding with correct pack_size you desire.

$packsize = ["4 x 2kg" => 4, "48-43GM" => 48, "12 x 1BTL" => 12, "1 x 24EA" => 24]; //add all pack_sizes here
echo $packsize["4 x 2kg"]; // Output: 4

Now you could get the acutal pack size via the key of associative array. It could save some time you would spend making if/else conditions or switching the input. I'm not sure if there is something wrong with this approach, so correct me if so.

Upvotes: 0

Related Questions