Merge/Remove duplicate MySQL query results

Hi I'm running a query that retrieves results from multiple tables.

My query retrieves orders from one table called "store_orders_items" and the corresponding customers details from another ca开发者_运维百科lled "store_orders".

However if there are two orders by one customer the query retrieves the customers details twice as the query selects the orders by the customers id number.

So I get the detail of two orders which is fine but also the customers details twice which I don't want.

How can I adjust my query to retrieve all orders and only on customers details per order?

Here's the query so far:

"SELECT   
                                store_orders.order_name, 
                                store_orders.order_address, 
                                store_orders.id, 
                                store_orders.order_town, 
                                store_orders.order_county, 
                                store_orders.order_postcode, 
                                store_orders.order_country, 
                                store_orders.order_email, 
                                store_orders.item_total,                                    
                                product.name, 
                                store_orders_items.sel_item_size, 
                                store_orders_items.sel_item_color,
                                store_orders_items.sel_item_id, 
                                store_orders.order_date, 
                                product.price 
                        FROM 
                                store_orders_items 
                        LEFT JOIN 
                                store_orders 
                        ON 
                                store_orders_items.order_id = store_orders.id 
                        LEFT JOIN 
                                product 
                        ON 
                                store_orders_items.sel_item_id = product.id 
                        WHERE 
                                store_orders_items.order_id=$id "

Here's a basic example of what I'm using to output/echo the code:

while ($order = mysql_fetch_array($store_orders)){
                $name = $order["name"] ;
                $size = $order["sel_item_size"];
                $color = $order["sel_item_color"];
                $order_name = $order["order_name"];
                $address = $order["order_address"] ;
                $town = $order["order_town"] ;
                $county = $order["order_county"] ;
                $postcode = $order["order_postcode"] ;
                $country = $order["order_country"] ;
                $email = $order["order_email"] ;
                $price = $order["price"];
                $date = $order["order_date"];

                $item = "<li>{$name}</li>" ;
                $item .= "<li>{$color}</li>" ;
                $item .= "<li>{$size}</li>" ;
                $item .= "<li>{$price}</li>" ;
                $item .= "<li>{$date}</li>" ;
                $item .= "<li>{$order_name}</li>" ;
                $item .= "<li>{$address}</li>" ;
                $item .= "<li>{$town}</li>" ;
                $item .= "<li>{$county}</li>" ;
                $item .= "<li>{$postcode}</li>" ;
                $item .= "<li>{$country}</li>" ;
                $item .= "<li>{$email}</li>" ;

                echo $item ;
            }

you should use group by like this: group by customer_id

What you want to do is not possible. The SQL JOIN operation joins the rows of two tables together based on some criteria, in your case the order_id. The resulting row will contain the data of the attributes you specify in the first part of the SELECT statement.

The real question is what the problem is with retrieving this data twice. You don't have to use all the data you retrieve. You can ignore it if you already processed the data of a customer.

Another option, which I do not recommend, is to retrieve all customer data with one query, and then retrieve the order items with other queries.

EDIT: This code should work, based on the code example you provided:

// Output order details
$order = mysql_fetch_array($store_orders);

$order_name = $order["order_name"];
$address = $order["order_address"] ;
$town = $order["order_town"] ;
$county = $order["order_county"] ;
$postcode = $order["order_postcode"] ;
$country = $order["order_country"] ;
$email = $order["order_email"] ;
$price = $order["price"];
$date = $order["order_date"];

$orderList = "<ul>";
$orderList .= "<li>{$price}</li>" ;
$orderList .= "<li>{$date}</li>" ;
$orderList .= "<li>{$order_name}</li>" ;
$orderList .= "<li>{$address}</li>" ;
$orderList .= "<li>{$town}</li>" ;
$orderList .= "<li>{$county}</li>" ;
$orderList .= "<li>{$postcode}</li>" ;
$orderList .= "<li>{$country}</li>" ;
$orderList .= "<li>{$email}</li>" ;
$orderList .= "</ul>";

echo $orderList;

// Reset result pointer
mysql_data_seek($store_orders, 0);

// Output Items
echo "<ul>";
while ($order = mysql_fetch_array($store_orders)){
    $name = $order["name"] ;
    $size = $order["sel_item_size"];
    $color = $order["sel_item_color"];

    $item = "<li>{$name}</li>" ;
    $item .= "<li>{$color}</li>" ;
    $item .= "<li>{$size}</li>" ;


    echo $item ;
}
echo "</ul>";

The first part of this code retrieves the first row of the mysql result set. From this row the customer data is shown in an html unordered list. Then the code resets the mysql result set by calling the mysql_data_seek() function. Then it loops over all rows in the result set, and outputs all items in another html unordered list. You should probably think of another way to display the results, but that change is trivial.

Futhermore, this code assumes that there is at least one row in the $store_orders variable. If this is not always the case, you should check that with the mysql_num_rows() function before executing this code.