CODEWITHSUNDEEP
phpmysqlsql
Björn C
Björn C

Reputation: 4008

Cannot `Select DISTINCT`

I´m trying to Select DISTINCT column in my mysql.

PHP

$query = "SELECT DISTINCT column1 as value, id FROM table WHERE column1 LIKE '%".$term."%'";;

$term = trim(strip_tags($_GET['term']));

while ($row = $stmt->fetch()){
    $row['value']=htmlentities(stripslashes($row['value']));
    $row['id']=(int)$row['id'];
    $row_set[] = $row;
}

The result will be the same as whithout using DISTINCT.
Why?

Upvotes: 1

Views: 220

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

Probably id is unique (remove it to get DISTINCT column1 values):

$query = "SELECT DISTINCT column1 as `value` FROM table WHERE column1 LIKE '%".$term."%'";;

For example:

id column1
1 'a'
2 'a'
3 'b'
4 'c'

And you want:

column1
'a'
'b'
'c'

EDIT:

If you need id then your query works perfectly as expected. You can also try GROUP_CONCAT to get list of corresponding id.

SELECT column1 AS `value`, GROUP_CONCAT(id) AS ids
FROM table 
WHERE column1 LIKE ...
GROUP BY column1

Output:

column1 ids
'a'     1,2
'b'     3
'c'     4

Upvotes: 1

Related Questions