Click here to Skip to main content
15,509,806 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello, 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 NUMBERS 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

1.How to exclude 0 and -1 because SPACE must be separator
2.Column Function
function getNumber($id)
    {
        global $database;
        $stmt = $database->runQueryPlayer("SELECT NUMBERS 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" }


3.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 '';
}




what i use on file.php


What I have tried:

Solution 1.
function getNumber($id)
        {
            global $database;
            $stmt = $database->runQueryPlayer("SELECT NUMBERS FROM ITEMS WHERE a_index=?");
            $stmt->bindParam(1, $id, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll(PDO::FETCH_COLUMN);
			if ($result)
				return getName($result[0]);
			else {
				return '---';
			} }
        }


but i get translated only first Number

Solution 2.
function getNumber($id)
        {
            global $database;
            $stmt = $database->runQueryPlayer("SELECT NUMBERS FROM ITEMS WHERE a_index=?");
            $stmt->bindParam(1, $id, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll(PDO::FETCH_COLUMN);
			if ($result)
				return getName(explode(" ", $result[0]));
			else {
				return '---';
			} }
        }


Nothing
Posted
Updated 5-Jan-22 21:26pm
v2

1 solution

There could be two approachs over your problem.

First approach is to modify your flawed approach to the solution. Correct steps would be like.

1. Read Number columns from DB
2. Split Number column using space separator and put it into an array
3. For each element in the array
4    Do a look up into Translate table and read the Name column
5    Save the name column into another array
6  End For
7  Return the array


Second approach could be to solve the entire problem using SQL. For example you can use following SQL query that would return the same result.

SELECT TRANS.NAME 
FROM
(
SELECT NUMBERS 
FROM (SELECT '5000 -1 -1 -1 -1 -1 -1 -1 -1 15021 -1 -1 0 0 0 0 0 0 0 0 0 0 0 33' as NUMBERS, 'TEST' as a_index  ) ITEMS
WHERE a_index='TEST'
) T CROSS APPLY STRING_SPLIT(NUMBERS, ' ') P
INNER JOIN (SELECT 'Axe' AS NAME,'5000' as id
UNION ALL SELECT 'Sword ', '15021'
UNION ALL SELECT 'Stone', '33') TRANS ON P.value = TRANS.id


It is up to you to decide which approach is better.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900