Multidimensional Array base on a single Array using ID's

[Tanker]

Hello everyone, I'm new to the forum, hopping to find some help.
I'm trying to build a multidimensional array base on a single array... please let me explain.
I have a very basic query as follow.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select('o.*, u.*, i.*')
    ->from($db->quoteName('#__redshop_orders', 'o'))
    ->join('LEFT', $db->quoteName('#__redshop_users_info', 'u').' ON (' . $db->quoteName('u.users_info_id') .' = '. $db->quoteName('o.user_info_id').')')
    ->join('INNER', $db->quoteName('#__redshop_order_item', 'i') . ' ON (' . $db->quoteName('i.order_id') . ' = ' . $db->quoteName('o.order_id') . ')')
     ->where($db->quoteName('o.order_number') . ' LIKE '. $db->quote($o_id) .'');
$db->setQuery($query);
$results = $db->loadObjectList();

that will output a single $key per order, say the user has 3 orders it will display

array (
[0] => array ( 'id 1', 'data42')
[1] => array ( 'id 1', 'data26')
[2] => array ( 'id 1', 'data27')
)

the idea is to make a single multidimensional array as follow

array (
[id1] => array (
                      [user 1] => array ( 
                                                 [order 1] => ( array => [item 1] => 2, [item 2] => 3 )
                                              )
                     )
)

in other words...

[user 1]
        [order id 1]
                    [items 1]
                    [items 2]
                    [items 3]
        [order id 2]
                    [items 15]
                    [items 25]
                    [items 33]
[user 2]
        [order id 46]
                    [items 1]
                    [items 2]
                    [items 3]
        [order id 25]
                    [items 15]
                    [items 25]
                    [items 33]

that type of array is what I'm looking for, for I'm having a hard time to build that array...
Now, that query display all the information that I want just not the way I need

I also made 3 other queries 1 for the user, 1 for the orders and 1 for the items, but I don't see how to put it together...

the ID's that can be use to relate one another are

order_id which is in the users table user_id which is in the orders table and item_id which is in the orders table...

I was using this foreach to put them together but is not working....

$user = array();
$order = array();
$items = array();
foreach ($results as $user) {
    $id = $user->order_id;
    $user->orders = array();
    $order[$id] = $user;

if ($user->order_id == 0) {
    $user[$id] = & $orders[$id];
}

foreach ($order as $id => & $node) {
    if (isset($order[$node->order_id])) {
        $order[$node->order_id]->children[$id] = $node;
    }
}
}

print_r($user);

but is not working...

and here is the array that I need to work with as an example...

Array in next post ...

so thats what my query outputs....

Thank you for taking the time...

[johanafm]

Why use all those select, from, join etc functions over one single string representing the query, which is much more readable. At least in my opinion. Coupled with prepared statements using named placeholders, even more so. For example

