Flattening a SQL query result for PHP array

Question

I have a SQL table (modules) with two columns (id, name). Now I can retrieve the rows from this through a PHP script but what I want is to use the value of id as the key, and the value of name as the value, in a multidimensional array. Then I want to be able to encode those into a JSON, retaining the relationship between key/value. I've muddled something together but it returns null.

the relevant code from index.php

        $mod1 = $core["module1"];
        $mod2 = $core["module2"];
        $modules = $db->getModulesById($mod1, $mod2);   //module names & ids

        $response["module"]["mod1"] = $modules[$mod1];
        $response["module"]["mod2"] = $modules[$mod2];
        $response["module"]["mod1name"] = $modules[$mod1]["name"];
        $response["module"]["mod2name"] = $modules[$mod2]["name"];
        echo json_encode($response);

The function from DB_Functions.php

public function getModulesById($mod1, $mod2) {
    require_once 'include/Config.php';
    $con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
    // Check connection
    if (!$con)
      {
        die("Connection error: " . mysqli_connect_error());
      }
    // selecting database
    mysqli_select_db($con, DB_DATABASE) or die(mysqli_connect_error());

    $query = "SELECT * FROM modules WHERE id= '$mod1' OR id='$mod2'";
    $result = mysqli_query($con, $query);
    $arr = array();
    while($row = mysqli_fetch_assoc($result)) { 
        // process each row
        //each element of $arr now holds an id and name
        $arr[] = $row;
    }
    // return user details
        return mysqli_fetch_array($arr);
        close();
}

I've looked around but I'm just not 'getting' how the query return is then broken down into key/value for a new array. If someone could ELI5 I'd appreciate it. I'm just concerned with this aspect, it's a personal project so I'm not focusing on security issues as yet, thanks.

RiggsFolly · Accepted Answer · 2017-04-16 18:42:03Z

1

You are pretty well there

public function getModulesById($mod1, $mod2) {
    require_once 'include/Config.php';
    $con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
    // Check connection
    if (!$con) {
        die("Connection error: " . mysqli_connect_error());
    }

    $query = "SELECT * FROM modules WHERE id= '$mod1' OR id='$mod2'";
    $result = mysqli_query($con, $query);
    $arr = array();
    while($row = mysqli_fetch_assoc($result)) { 
        $arr[] = $row;
    }
    // here is wrong
    //return mysqli_fetch_array($arr);

    // instead return the array youy created
    return $arr;
}

And call it and then just json_encode the returned array

$mod1 = $core["module1"];
$mod2 = $core["module2"];
$modules = $db->getModulesById($mod1, $mod2);   //module names & ids

$response['modules'] = $modules;

echo json_encode($response);

You should really be using prepared and paramterised queries to avoid SQL Injection like this

public function getModulesById($mod1, $mod2) {
    require_once 'include/Config.php';
    $con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
    // Check connection
    if (!$con) {
        die("Connection error: " . mysqli_connect_error());
    }

    $sql = "SELECT * FROM modules WHERE id= ? OR id=?";
    $stmt = $con->prepare($sql);
    $stmt->bind_param('ii', $mod1, $mod2);
    $stmt->execute();

    $result = $stmt->get_result();

    $arr = array();
    while($row = mysqli_fetch_assoc($result)) { 
        $arr[] = $row;
    }
    // here is wrong
    //return mysqli_fetch_array($arr);

    // instead return the array youy created
    return $arr;
}

edited Apr 16, 2017 at 18:42

answered Apr 16, 2017 at 18:20

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DarkMalice Over a year ago

If I'm wanting to add the $arr contents alongside other data to the JSON, say an existing array $response, how would I go about adding $arr to $response? Push?

RiggsFolly Over a year ago

I amended the code to add the modules array returned from the function to your existing $response array

Dygnus · Accepted Answer · 2017-04-16 18:19:05Z

1

mysqli_fetch_array requires the result of a mysqli_query result. Passing the constructed array to mysqli_fetch_array() is not going to work.

If you want to have a specific value from a row to use as its key, you can't resolve this with any mysqli_* function. You could however construct it yourself:

while($row = mysqli_fetch_assoc($result)) { 
    // process each row
    //each element of $arr now holds an id and name
    $arr[$row['id']] = $row;
}

mysqli_close($con);

return $arr;

You should close the connection before returning the result, code positioned after a return will not be executed.

answered Apr 16, 2017 at 18:19

Dygnus

6416 silver badges16 bronze badges

Collectives™ on Stack Overflow

Flattening a SQL query result for PHP array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related