Duplicate results from mysql

Question

I'm new to php, but I have already made some query's for my webshop. I have a little problem now with making the invoice. I'm trying put the invoice data from the database in a table in my website, but it duplicates itself.

Product_id | Product name | amount | price | subtotal|
2            kippensoep     1         €3      €3
2            kippensoep     1         €3      €3
2            kippensoep     1         €3      €3
4            Loempia's      1        €10     €10
4            Loempia's      1        €10     €10
4            Loempia's      1        €10     €10
4            Loempia's      1        €10     €10

So as you can see, the product_id , product_names are duplicated. It's strange because in the database it isn't duplicated. Here's the php code.

<?php
  $maxfac1 = mysql_fetch_array(mysql_query("SELECT MAX(transactie.factuur_id) FROM transactie , account WHERE transactie.id = $session_user_id"));

  unset ($maxfac1[0]); 
  $maxfactuur_id = implode (" ",$maxfac1);

  $productinfo = mysql_query("SELECT * FROM producten, transactie, factuur WHERE producten.product_id = factuur.product_id AND factuur.factuur_id = $maxfactuur_id AND factuur.gebruikers_id= $session_user_id");

  $totaal=0;
  $subtotaal=0;

  while ($row = mysql_fetch_array($productinfo))
  {
  ?>
  <tr>
    <td><?php echo $row['product_id']?></td>
    <td><?php echo $row['name'] ?></td>
    <td><?php echo $row['amount'] ?></td>
    <td>€<?php echo $row['price'] ?></td>
    <?php $subtotaal = ($row['price']*$row['amount']);?>
    <td>€<?php echo "$subtotaal" ;$totaal+= $subtotaal;?></td>
  </tr>

  <?php
  }
  ?>
?>

I hope you guys can help me and find a solution.

EDIT session_user_id checks if the user is logged in. Then it returns the id from the user who is logged in.

I strongly recommend to use PDO (prepared statements) when writing your queries for these (prepared queries) are secure. You got plenty of variables within your code and I am sure soon you will use Customer/User inputs to filter data due your queries. So its always a good decision (especially for beginners) to learn techniques that are recommended these days and help you keeping your DB-tansactions more save — serjoscha, Feb 10 '15 at 14:10
build the proper query of your productinfo, then query it in your mysql to track it properly and check it. — Oli Soproni B., Feb 10 '15 at 14:11
@serjoscha `$maxfactuur_id` is purely a result from an SQL query, so it dose not need to be escaped. So it really matters how `$session_user_id` was received. Also note `mysql` is deprecated, `mysqli` should be used. — Spencer Wieczorek, Feb 10 '15 at 14:15
@jorphp It would help if you posted the schema of your db with some content. — Einar Sundgren, Feb 10 '15 at 14:16
Try to use `mysql_fetch_assoc()`. Also try to execute your mysql-query at mysql console for checking results. — Oleg, Feb 10 '15 at 14:20
If i make a new variable $pr, and do mysql_numrows($productinfo). it gives the value 8, while it must give the value 2. so the problem isn't in the while loop, but in the query.... — jorphp, Feb 10 '15 at 14:31
Ok. Now try to check rows are unique. Add `GROUP BY producten.product_id` to your query — Oleg, Feb 10 '15 at 14:35
aah man, you are the best Oleg!! That was indeed the solution. Thank you very, very much! — jorphp, Feb 10 '15 at 14:50

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

2

You are missing a link with the transactie table. So you have a Cartesian join appearing.

Add the proper where condition to fix that.

As you didn't provide any table structure, I can't help you more than that!

edit

on an other level, do not use mysql extension since it is deprecated!

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 10 '15 at 14:13

A.D.

1,160
1
8
23

He can add `GROUP BY producten.product_id` for it – Oleg Feb 10 '15 at 14:40
or remove the _transactie_ table as it is not used. – A.D. Feb 10 '15 at 14:48

score 1 · Answer 2 · answered Feb 10 '15 at 14:08

1

Try to add the DISTINCT keyword.

$productinfo = mysql_query("SELECT DISTINCT * FROM producten, transactie, factuur WHERE producten.product_id = factuur.product_id AND factuur.factuur_id = $maxfactuur_id AND factuur.gebruikers_id= $session_user_id");

answered Feb 10 '15 at 14:08

Einar Sundgren

4,325
9
40
59

we also tried this, but it still doesn't work. We don't have any double columns in our database. – jorphp Feb 10 '15 at 14:12
@jorphp `DISTINCT` gets rid of duplicates from the query. As such I don't see how duplicates could of still be there in this code. Is the code in question all your code making this table? – Spencer Wieczorek Feb 10 '15 at 14:20
yes it's !The table is making because of the while loop. – jorphp Feb 10 '15 at 14:28
I mean: Yes it is. * ;) – jorphp Feb 10 '15 at 14:34

score 1 · Accepted Answer · answered Feb 10 '15 at 18:44

1

Try to check rows are unique. And add GROUP BY producten.product_id to your query.

answered Feb 10 '15 at 18:44

Oleg

568
1
5
18

Duplicate results from mysql

3 Answers3

edit