PHP: mysql_fetch_array exclude duplicate entries

Question

I got a simple line of code, that fetch the sequel database and populate a category list, these category strings come by a product table, so many product row produce duplicate category entries.

$cat_result = $mysqli -> query("SELECT * FROM products");
while ($cat_row = mysqli_fetch_array($cat_result, MYSQL_NUM)) { 
    if($cat_row['4'] != NULL) { 
        print '<li><a href="/category/'. $cat_row['4'] .'">'. $cat_row['4'] .'</a></li>'; 
    }
}

The current output is like this:

Category1
Category1
Category2
Category2

So how I can suppress duplicate entry and got something like this?

Category1
Category2

Thanks for your help!

Hi @Fred-ii- , see you again! So I must to do "SELECT * FROM product GROUP BY category" where I must to use DISTINCT? — andreaem
– andreaem, Commented Feb 11, 2016 at 16:48
$cat_result = $mysqli -> query("SELECT DISTINCT(name) FROM products"); or $cat_result = $mysqli -> query("SELECT * FROM products GROUP BY name"); — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Feb 11, 2016 at 16:49
SELECT DISTINCT WhatEverTheColumnIsCalled FROM products specially as you only use one column, dont retrieve more than you use and you will find that it run quicker as well — RiggsFolly
– RiggsFolly, Commented Feb 11, 2016 at 16:49
Ciao Andrea! si, and SELECT DISTINCT col1, col2 FROM TABLE as per dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html - come stai? — Funk Forty Niner
– Funk Forty Niner, Commented Feb 11, 2016 at 16:49

Funk Forty Niner · Accepted Answer · 2016-02-11 16:54:28Z

4

As requested:

You can use either a DISTINCT in SELECT and/or a GROUP BY

I.e.:

SELECT DISTINCT col1, col2 FROM TABLE GROUP BY col_x

as per http://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html

which will avoid duplicates in your query.

Additionally, a WHERE clause could also be useful (later on).

I.e.:

SELECT DISTINCT col1, col2 FROM TABLE WHERE col_x = 'y' GROUP BY col_x

Reference:

http://dev.mysql.com/doc/refman/5.7/en/select.html

answered Feb 11, 2016 at 16:54

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

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

Comments

Death-is-the-real-truth · Accepted Answer · 2016-02-11 16:57:20Z

3

As suggested by everyone above DISTINCT and GROUP BY both can solve your problem, so you can do like below:-

Suppose the column name is category then:-

$cat_result = $mysqli -> query("SELECT DISTINCT(category) FROM products"); 

OR 

$cat_result = $mysqli -> query("SELECT * FROM products GROUP BY category");

Note:- you can choose n number of different column in DISTINCT too as suggested by others. Thanks.

edited Feb 11, 2016 at 16:57

answered Feb 11, 2016 at 16:52

Death-is-the-real-truth

72.3k10 gold badges59 silver badges105 bronze badges

Collectives™ on Stack Overflow

PHP: mysql_fetch_array exclude duplicate entries

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