shnisaka shnisaka - 5 months ago 15
SQL Question

combine units with strengths using php

Let us say that I have a medication database with this format:

-----------------------------------------------
| Medication Name | Strength | Unit |
-----------------------------------------------
| NORCO | 5;325 | mg/1;mg/1 |
| Amoxicillin | 500 | mg/1 |
| Augmentin | 250;62.5 |mg/5mL; mg/5mL|
-----------------------------------------------


How can I display data in this way using php:

NORCO 5mg/325mg
Amoxicillin 500mg
Augmentin 250mg/5mL 62.5mg/5mL


removing
/1
from the unit column is easy using
str_replace
but how can I distribute units to strengths with a semicolon separating them?

Answer

You can use php explode function. There might be other better solutions, for starters you can try below code.

// Considering this is your data from database
$data = array(
            array(
                'name' => "NORCO",
                'strength' => "5;325",
                'unit' => "mg/1;mg/1"
            ),
            array(
                'name' => "Amoxicillin",
                'strength' => "500",
                'unit' => "mg/1"
            ),
            array(
                'name' => "Augmentin",
                'strength' => "250;62.5",
                'unit' => "mg/5mL; mg/5mL"
            ),
        );

// Looping through data
foreach ($data as $row) {
    $strength = explode(';', $row['strength']);
    $unit = explode(';', $row['unit']);
    $combine = combineStrengthUnit($strength, $unit);   
    echo $row['name'] . " " . $combine ;
    echo "<br/>";
}

// return combined Strength and Units
function combineStrengthUnit($strength, $unit)
{
    $combine_result = '';
    foreach ($strength as $key => $value) {
        $combine_result .= $value . trim(str_replace('/1', '', $unit[$key])) . " "; //trimming the spaces of unit
    }
    return $combine_result;
}

Output:

NORCO 5mg 325mg
Amoxicillin 500mg
Augmentin 250mg/5mL 62.5mg/5mL