Remove duplicate MySQL result of query?

Question

I am using SELECT COUNT to check if my mysql table has any duplicates, and when it does, I want it to remove the duplicate.

Here is a screenshot of how the query result looks like: http://s.prelicio.us/lDyRV.png

Now there are 2 'Aanpassingen voor website Chat-Garden' in my table. That is correct, so I've made it count and where 'Aantal' stands, that's where it'll tell me how many rows have that 'Aanpassingen voor website Chat-Garden'.

But now I want it to remove the duplicate row in my screenshot, since the '2' already has been shown at 'Aantal'. I then do not need the extra row in my screenshot. (excuse me for my bad english)

This is de current code:

<?php 

$products = mysql_query('SELECT * FROM `preo_invoices-products`');
if(!$products || mysql_num_rows($products) == '0') echo '<li>Er zijn geen producten voor dit factuur.</li>'; 

else $i == '0'; while($product = mysql_fetch_object($products)) {

$color = ($i % 2 == 0) ? ' class="grey"' : ''; $i++;


$result = mysql_query('SELECT *, COUNT(`id`) FROM `preo_invoices-products` WHERE `invoiceid` = "'.$paid->id.'" GROUP BY `description`');
while($row = mysql_fetch_array($result)) { 

?>

<li<?php echo $color; ?>>

<span class="inv9"><?php echo $row['COUNT(`id`)']; ?></span> // This one is the 'Aantal' where it shows 2
<span class="inv3">

<?php 
$services = mysql_query('SELECT * FROM `preo_services` WHERE `id` = "'.$product->serviceid.'" LIMIT 1');
while($service = mysql_fetch_object($services)) echo strtoupper($service->name); 
?>

</span>
<span class="inv14"><?php echo $product->description; ?></span>
<span class="inv9"><?php echo $product->tax; ?>%</span>
<span class="inv12">&euro;<?php echo $product->sum; ?>,-</span>
<span class="inv15">&euro;<?php echo $product->sum*$row['COUNT(`id`)']; } ?>,-</span>

</li>

<?php } ?>

It now actually has to remove the duplicate row, since the 'Aantal' shows me I have a duplicate one. But when I have 3 same rows, it'll show '3' at Aantal, and still has to show 1 row at my screenshot.

Any ideas?

John Woo · Accepted Answer · 2012-12-13 00:47:05Z

7

you can add DISTINCT in your query, example

SELECT DISTINCT * FROM tableName

answered Dec 13, 2012 at 0:47

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

11 Comments

stUrb Over a year ago

And then use PDOStatement::rowCount or mysql_num_rows() to display a number.

Chloë E. Over a year ago

Do you have an example? Because the way I just did it, did not seem to work. I have changed the query to SELECT DISTINCT * FROM preo_invoices-products And added a mysql_num_rows echo where the 'Aantal' is, but now it shows me this: s.prelicio.us/G21ff.png $result = mysql_query('SELECT DISTINCT * FROM preo_invoices-products WHERE invoiceid = "'.$paid->id.'"'); while($row = mysql_fetch_array($result)) { ?> <li<?php echo $color; ?>> <span class="inv9"> <?php echo mysql_num_rows($result); ?> // Aantal

John Woo Over a year ago

much better if you will provide sample data in your example so i can show you how :D

Chloë E. Over a year ago

This is the table: s.prelicio.us/VgKoF.png This is the PHP: s.prelicio.us/XaJ2F.png This is the current output: s.prelicio.us/Qq8fC.png This is how it should look like: s.prelicio.us/S6bMZ.png (if there are 2 same values at 'description' in the table, now if there are 3, then show '3' at 'Aantal'. Aantal means 'amount'.

Ian Atkin Over a year ago

Don't forget to use GROUP BY or DISTINCT won't do anything.

|

Collectives™ on Stack Overflow

Remove duplicate MySQL result of query?

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related