CODEWITHSUNDEEP
mysqlsqlselectduplicates
Darius
Darius

Reputation: 273

Select one row without duplicate entries

In mysql table info i have :

Id , Name , City , date , status

I want to select all names from "info" Making the query

$query = mysql_query("SELECT name FROM info WHERE status = 1 ORDER BY id") 
         or die(mysql_error());

while ($raw = mysql_fetch_array($query)) 
{
  $name = $raw["name"];
  echo ''.$name.'<br>';
}

Well, the result is that it returns all the entries. I want to echo all the entries without duplicates.

Saying: under raw "name" we have inserted the name "John" 10 times.
I want to echo only one time. Is this possible?

Upvotes: 24

Views: 97072

Answers (8)

Bohemian
Bohemian

Reputation: 424973

It's pretty simple:

SELECT DISTINCT name FROM info WHERE status = 1 ORDER BY id

The SQL keyword DISTINCT does the trick.

Upvotes: 53

Laud Randy Amofah
Laud Randy Amofah

Reputation: 29

$sql="SELECT DISTINCT name FROM status =1 GROUP BY name ORDER BY name";

$query = mysqli_query($conn,$sql);
<?php while ( $fire=mysqli_fetch_array($query)) { ?>
<h4><?php echo $query['name']; ?><br></h4>
<?php } ?>

Upvotes: 0

Humphrey
Humphrey

Reputation: 2807

Let us say that you are sending bulk sms and you don't want to send the same message two times to the same guy John . What i discovered , is that using the trick of GROUP BY and ORDER BY at the same time works perfectly . But I don't say that its the best way . Here is how u can use it 

SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY name

EDIT: This is important to note, when you need more then single column and there values are unique per row then the DISTINCT was not helping.

Upvotes: 0

Darkpore
Darkpore

Reputation: 1

This works for me, returns the table names for a given database.

my $sql="select distinct table_name from COLUMNS  where table_schema='$database'"
my $sth = $dbht->prepare( $sql )
      or die "Cannot prepare SQL statement: $DBI::errstr\n";
  $sth->execute
      or die "Cannot execute SQL statement: $DBI::errstr\n";

  if ($DBI::err){

    $msg= "Data fetching terminated early by error: $DBI::errstr";

}


while (@col=$sth->fetchrow_array()){
    $table[$i]=$col[0];
    $i++;
}

Upvotes: 0

cristian
cristian

Reputation: 8744

Change 

SELECT name FROM info WHERE status = 1 ORDER BY id

to 

SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY id

Observe that GROUP BY was added. More about group by http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Edit:
for name with number of apparences try 

SELECT name, count(name) FROM info WHERE status = 1 GROUP BY name ORDER BY id

Upvotes: 6

Dalen
Dalen

Reputation: 8986

try using this as your query:

SELECT DISTINCT name FROM info WHERE status = 1 ORDER BY id

to get distinct names

or as other suggested use GROUP BY

SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY id

I think the first one is more intuitive and there are no big performance difference between the two

EDIT

as the OP wants also the number of names here we go:

SELECT name,COUNT(id) AS n_names
FROM info WHERE status = 1
GROUP BY name
ORDER BY name

you can ORDER BY name or n_names depending on what you need

Upvotes: 17

SergeS
SergeS

Reputation: 11779

use GROUP BY name statement 

$query = mysql_query("SELECT name FROM info WHERE status = 1 GROUP BY name ORDER BY id") or      die(mysql_error());

while ($raw = mysql_fetch_array($query)) {
                $name = $raw["name"];
                echo ''.$name.'<br>';
                }

Upvotes: 0

Manse
Manse

Reputation: 38147

add GROUP BY name to your SQL Statment - this will only bring back one of each entry from the name column

Upvotes: 1

Related Questions