How to select unique arrays from MySQL table row?

Question

I have a table with 20 rows and one row have for example:

2,3,5,6,8,22
2,3,5,6,8,22,44,55
etc.

How can I select from mysql table rows only unique numbers, not duplicated so results are:

2,3,5,6,8,22,44,55

The table definition:

CREATE TABLE IF NOT EXISTS `test` (

  `id` int(11) NOT NULL auto_increment,

  `active` tinyint(1) NOT NULL default '1',

  `facilities` varchar(50) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `test` (`id`, `active`, `facilities`) VALUES

(1, 1, '1,3,5,6,7,8'),

(2, 1, '2,3,4,5,8,9'),

(3, 1, '4,5,6,7,9,10');

Here is my attempt:

SELECT DISTINCT facilities FROM test WHERE active='1'

$dbgeneral= explode(',', $row['facilities']);


$facilities = array(

"Air Conditioning" => "2",

"Balcony" => "4");

foreach ($facilities as $facilities=> $v) {

     if(in_array($v,$dbgeneral)) {

echo '';

}
}

could you show the table Definition? SSince althane's answer of distinct could be correct, but does each of your "rows" contain only 1 field? or are their multiple fields? — Doon
– Doon, Commented Aug 5, 2010 at 17:30
Here is my table: id 2 - 3 - 5 facilities 1,2,3,4,5,8,9 - 1,7,9,11,13 - 4,5,6,9,10 — Stipe
– Stipe, Commented Aug 5, 2010 at 17:53
I'm still not grasping this concept - could you post the DDL for the table? — Marco Ceppi
– Marco Ceppi, Commented Aug 5, 2010 at 17:55

Felix Kling · Accepted Answer · 2010-08-05 18:06:08Z

5

As this is only one field, you could do something like:

$result = mysql_query('SELECT facilities FROM table');

$facilities = array();

while(($row = mysql_fetch_array($result))) {
    $facilities = array_merge($facilities , explode(',', $row[0]));
}

$facilities = array_unique($facilities);

But you should consider to change your database design, it looks like your data is not normalized.

Reference: explode(), array_merge(), array_unique()

Depening on what kind of queries you want to do, a better table layout would be:

 | id  | facility |
 |  2  | 1        |
 |  2  | 2        |
 |  2  | 3        |
 ...
 |  3  | 1        |
 |  3  | 7        |
 |  3  | 9        |
 ...

and then you could just do:

SELECT DISTINCT facility FROM ...

edited Aug 5, 2010 at 18:06

answered Aug 5, 2010 at 17:59

Felix Kling

820k181 gold badges1.1k silver badges1.2k bronze badges

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

1 Comment

Stipe Over a year ago

Thank Felix, it works great! Now I can easily select for example FIND_IN_SET(2, facilities) OR FIND_IN_SET(6, facilities)

Collectives™ on Stack Overflow

How to select unique arrays from MySQL table row?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related