Skip to main content
_
_AJD_TUS_
Inspiring
October 5, 2011
Answered

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

  • October 5, 2011
  • 1 reply
  • 2915 views

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?

This topic has been closed for replies.
Correct answer bregent

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


>"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.

1 reply

B
bregent
Participating Frequently
October 5, 2011

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

SELECT DISTINCT Town FROM MyTable

_
_AJD_TUS_Author
Inspiring
October 6, 2011

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_Author
Inspiring
October 6, 2011

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices