CODEWITHSUNDEEP
phpmysql
user1630140
user1630140

Reputation: 27

Duplication of results from MYSQL query

Hi I have a a query that produces what I need, however I am getting duplicate rows on the output and cannot figure out why, every line is appearing twice. Any ideas?

$query = "SELECT * FROM orders LEFT JOIN users ON orders.USER_ID = users.USER_ID
          LEFT JOIN items ON items.CHECKOUT_ID = orders.CHECKOUT_ID ORDER BY
           date_order DESC LIMIT 0,1000";



            $result = mysqli_query($con, $query);

            while($row = mysqli_fetch_array($result, MYSQL_ASSOC)){
                $order_id = $row["ORDER_ID"];
                $date_order = $row["date_order"];
                $date_req = $row["delivery"];
                $country = $row["country"];
                $firstname = $row["name"];
                $lastname = $row["surname"];
                $email = $row["email"];
                $size = $row["size"];
                $total_cost = $row["total_cost"];


                echo '<tr><td>' . $order_id . ' </td>' .
                     '<td>' . $date_order . '</td>' . 
                     '<td>' . $date_req . '</td>' . 
                     '<td>' . $country . ' </td>' . 
                     '<td>' . $firstname . ' </td>' .
                     '<td>' . $lastname . ' </td>' .
                     '<td>' . $email . ' </td>' .
                     '<td>' . $size . ' </td>' .
                     '<td>&euro;' . number_format($total_cost, 2, '.', '') . ' </td>' .
                     '<td style="text-align:right"><a href="xxxxxxx_Order_Details_Admin.php?id=' . $order_id . '">More Details</td>' .
                     '<td style="text-align:right"><a href="xxxxxxxx_Order_Details_print.php?id=' . $order_id . '">Print</td>' .

                    '</tr>';

Upvotes: 0

Views: 87

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Your query should be returning one row for every item in the row. Is this what you are expecting? If you are expecting one row per user or one row per order, then you have the wrong query.

From the fields that you are pulling out, I don't think you want the items. Try this query instead:

SELECT *
FROM orders LEFT JOIN
     user
    ON orders.USER_ID = users.USER_ID
ORDER BY date_order DESC LIMIT 0,1000

If you are still getting duplicates (or duplicate rows when you are expecting one row per item), then you need to look into the underlying tables to see where the duplicates are coming from.

Upvotes: 1

dnagirl
dnagirl

Reputation: 20456

Change your query to: 

SELECT DISTINCT o.ORDER _ID, o.date_order, ...
FROM orders o
  LEFT JOIN users u
    ON o.USER_ID = u.USER_ID 
  LEFT JOIN items i
    ON i.CHECKOUT_ID = o.CHECKOUT_ID 
ORDER BY date_order DESC LIMIT 0,1000

Oh, and always specify which columns you want to return. Using * makes it much more difficult to debug.

Upvotes: 0

Related Questions