CODEWITHSUNDEEP
phpmysqlarrays
scrnjakovic
scrnjakovic

Reputation: 111

Getting an array as a field from result row

I'm pretty new to databases, so my question is:

Let's say that we have two tables: Users and Orders

Is it possible (and how do I do it) to list all users with their data (First & Last name, birthdate etc) and associated orders in one query? So the result should look something like this:

|------------------------------|
| John Doe  |  Order 1 details |
|           |  Order 2 details |
|           |  Order 3 details |
|------------------------------|
| Janny Hoe |  Order x details |
|           |  Order y details |
|------------------------------|

So you got the point? Each row from table1 has a field which contains multiple rows from table2? I've heard MySQL doesn't return multidimensional arrays, so how does it return something like this?

Any help would be greatly appreciated!

EDIT:

Table structure could be something like this:

TABLE users:
id | name

TABLE orders:
id | user_id | date_ordered | other unrelevant data...

I know this is doable with joins, but what bugs me is what will it return? Will result have field orders which would be array containing orders row data like $result[0]['orders'][0]['date_ordered'] or something like that ?

Upvotes: 0

Views: 94

Answers (1)

user399666
user399666

Reputation: 19909

Do a LEFT join (if there is a possibility that a user does not have any orders) and then gather the results in an array, based on the user ID:

SELECT Users.id, Users.name, Order.detail
FROM Users
LEFT JOIN Order
ON Users.id= Order.user;

Ex:

$userOrders = array();

$sql = 'SELECT Users.id, Users.name, Order.detail
        FROM Users
        LEFT JOIN Order
        ON Users.id= Order.user';

foreach ($pdo->query($sql) as $row) {
    $userOrders[$row['user_id']][] = $row;
}

Upvotes: 2

Related Questions