Distinct Drop Downs

ssjody

I am having a problem getting my results to be DISTINCT in my three drop downs. Here is a link to my drop downs. http://www.searstractor.com/interchange/buildselect.php All three columns need to be DISTINCT but does not work for some reason. How do I fix this? Thanks

  $query = "SELECT DISTINCT `year`, `part_type`, `side` from `interchange` where `model_number` = $value limit 0, 30";

?>

<?php
if($results = mysql_query($query)) {
	while($row = mysql_fetch_array($results)) {
		$data[] = $row;
	}
}
?>

<?php
echo "<select name='year'>\n";
foreach($data as $arr) {
	echo "<option value = '" . $arr['year'] . "'>" . $arr['year'] . "</option>\n"; } echo "</select>"
?>

<?php
reset($data);
?>

<?php
echo "<select name='part_type'>\n";
foreach($data as $arr) {
	echo "<option value = '" . $arr['part_type'] . "'>" . $arr['part_type'] . "</option>\n"; } echo "</select>"
?>

<?php
reset($data);
?>

<?php
echo "<select name='side'>\n";
foreach($data as $arr) {
	echo "<option value = '" . $arr['side'] . "'>" . $arr['side'] . "</option>\n"; } echo "</select>"
?>

rachel2004

The keyword DISTINCT is doing what it's supposed to be doing, which is it is only select unique combinations of columns 'year' + 'part_type' + 'side'. It is the combination of all three columns that makes it unique. So if you had values like 2004, part123, and sideA and another row exactly the same (2004, part123, sideA), only one of those rows would be selected. But a row with values like '2004', 'part123', and 'sideB' would show up in the result set, even though the year is the same for the first example I gave you. That is because the combination of the values '2004', 'part123', and 'sideB' make the combination of the three columns unique.

So what to do? Revisit your database design. Since you have duplicate values in the columns 'year', 'part_type' and 'side', consider breaking those off into their own tables and establish a linking table since you would have a many-to-many relatinonship between them.

Once you've done that, you could use a JOIN in your query and depending on the WHERE clause in your query, you should be able to achieve your goal.

You should try this on a small scale before you make any changes to your production database and/or tables.

ssjody

http://qa.car-part.com/cgi-bin/jeffm.cgi This is a link to what I am trying to accomplish but was not written with PHP. It works beautifully with the exact same database setup. I just want to make it work like this but with PHP. Any idea how to do it?