$stmt = $pdo_instance->prepare("
SELECT o.*, u.*, i.*
FROM #__redshop_orders o,
LEFT JOIN #__redshop_users_info u ON u.users_info_id = o.user_info_id
INNER JOIN #__redshop_order_item ON i.order_id = o.order_id
WHERE o.order_number LIKE :order_id");

Never select more fields than you will use. Always explicitly specify the fields you select, even when you select all fields of all tables. Otherwise you do not know what fields are available when reading the php code and you will need to look at the db schema too when you next return to the code for maintenenace, future development, bug fixes… And selecting more fields than you need is a waste of resources.

SELECT o.col_1, o.col_2, u.col_a, u.col_b, etc

Are you intentionally allowing pattern matching against ids in the where clause? Are the ids actually strings, rather than unsigned ints - and if so, why? You should most likely use = over like in this case.
- Index on an unsigned int column is (will most likely be) shorter than an string index, and is therefor preferable.
- If the order_number column is NOT a string, your query may actually cast all order_number values of that table to strings before matching against $o_id.
- Even if it is a string, you most likely do not want to allow user input of '%' which would then match all orders

WHERE o.order_number LIKE '%'

WHERE o.order_number = :order_id

If you select all items for one order, you get all items for that order.

WHERE u.user_number = :user_id

If you select all items for all orders of one user, you get all items for all orders of that user… etc

This is what you will end up with once you select more than one order (change the where clause) and make sure the rows are properly ordered (add order by user.id, order.id)

[user 1][order id 1][items 1]
[user 1][order id 1][items 3]
[user 1][order id 1][items 2]
[user 1][order id 3][items 15]
[user 2][order id 2][items 25]
[user 2][order id 2][items 5]

[Tanker]

Thank you, yes the ID's are not all INT... FO44, GO222, 5555...etc your query is clean and easy to read, thank you, I made a few modifications to mine following yours...

But the result in my case still the same, please let me paste my array:

Array
(
    [0] => stdClass Object
        (
            [order_id] => 3
            [user_id] => 374
            [order_number] => 3
            [barcode] =>
            [user_info_id] => 1
            [order_total] => 594.00
            [order_subtotal] => 394.00000
            [order_tax] => 0.00
            [order_tax_details] =>
            [order_shipping] => 200.00
            [order_shipping_tax] => 0.00
            [coupon_discount] => 0.00
            [order_discount] => 0.00
            [special_discount_amount] => 0.00
            [tax_after_discount] => 0.000
            [recuuring_subcription_id] =>
            [users_info_id] => 1
            [product_id] => 15
            [order_item_sku] => 139-1310-1
            [order_item_name] => Soe other names items 3

    )

[1] => stdClass Object
    (
        [order_id] => 3
        [user_id] => 374
        [order_number] => 3
        [barcode] =>
        [user_info_id] => 1
        [order_total] => 594.00
        [order_subtotal] => 394.00000
        [order_tax] => 0.00
        [order_tax_details] =>
        [order_shipping] => 200.00
        [referral_code] =>
        [customer_message] =>
        [shop_id] =>
        [order_discount_vat] => 0.000
        [track_no] =>
        [payment_oprand] => -
        [discount_type] =>
        [requesting_tax_exempt] => 0
        [accept_terms_conditions] => 0
        [order_item_id] => 4
        [supplier_id] => 0
        [product_id] => 6
        [order_item_sku] => 139-2011-DS-1
        [order_item_name] => Name 3 for prod
        [product_quantity] => 3
        [product_item_price] => 80.0000
        [product_item_price_excl_vat] => 80.0000
    )

[2] => stdClass Object
    (
        [order_id] => 3
        [user_id] => 374
        [order_number] => 3
        [barcode] =>
        [user_info_id] => 1
        [order_total] => 594.00
        [is_booked] => 0
        [order_label_create] => 0
        [vm_order_number] =>
        [requisition_number] =>
        [bookinvoice_number] => 0
        [bookinvoice_date] => 0
        [is_company] => 0
        [ean_number] =>
        [braintree_vault_number] =>
        [veis_vat_number] =>
        [veis_status] =>
        [company_name] =>
        [requesting_tax_exempt] => 0
        [accept_terms_conditions] => 0
        [order_item_id] => 5
        [supplier_id] => 0
        [product_id] => 49
        [order_item_sku] => 150-1017-0
        [order_item_name] => Poducs names 1
        [product_quantity] => 2
    )

)

In this array, the order id is 3, and this order has 3 items, as you can see it repeats the User information as well the order information, just the items in the order change...

I'm aware that there is information from the DB that I don't need, but for now, lets just leave it there... what I actually need is, how do I put it together...

as:

[User id]
    [user id]
    [user name]
    [address]
    [oder id]
         [oder id]
         [order cost]
         [order date]
         [order stats]
         [items]
                [item id1]
                          [items information]
                [item id2]
                          [items information]
                [tem id3]
                          [items information]

I was using a foreach to loop trough the results and build the final array from there, but is not working, all I get is the order id, user id and the items only the key with no value at all...

Thank you.

[johanafm]

Tanker;11038863 wrote:

I was using a foreach to loop trough the results and build the final array from there, but is not working, all I get is the order id, user id and the items only the key with no value at all...

If I understand you correctly, all you need to do is group all such items in an array using the order id as key. Or whatever else you want to group items by

# group items by order
$by_order_id = [];
foreach ($all as $i) {
    # Simply move each item into an array using order id as a key to the array
    # $i is all info for an ordered item
    # $i['order_id'] is its order id.
    $by_order_id[$i['order_id']][] = $i;
}

# In a similar fashion you could group orders by users and also items by order (and by extension by users)
$user_order = [];
foreach ($all as $i) {
    $user_order[$i['user_id']][$i['order_id']][] = $i;
}