[RESOLVED] Best method to select DISTINCT in a non-normalized table.

[dmacman1962]

Hi All,

Before I start, I plan on taking all this data and normalizing it and then use JOINS to get the data I need to make my drop-down lists, but need to show the flow of this form first. (IE, please don't hammer me about this being non-efficient, I already told the marketing people who are supplying the data, we will need to normalize it).

OK, I have a form that is for teachers to pick which training videos apply to their

State (1st)
Grade (2nd)
Subject (3rd)

Then it will show all the federal 'standards' it covers. (This enables them to choose the approriate material to cover in class and complies with state and federal standards).

Here is an example of the table.

ID----Video------------------------State----------Grade------Class------------Description
1-----The Foundations of Wealth---Pennsylvania-----4---------Social Studies---6.1.3.A Economics: Describe how individuals, families...

So I selected the Video by DISTINCT and made a drop-down menu of the results, but then I want to take the Video selected and narrow the states from the video and so on.

Here is how I got the Video list.

//create the drop down function
function makeDropList($name,$list,$selected="") {
  // $name       select name
  // $list       array of value, label pair
  // $selected   selected value
  global $x;
  while(list($value,$label) = each($list)) {
    $options .= '<option value="'.$value.'">'.$label.'</option>';
  }
  $dropList = '<select name="'.$name.'" tabindex="'.$x.'">'.$options.'</select>'."\n";
  if($contactname == " ") {
  $dropList = ereg_replace("value=\"$selected\"","value=\"$selected\" selected",$dropList);
  }
  return $dropList;
}

//get the data from the db
$videoname = array();
$videoname[0] = " ";
$dbname = 'standards';
mysql_select_db ($dbname, $conn) or die (mysql_error());
	$query = "SELECT DISTINCT Video
			 FROM list";
					$result = mysql_query($query) or die (mysql_error());
						while($row = mysql_fetch_array($result)) {
							$videoname[] = $row['Video'];
							}

//echo the list
$list = makeDropList('Video',$videoname,$videoname);
echo 'Pick the video first --->  '.$list;

Then I want to do a DISTINCT SELECT on the video (I will have to use the name here since it is not normalized, which I would use the Video id in that case).

$state = array();
$state[0] = " ";
$dbname = 'standards';
mysql_select_db ($dbname, $conn) or die (mysql_error());
	$query = "SELECT DISTINCT State
			 FROM list
			 WHERE Video='".$Video."'";
					$result = mysql_query($query) or die (mysql_error());
						while($row = mysql_fetch_array($result)) {
							$state[] = $row['State'];
							}

$list = makeDropList('State',$state,$state);
echo '<br />Now pick the state --->  '.$list;

But this will not work, since the value for Video equals the array ID it was assigned, IE Video[1]='The Foundations of Wealth' in this case.

I need to get the value of Video[1] (The Foundations of Wealth) not ID 1.

Does that make sense?

I know this is probably simple, but I am having a 'vapor=lock' in this case.

Thanks for the help,

Don

[dmacman1962]

Never mind, I figured it out.

I changed the second query to ...

$query = "SELECT DISTINCT State
			 FROM list
			 WHERE Video='".$videoname[$Video]."'";

If anyone has a better way, feel free to post it.

Thanks,

Don