Unique/Distinct values from a PHP/MYSQL query

Question

I have a database with the fields type1, type2, type3, type4 all the way up to type19, along with other fields. What I am trying to do is to get all the fields from those rows and then only echo the fields which are not duplicates.

I have tried using select distinct type1, type2 etc from products but do not know the php code to put all the fields ($_row['type1'] $_row['type2'] etc) into a single variable and then echo all the distinct values from that variable. Does anyone have any suggestions?

Edgar Velasquez Lim · Accepted Answer · 2011-07-20 04:39:35Z

4

Loop through your results and add them to an array. Then use array_unique() to return only unique values.

http://php.net/manual/en/function.array-unique.php

You should definitely rethink your database design if possible however, since this is a pretty bad way to do things.

answered Jul 20, 2011 at 4:39

Edgar Velasquez Lim

2,44418 silver badges15 bronze badges

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

3 Comments

Ben Over a year ago

Agreed, a bad way to do things. However, your answer is what the OP asked :/. This way is really heavy on the server, since you've got to return all your results, and then process every single one, server side...yikes.

Mathew Over a year ago

Yeah the answer worked, I was just wondering what the best way to do this would be since everyone is saying that the database design is bad

Ben Over a year ago

Didn't mean to sound hostile - actually your database design may be fine. What I meant was that easy data manipulating (like sorting or finding unique values) is usually better off happening in SQL. But keep up that spirit of curiousity :)

Ben · Accepted Answer · 2011-07-20 04:40:33Z

3

If you wanted to use an SQL query only, you can say

SELECT DISTINCT type1 FROM products ORDER BY type1

An alternative is

SELECT type1, max(1) FROM products GROUP BY type1

The downside is that you have to do 19 queries if you want to get distinct values for all of your columns.

The upside is that if you want distinct values for one column, it's a lot easier.

You could batch the 19 queries into a for loop, perhaps:

for($i=1;$i<20;$i++) {
  $sql = "SELECT DISTINCT type".$i." FROM products ORDER BY type1";
  // Run the sql query to get the data, then manipulate it as you wish.
}

edited Jul 20, 2011 at 4:40

answered Jul 20, 2011 at 4:35

Ben

57.6k50 gold badges187 silver badges229 bronze badges

Comments

AndrewR · Accepted Answer · 2011-07-20 04:40:25Z

0

Use UNION to join the results of 19 queries as a subquery.

SELECT DISTINCT a FROM (
  SELECT DISTINCT type1 AS a FROM products
  UNION
  SELECT DISTINCT type2 AS a FROM products
  UNION 
  ...
  SELECT DISTINCT type19 AS a FROM products
) ORDER BY a

answered Jul 20, 2011 at 4:40

AndrewR

6,7781 gold badge26 silver badges38 bronze badges

Collectives™ on Stack Overflow

Unique/Distinct values from a PHP/MYSQL query

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related