PHP/MySQL Duplicate values from select

Question

I am running this query

$sql6 = "SELECT RECIPE.Price, RECIPE.Name FROM ORDERRECI, ORDERS, RECIPE WHERE ORDERRECI.OrderID = $orderID AND ORDERRECI.RecipeID = RECIPE.RecipeID";        
$results = mysqli_query($con, $sql6);
while ($row=mysqli_fetch_assoc($results))
{
    $calcC+= $row['Price'];
    echo $calcC.$row['Name']."<br />";
}
return $calcC;

The query Runs fine I'm getting the right values But I'm getting them 9 times.( I checked via the echo). I checked the Database they are only in there once. I tried using Distinct but because the customer can pick the same side multiple times they would get an inaccurate result (tested. Can anyone explain why. Would using join help. My teacher favors where(Don't know why) but that's why I use it
EDIT: I Recipe and Orders have a many-to-many relationship ORDERRECI is the referential table. I am trying to calculate the Total Cost of an order. I just tried inner join but it still duplicated and this time it duplicated 14 times

Balázs Édes · Accepted Answer · 2014-05-11 19:58:01Z

1

FROM ORDERRECI, ORDERS, RECIPE here you are doing a Cartesian product with the 3 tables, which means, each row from each table will be paired with each row from every other table from the FROM list. I don't know what was your goal with the query, but that is whats happening.

answered May 11, 2014 at 19:58

Balázs Édes

13.9k7 gold badges60 silver badges105 bronze badges

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

1 Comment

ProgramKitkat Over a year ago

Its the table created when you have a many to many relation. I'm trying to get the Prices for the recipes corresponding to a specific order. But thanks for the mathematical explanation.

Machavity · Accepted Answer · 2014-05-11 19:56:18Z

0

Try adding this to your query

GROUP BY RECIPE.Name

answered May 11, 2014 at 19:56

Machavity♦

31.8k27 gold badges97 silver badges108 bronze badges

1 Comment

ProgramKitkat Over a year ago

I don't have to have the name it was just for error checking so I could make sure it was getting the right things.

Itay Gal · Accepted Answer · 2014-05-11 19:56:21Z

0

For each row in ORDERRECI you create a merged row with each one of the rows in ORDERS and the same goes for RECIPE.

You should use LEFT JOIN. Read about SQL join types here

answered May 11, 2014 at 19:56

Itay Gal

10.8k6 gold badges43 silver badges78 bronze badges

Comments

ProgramKitkat · Accepted Answer · 2014-05-12 08:18:34Z

0

I ended up with this statement. I Needed the general join of the tables. then use where to narrow it down. Thanks for the help in figuring it out
$sql = "SELECT RECIPE.Price FROM ORDERRECI INNER JOIN ORDERS ON ORDERRECI.OrderID = ORDERS.OrderID INNER JOIN RECIPE ON ORDERRECI.RecipeID = RECIPE.RecipeID WHERE ORDERS.OrderID = $orderID";

answered May 12, 2014 at 8:18

ProgramKitkat

3951 gold badge4 silver badges9 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL Duplicate values from select

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related