PHP prevent duplicates in while loop

Question

I'm populating a select field from database. However, many rows have the same value in the same column. How would I prevent duplicate values in a select field?

At the moment, I have this

$q = "SELECT * FROM table WHERE UserID = ?";
$smt = $db->prepare($q);
$smt->execute(array($userid));
while($row = $smt->fetchObject()){
     echo '<option value="'. $row->Column. '">'.$row->Column.'</option>';
}

It will echo a new option for every row in the database matching the query. In this case, it could be this.

<option value="Value1">Value1</option>
<option value="Value1">Value1</option>
<option value="Value2">Value2</option>
<option value="Value1">Value1</option>

How do I prevent this?

score 7 · Accepted Answer · answered Oct 21 '13 at 12:03

7

Do you only want DISTINCT Column values?

SELECT DISTINCT column FROM table WHERE UserID = ?

Assuming your code is accurate, you don't need to select all (*) from the table as you are only using Columnm hence using SELECT DISTINCT Column is sufficient to bring back deduplicated results and values from Column

answered Oct 21 '13 at 12:03

bear

11,364
26
77
129

Why? It answers the question. – Tomdarkness Oct 21 '13 at 12:04
@AmalMurali I would disagree, it contains a code fragment which is a possible solution – bear Oct 21 '13 at 12:04
@AmalMurali I also disagree with you. It's a correct answer to the question, why would it not be posted as an answer? – Oct 21 '13 at 12:06
@Shamil: I've removed my downvote, but consider editing your question and mention selecting `*` is not necessary. :) – Amal Murali Oct 21 '13 at 12:06
@AmalMurali I always select `*`, any reason why I shouldn't? – Oct 21 '13 at 12:07
@Christian: Perfomance, maintenance, indexing... various issues. Read [this](http://stackoverflow.com/questions/3639861/) thread for more details. – Amal Murali Oct 21 '13 at 12:08
@Christian You transfer way more data than needed, it's just bad practise. – h2ooooooo Oct 21 '13 at 12:08
@Shamil: And upvotted :) – Amal Murali Oct 21 '13 at 12:08
@Christian: It's best practice to only select what you need for performance reasons (more data gets transmitted that is needed), but unless you're going for super performance optimization, you're fine selecting *. – Oct 21 '13 at 12:09
@AmalMurali I just often happen to need almost all of the columns. When I don't select all, I'm probably working with joins. – Oct 21 '13 at 12:14

score 1 · Answer 2 · answered Oct 21 '13 at 12:04

Multiple ways.

Use the GROUP BY SQL statement in your query:

$q = "SELECT * FROM table WHERE UserID = ? GROUP BY Column";

or do it in PHP using an array:

$columns = array();
while($row = $smt->fetchObject()){
     if (!isset($columns[$row->Column])) {
          echo '<option value="'. $row->Column. '">'.$row->Column.'</option>';
          $columns[$row->Column] = true;
     }
}

Note: That said, do you really need all the columns? Couldn't you just SELECT Column? No need to transfer more data than needed.

Works, but `DISTINCT` is the thing I'm looking for, as it's meant for this! — , Oct 21 '13 at 12:09

PHP prevent duplicates in while loop

2 Answers2