PHP and SQL returning duplicate results

Question

I am trying create a order history page. An SQL statement that finds all the applications based on a order_ID in an orderdetails_tbl. Here is the SQL.

SELECT * FROM applications_tbl INNER JOIN orderdetails_tbl ON orderdetails_tbl.app_ID = applications_tbl.app_ID WHERE orderdetails_tbl.order_ID = $orderID

The results are displayed using this PHP.

$result = mysql_query("SELECT * 
FROM applications_tbl
INNER JOIN orderdetails_tbl ON orderdetails_tbl.app_ID = applications_tbl.app_ID WHERE orderdetails_tbl.order_ID = ".$order_ID."");
$row = mysql_fetch_assoc($result);
echo "<table>";
do { ?>

    <tr style="background-color:#fff">
    <td> <img src="getimage.php?ID=<?php echo $row['app_ID']; ?>" width="100" height="100" alt="IMAGE" /></td>
           <td width="20%"><?php echo $row['app_name']; ?></td>
           <td><?php echo $row['app_desc']; ?></td>
           <td width="15%"><?php echo $row['app_cost']; ?></td>
     </tr>    
     <?php  
 } while ($row = mysql_fetch_assoc($result));

The SQL returns the correct values and the PHP displays them correctly. My issue is that it on the webpage it returns many duplicate results. It appears to be random, but I noticed the more apps/items in the order the more duplicate results. In phpMyAdmin when I run the SQL it doesn't display duplicates.

Cheers for your time. Any advice will be greatly received!

orderdetails_tbl sounds like it holds the line items of the order. You should have another order table for the head of the order. Otherwise, you could just use SELECT DISTINCT. — mrks
– mrks, Commented Apr 7, 2013 at 11:18
Your should not use SELECT * on the table. Specify the fields you need and then also add a DISTINCT statement — Bjørne Malmanger
– Bjørne Malmanger, Commented Apr 7, 2013 at 11:18

Nikola Drosakis · Accepted Answer · 2013-04-07 11:20:44Z

1

Use GROUP BY

$result = mysql_query("SELECT * FROM applications_tbl
INNER JOIN orderdetails_tbl ON orderdetails_tbl.app_ID = applications_tbl.app_ID 
WHERE orderdetails_tbl.order_ID = ".$order_ID." GROUP BY app_ID");

answered Apr 7, 2013 at 11:20

Nikola Drosakis

2,3581 gold badge22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bestprogrammerintheworld · Accepted Answer · 2013-04-07 11:29:53Z

A tip is to use alias to get sql-statements more readable like this:

$result = mysql_query("SELECT * FROM applications_tbl at INNER JOIN orderdetails_tbl od ON od.app_ID = at.app_ID WHERE od.order_ID = ".$order_ID);

But to answer your question you could use DISTINCT on one field and list the fields you want to have included in your recordset or add a GROUP BY -statement at the end of the SQL-statement.

DISTINCT: (example)

$result = mysql_query("SELECT DISTINCT at.id, od.app_ID FROM applications_tbl at INNER JOIN orderdetails_tbl od ON od.app_ID = at.app_ID WHERE od.order_ID = ".$order_ID);

OR

GROUP BY (example)

$result = mysql_query("SELECT * FROM applications_tbl at INNER JOIN orderdetails_tbl od ON od.app_ID = at.app_ID WHERE od.order_ID = ".$order_ID . " GROUP BY at.id");

What is the actual value of $order_ID ? (I ask because you say you have the same query and you only get duplicates when viewing in browser. It seems very odd)

Collectives™ on Stack Overflow

PHP and SQL returning duplicate results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related