don't show duplicates

[kvnirvana]

I’ve got three drop down generated from mysql, $a, $b and $c. If the user chooses from all three drop downs there are no problems, but if the user doesn’t choose from the last drop down, but only $a and $b it shows duplicate names

 

The problem is that there are more than one with the same name in my mysql database, so it shows all the persons with the same name. The reason why there are people with the same name, is that the user gets to rate the person in different categories, and $3 displays the categories they can choose. So off course if they don’t choose $c it will show the same person but from different categories.

What should I do?

This is some of the code

echo "<form name='search' action=".$_SERVER['PHP_SELF']." method='post'> 
        <table width='50%' align='center' valign='center'> 
        
	<tr> 
          <td colspan='2' align='center'>Search</td> 
        </tr> 
        <tr> 
          <td align='right'>a:</td><td>$a</td> 
        </tr> 
        <tr> 
          <td align='right'>b:</td><td>$b</td> 
        </tr> 
        <tr> 
          <td align='right'>c:</td><td>$c</td> 
        </tr> 
  
         
          <td colspan='2' align='center'> </td> 
        </tr> 
        <tr> 
          <td colspan='2' align='center'><input type='submit' name='submit' value='Go!'></td> 
        </tr> 

        </table> 
        </form>"; 

}//end function 


/*------------------------------------------------------------------------ 
            run the search and show the results 
------------------------------------------------------------------------*/
function search() 
{ 

//base sql 
  mysql_connect("localhost", "***", "****") or die(mysql_error());
mysql_select_db("****") or die(mysql_error());

$sql = "select  * from behandlere WHERE 1=1"; 
//get the values from the form //NOTE: You should do way more valdation on the values before you attempt to process anything   
if ((!empty($_POST['a']))&&($_POST['a'] != 'all'))  
  {     $sql .= " and a like '". mysql_real_escape_string($_POST['a'])."%' ";   }     
  if ((!empty($_POST['b']))&&($_POST['b'] != 'all'))  
   {     $sql .= " and b like '". mysql_real_escape_string($_POST['b'])."%' ";   }    
    if ((!empty($_POST['c']))&&($_POST['c'] != 'all'))  
 {     $sql .= " and c = '". mysql_real_escape_string($_POST['c'])."' ";   } 

  // ADD ORDER BY  
  $sql .= ' order by total_value DESC ';
  

[glennn.php]

try SELECT DISTINCT * FROM...

[kvnirvana]

I've tried that but it doesn't seem to work 

[kvnirvana]

I think it’s because even though it’s the same name, its not the same category, so maybe the DISTINCT doesn’t see it as a duplicate. Could this be why, and what should I do?

 

This is what I mean

 

Name: Niel Jensen

Category:a

 

Name: Niel Jensen

Category:b

 

 

Name: Niel Jensen

Category:c

 

So in the database the same person appears in different categories.

 

[kvnirvana]

Anyone?

[kvnirvana]

Someone please help

[jskywalker]

i'm sure someone will help, if you post MORE data

 

The question itself already got answered ("SELECT DISTINCT....")

 

If you really thing this was not a good solution, you should try to explain WHY,

and not simply stat "it does not work...."

 

Give us and URL, or some sample-data, and the query's used...

 

and STOP begging 

[kvnirvana]

I need to use distinct on one row but still select all from table

 

say i have this:

 

ID | Name| Country | value | Group

 

1 | ray      | DK | 10.00 | fm

2 | charles | G  | 18.00 | sb

3 | ray      | DK | 17.00 | fm

4 | kelly    | FR  | 15.00 | sb

 

 

Ray is the same guy, but he has different values, so it shows his name twice. but I only want his name displayed once

So it should only display ray charles kelly

 

My query should look something like this, even though I now it doesn’t work

 

$sql = "SELECT DISTINCT (name), * FROM behan WHERE 1=1 ";

 

[jskywalker]

this is because MySQL cannot know what values to fill in for the "*" in your query

 

DISCTINCT (name), * gives "ray, DK, 10.00" and "ray, DK, 17.00", because these two are different.

 

if you do:

SELECT DISTINCT(name) from behan

than you will get:

ray,charles,kelly

 

if you really need the other fields in your quey, you should find out WHICH value should be returned for the value (10.00 or 17.00)

 

try:

 

SELECT name, min(Country), min(value), min(GrouP) from behand group by name;

[kvnirvana]

I don’t now why but the group by, doesn’t work correctly, it does something to the drop down search so it doesn’t work the way it should. Isn’t there a way to do it with out group by? I need all the values from the other fields

“SELECT DISTINCT(name) from behan” is working, but I get errors because I need all values from the fields.

 

[jskywalker]

I don’t now why but the group by, doesn’t work correctly,

 

if this is TRUE, please report the bug to MySQL

http://dev.mysql.com/doc/refman/5.1/en/bug-reports.html

[kvnirvana]

isn't there a way to do this query "SELECT DISTINCT(name) from behan" but also get all values from the other fields? Besides Group by?

[jskywalker]

what value do you want to be returned for the field 'value' on the line returning  with name='ray' ?

[kvnirvana]

It doesn't matter which value being returned. Couldn't you just do it so it always returns the value which is the highest?

[kvnirvana]

???

[kvnirvana]

I've found a solution :=)