JOIN problem

[eluser]wowdezign[/eluser]
I am not sure I am following then. Am I totally lost or is something like this what he's looking for?


Sample Orders Table:

Code:

CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) NOT NULL,
  `order_date` date NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

INSERT INTO `orders` (`order_id`, `customer_id`, `order_date`) VALUES
(1, 2, '2010-01-19'),
(2, 1, '2010-01-26'),
(3, 3, '2010-01-22'),
(4, 13, '2010-01-22'),
(5, 2, '2010-01-28');

Sample Orders Items Table:

Code:

CREATE TABLE IF NOT EXISTS `orders_items` (
  `product_id` bigint(20) DEFAULT NULL,
  `order_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `orders_items` (`product_id`, `order_id`) VALUES
(1, 1),
(2, 1),
(4, 1),
(3, 1),
(1, 2),
(4, 2),
(35, 5),
(136, 5);

Then when I run this:

Code:

SELECT *
  FROM (`orders_items`)
  LEFT JOIN `orders` ON `orders_items`.`order_id` = `orders`.`order_id`
  WHERE `orders`.`customer_id` = '2'

I get a result set of all the items bought by customer number 2 and they are grouped by the order id.

I thought that was the desired result.

This is what LEFT JOINS are great for IMO.

Sorry, if I misunderstood. I was just trying to help.

[eluser]Sean Gates[/eluser]
No worries!

I believe what he's asking for is to have multiple columns returned for each order. Here again is what he posted:

Code:

Array
(
    [0] => Array
        (
            [id] => 84
            [order_date] => 2010-01-04 15:07:00
            [order_id] => 84
            [product_id] => 1
            [product_id] => 3
        )

    [1] => Array
        (
            [id] => 85
            [order_date] => 2010-01-07 13:24:02
            [order_id] => 85
            [product_id] => 1
        )

)

Basically, you can't have product_id show up as two columns in a returned result. Only one. So, at some point (easily in PHP) you need to rearrange the array. In @Dyllon's example he does just that - rearranges things to have multiple product IDs be associated with a single order.

@underzen, are we answering your question correctly? Please respond. Thanks!

[eluser]underzen[/eluser]
[quote author="Dyllon" date="1262947180"]Just foreach through the array to group your orders.

Code:

$query_result = array(
          array(
             'id' => 84,
             'order_date' => '2010-01-04 15:07:00',
             'order_id' => 84,
             'product_id' => 1
            ),
          array(
             'id' => 84,
             'order_date' => '2010-01-04 15:07:00',
             'order_id' => 84,
             'product_id' => 3
            ),
          array(
             'id' => 85,
             'order_date' => '2010-01-07 13:24:02',
             'order_id' => 85,
             'product_id' => 1
            )
        );

$orders = array();
foreach($query_result as $order)
{
    $orders[$order['id']][] = $order;
}
print_r($orders);

gives this result

Code:

Array
(
    [84] => Array
        (
            [0] => Array
                (
                    [id] => 84
                    [order_date] => 2010-01-04 15:07:00
                    [order_id] => 84
                    [product_id] => 1
                )

            [1] => Array
                (
                    [id] => 84
                    [order_date] => 2010-01-04 15:07:00
                    [order_id] => 84
                    [product_id] => 3
                )

        )

    [85] => Array
        (
            [0] => Array
                (
                    [id] => 85
                    [order_date] => 2010-01-07 13:24:02
                    [order_id] => 85
                    [product_id] => 1
                )

        )

)

[/quote]



Dyllon,

I can't believe it was that easy and that I couldn't figure it out myself...Work's perfectly.

Sorry for taking a long time to respond but I've been out of town. Thank you to everyone, you all are life savers!!!!!!

KUDOS!!!!

[eluser]Sean Gates[/eluser]
@underzen,

Glad you got what you needed.

-- Sean