CODEWITHSUNDEEP
phpmysql
131
131

Reputation: 1385

bi-dimensional array when joining tables in PHP MySQL

I have the following tables, projects:

+----+----------+-------+------------+
| id | name     | admin | timestamp  |
+----+----------+-------+------------+
|  1 | Group1   |     2 | 1539678214 |
|  2 | Test     |     2 | 1539678214 |
+----+----------+-------+------------+

And users:

+----+----------------+------------------------+
| id | name           | email                  |
+----+----------------+------------------------+
|  1 | FOO            | [email protected]            |
|  2 | BAR            | [email protected]            |
|  3 | G2W            | [email protected]            |
+----+----------------+------------------------+

I run the following SQL command:

SELECT projects.id,projects.name,users.id as userid,users.email 
FROM projects 
INNER JOIN users ON users.id=projects.admin;

And I get the following result:

+----+----------+--------+-----------------+
| id | name     | userid | email           |
+----+----------+--------+-----------------+
|  1 | Group1   |      2 | [email protected]     |
|  2 | Test     |      2 | [email protected]     |
+----+----------+--------+-----------------+

And this is perfect, it's just what I need. Now my question is, what is the best way to do this in PHP and get the result in a bi-dimensional array.

This is what I have so far, but I get the result in a one-dimensional array, which i don't want:

$stmt = $_DB->prepare("SELECT projects.id,projects.name,users.id as userid,users.email 
                       FROM projects 
                       INNER JOIN users ON users.id=projects.admin");
if($$_DB->error) die($$_DB->error);

$stmt->execute();
$res=$stmt->get_result();
while($result[]=$res->fetch_array(MYSQLI_ASSOC)){}
print_r($result);

Result:

[0] => Array
    (
        [id] => 1
        [name] => Group1
        [userid] => 2
        [email] => [email protected]
    )

[1] => Array
    (
        [id] => 2
        [name] => Test
        [userid] => 2
        [email] => [email protected]
    )

This is what i want:

[0] => Array
    (
        [id] => 1
        [name] => Group1
        [admin] => Array
            (
                [id] => 2
                [email] => [email protected]
            )

    )

[1] => Array
    (
        [id] => 2
        [name] => Test
        [admin] => Array
            (
                [id] => 2
                [email] => [email protected]
            )

    )

Upvotes: 0

Views: 28

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Query results will always be in a Tabular format (rows and columns). Basically, it will be an array of arrays, wherein second-level array represents a particular row. If you want your results to be in Multi-dimensional array, you will have to do this using PHP code, after getting query output:

$res=$stmt->get_result();

// Initialize result variable
$result = array();

// Get results row by row
while( $row = $res->fetch_array(MYSQLI_ASSOC) ) {

    // In a temp variable, create the multi-dim array as you wish
    $temp['id'] = $row['id'];
    $temp['name'] = $row['name'];
    $temp['admin'] = array('id' => $row['userid'], 
                           'email' => $row['email']);

    // insert the temp result row
    $result[] = $temp;
}
print_r($result);

Upvotes: 2

Related Questions