Return all duplicate values in lookup (PHP, drop-down list)

[_AJD_TUS_]

Hi, I have a PHP page that I've designed as a search page. I have a number of drop-down boxes that get their dynamic values from fields in a MySQL database.

At present, if there are any entries that have duplicate values in a certain field, they are all listed, in the order of the database index. For example:

Town:

  Derby

  Stoke

  Stafford

  Stoke

  Derby

  Derby

  Stoke

  etc...

Now, what I want to do is be able to show all of the results for a given town. In the search drop-down list box, I would want each of the towns to only appear once rather than each time that it is listed; included in this, I would want the results page to then display ALL of the results that match that search criteria.

So, in the example above, "Derby" would only appear once but, when the user selected "Derby", it would return all three results in the results page.

My results page is built on a dynamic form, so I am confident that the results page would render as required. However, How do I get the drop-down list box to instruct it?

[bregent]

You just need to add the DISTINCT keyword to your select statement.

SELECT DISTINCT Town FROM MyTable

[_AJD_TUS_]

Thanks bregant,

Where do I ammend the code to select DISTINCT ?

Here is the code for the search box as it is at the moment:

<select name="Location_Search" id="Location_Search">

<?php

do {

?>

<option value="<?php echo $row_Recordset1['Location_C']?>"><?php echo $row_Recordset1['Location_C']?></option>

<?php

} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));

$rows = mysql_num_rows($Recordset1);

if($rows > 0) {

mysql_data_seek($Recordset1, 0);

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

}

?>

</select>

Thank you!

[_AJD_TUS_]

(I should add that I have added "DISTINCT" to the '$query_Recordset1 = "SELECT...' string earlier in the PHP. Still doesn't work)

[bregent]

Show us the SQL behind the recordset that is populating the drop-down.

[_AJD_TUS_]

Code follows (for commercial confidentiality, I have changed the table/field names to generic examples)

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;   
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_DB1, $DB1);
$query_Recordset1 = "SELECT DISTINCT Table1.Field1, Table1.Field2, Table1.Field3 FROM Table1";
$Recordset1 = mysql_query($query_Recordset1, $DB1) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>

Thank you

[bregent · Accepted solution]

>"SELECT DISTINCT Table1.Field1, Table1.Field2, Table1.Field3 FROM Table1";

The DISTINCT keyword forces SQL to supress duplicate rows. This means it will only return unique combinations of fields in your select list. If you examine your result set, you will see that each row is distinct, although there may be duplicate values in a particular field. Since you want to populate a drop-down, you should only include the one field in your select list, otherwise you will get dupes.

[_AJD_TUS_]

Ah, I see!

That suddenly makes a lot of sense. Thank you.

Is there another way of trying to do what I want (multiple look-up fields in one search form) whilst still maintaining the unique valeus in each of the drop-down menus?

[bregent]

>Is there another way of trying to do what I want (multiple look-up fields in one search form)

>whilst still maintaining the unique valeus in each of the drop-down menus?

The most common way is to have a separate recordset that populates each drop down. In ASP (with ADO), you can stuff multiple select statements into a single recordset, creating what is called a Multiple Recordset. This is a perfect solution for populating multiple drop-downs. I don't know if this is possible with PHP.

The only other way to use the select statement you currently have, would be to step through each value and perform some kind of de-duping loop operation. This sounds tedius and I would not recommend it.

EDIT: Ok, it looks like you can produce multiple recordsets with PHP/MySQL using stored procedures: http://www.justintubbs.com/code-samples/mysql-stored-procedures.php

Look at #4.

In ASP, you are able to create multiple recordsets by simply separating each select statement with a semicolon. Not sure if that option exist with PHP/MySQL - but the stored procedures approach should work fine.

EDIT2: Looks like MySQL supports multiple selects separated by semicolon: http://markalexanderbain.suite101.com/how-tor-run-multiple-mysql-queries-with-php-a105672

So the solution is to create a separate SELECT DISTINCT statement for each drop-down, and then put them together in a string separating each with a semicolon. Then loop through each result to populate the dropdown.