bi-dimensional array when joining tables in PHP MySQL

Question

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]
            )

    )

There is no ready way to accomplish what you want. You'd need to write the code youself to interpret the results in whatever structure you see fit. — yivi
– yivi, Commented Oct 19, 2018 at 8:27
How does laravel do it? Don't they use a plugin or something? — 131
– 131, Commented Oct 19, 2018 at 8:28
The same way the framework does everything else. (E.g. interpreting query parameters.) Just by writing the appropriate code to implement the desired functionality. — yivi
– yivi, Commented Oct 19, 2018 at 8:29
@131 Laravel uses Eloquent ORM. ORM do it in PHP layer. Querying on RDBMS like MySQL will always give you a tabular format data ( array of arrays) - second level array being individual row — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 19, 2018 at 8:30
Just do a foreach to rebuild your result as you want? Or maybe (but it will be "ugly" : do a SELECT concat(...) and build a JSON format string to get your result and just do a json_decode after...but the foreach should be better ! — Mickaël Leger
– Mickaël Leger, Commented Oct 19, 2018 at 8:32

Madhur Bhaiya · Accepted Answer · 2018-10-19 08:34:25Z

2

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);

answered Oct 19, 2018 at 8:34

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

bi-dimensional array when joining tables in PHP MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related