0

I want to retrieve the values of NEW_COLUMN_1 and NEW_COLUMN_2 columns from the database, using distinct CR numbers from the NUMBER column in the following function below:

function retrieveDistinctFilePaths(array $distinctCRNumbers, $database)
{
    $filePaths = [];

    echo "<pre>"; print_r($distinctCRNumbers); echo "</pre>";  // Line A
    foreach ($distinctCRNumbers as $crNumber) {
        $query = "
        SELECT
            NEW_COLUMN_1,
            NEW_COLUMN_2
        FROM
            your_table_name
        WHERE
            NUMBER = '$crNumber'";

        // Execute the query and fetch results from the database
        $database->executeStatement($query);

        // Fetch the row from the result
        $row = $database->fetchRow();

        if ($row) {
            $filePaths[$crNumber] = [
                'NEW_COLUMN_1' => $row['NEW_COLUMN_1'],
                'NEW_COLUMN_2' => $row['NEW_COLUMN_2']
            ];
        }

    }
    
    echo "<pre>"; print_r($filePaths); echo "</pre>";  // Line Y
    return $filePaths;
}

Line A in the function above prints the following:

Array
(
    [0] => AUTH-GOOD-MORNING
    [1] => GOOD-MORNING
)

Line Z in the function above outputs (prints) the following, as shown below. The problem with the output from Line Z is that it doesn't print the result based on 'GOOD-MORNING' array value from Line A.

Array
(
    [AUTH-GOOD-MORNING] => Array
        (
            [0] => Array
                (
                    [NEW_COLUMN_1] => 
                    [NEW_COLUMN_2] => 
                )

        )

)

Problem Statement: I am wondering what changes I need to make in the function above so that Line Z prints values based on both array values at Line A.

flash
  • 1,455
  • 11
  • 61
  • 132
  • 1
    The result you show implies that there's no row in the table for `GOOD-MORNING`, so the `if ($row)` test for that `$crNumber` fails. – Barmar Aug 14 '23 at 04:17
  • Ahh, I got it now. I have been trying to figure this out for a while. Now, I've got it. There is one record in the table for `AUTH-GOOD-MORNING` and its NULL both for `NEW_COLUMN1` and `NEW_COLUMN2`. – flash Aug 14 '23 at 04:22
  • 3
    Your code is ***HIGHLY EXPOSED*** to [SQL Injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) – Abdulla Nilam Aug 14 '23 at 05:47

1 Answers1

0
function retrieveDistinctFilePaths(array $distinctCRNumbers, $database)
{
    $filePaths = [];

    echo "<pre>"; print_r($distinctCRNumbers); echo "</pre>";  // Line A
    foreach ($distinctCRNumbers as $crNumber) {
        $query = "
        SELECT
            NEW_COLUMN_1,
            NEW_COLUMN_2
        FROM
            your_table_name
        WHERE
            NUMBER = '$crNumber'";

        // Execute the query and fetch results from the database
        $database->executeStatement($query);

        // Fetch all rows for the current CR number
        $rows = $database->fetchAll();  // Fetch all rows

        foreach ($rows as $row) {
            $filePaths[$crNumber][] = [
                'NEW_COLUMN_1' => $row['NEW_COLUMN_1'],
                'NEW_COLUMN_2' => $row['NEW_COLUMN_2']
            ];
        }
    }

    echo "<pre>"; print_r($filePaths); echo "</pre>";  // Line Z
    return $filePaths;
}
Anoob
  • 22
  • 1