PDO explode return to function

Question

i have 2 functions, 1 is getting all numbers from a COLUMN and other function will transform numbers from COLUMN to NAME

The problem is, i have to Explode COLUMN because inside i have like that

SELECT NUMBER FROM ITEMS WHERE a_index=TEST will result

NUMBERS Column 5000 -1 -1 -1 -1 -1 -1 -1 -1 15021 -1 -1 0 0 0 0 0 0 0 0 0 0 0 33

Names Translated Axe -1 -1 -1 -1 -1 -1 -1 -1 Sword -1 -1 0 0 0 0 0 0 0 0 0 0 0 Stone

How should be Axe Sword Stone

How to exclude 0 and -1 because SPACE must be separator

1.Column Function

function getNumber($id)
    {
        global $database;
        $stmt = $database->runQueryPlayer("SELECT NUMBER FROM ITEMS WHERE a_index=?");
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_COLUMN);
        if ($result)
            var_dump(explode(" ", $result[0])); // Here i have to add translate function

        else {
            return '---';
        } }
    }

Result is that: --- array(25) { [0]=> string(0) "" [1]=> string(2) "78" [2]=> string(2) "26" [3]=> string(2) "50" [4]=> string(2) "28" [5]=> string(2) "-1" [6]=> string(2) "30" [7]=> string(2) "32" [8]=> string(2) "-1" [9]=> string(2) "-1" [10]=> string(2) "-1" [11]=> string(2) "-1" [12]=> string(2) "-1" [13]=> string(1) "0" [14]=> string(1) "0" [15]=> string(1) "0" [16]=> string(1) "0" [17]=> string(1) "0" [18]=> string(1) "0" [19]=> string(1) "0" [20]=> string(1) "0" [21]=> string(1) "0" [22]=> string(1) "0" [23]=> string(1) "0" [24]=> string(1) "0" }

If i use function return getName($result[0]); ill get only first number, rest are not showing, if i use return getName(explode(" ", $result[0])); nothing shows

2.Translate Function

function getName($id)
{
    global $database;

    $stmt = $database->runQueryPlayer('SELECT NAME FROM TRANSLATE
        WHERE NUMBER = ?');
    $stmt->bindParam(1, $id, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_COLUMN);

    if($result)
    return utf8_encode($result['0']);
    else return '';
}

Why do you store a data structure within a field? This is what tables and fields are for in a relational database! — Shadow, Jan 05 '22 at 21:53
@Shadow is right. Normalize your schema. Just store each of that numbers in a column or row depending on what the idea behind them is. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). Then you can simply join to get the names in one query. — sticky bit, Jan 05 '22 at 21:58
this is how my game was made, cannot change that because i have to change tons of code. — Laurentiu, Jan 05 '22 at 22:22
@Laurentiu so instead of doing the right thing and change your data structure, you keep adding php code that does what a simple join operation in sql could do. Brilliant! This way you keep increasing the technical debt! — Shadow, Jan 05 '22 at 22:42
@Laurentiu: Well, if it's truly your game, then you can and should change it. Besides that, storing the data in a normalized way doesn't mean you cannot denormalize it at *time of retrieval* (what, in part you're trying to do, when resolving the names). In fact doing that is pretty normal and a properly normalized schema makes that usually fairly easy. A not normalized one likely makes it pretty hard, as you experience... — sticky bit, Jan 05 '22 at 22:57

score 0 · Accepted Answer · answered Jan 05 '22 at 21:43

You need to call your getName() function for all numbers in the returned result, and keep only the values that returned a valid name. You can do that in a few ways, here's one example:

if ($result) {
    $numbers = explode(" ", $result[0]);
    $names = [];

    foreach ($numbers as $number) {
        $name = getName($number);
        if (!empty($name)) {
            $names[] = $name;
        }
    }

    var_dump($names);
    // should print out:
    // array(3) {
    //   [0] => string(3) "Axe"
    //   [1] => string(5) "Sword"
    //   [2] => string(5) "Stone"
    // }

    return implode(" ", $names);
}
else {
    return '---';
}

PDO explode return to function

1 Answers1