Want 2 values from Db in drop down.

[acuken]

I've got two tables (classOfferings, instructors). The fields I'm dealing with are 'co.instructorId', 'i.instructorId', 'i.fName', 'i.lName'.

I have a form with dynamically generated drop downs.

What I would like to do is check classOfferings table to see which instructors are teaching classes then display them in drop down with the 'instructorId' as the value and 'fName' and 'lName' as the user selectable part.  So I have found the distinct 'instructorId', but I can't make 'fName' and 'lName' appear as the user selectable part.

The code below produces a drop down which has invisible values, but still posts a value.

 

<select size="1" name="instructor">

<option value="" selected>Search By Teacher...</option>

<?

 

$instrList=mysql_query("select distinct instructorId from classOfferings order by instructorId asc");

$instrNameList=mysql_query("select fName, lName from instructors where classOfferings.instructorId = instructors.instructorId order by lName asc");

 

// Show records by while loop.

while($instructor_list=mysql_fetch_assoc($instrList)){

$instrNames = ($instr_Name['fName']) . ($instr_Name['lName']);

?>

<option value="<? echo $instructor_list['instructorId']; ?>" <? if($instructor_list['instructorId']==$select){ echo "selected"; } ?>>

<? echo $instrNames; ?></option>

<?

// End while loop.

}

?>

 

</select>

[fugix]

for these two lines

$instrList=mysql_query("select distinct instructorId from classOfferings order by instructorId asc");
$instrNameList=mysql_query("select fName, lName from instructors where classOfferings.instructorId = instructors.instructorId order by lName asc");

 

use a join query to get the correct results..look here

[acuken]

Thanks for the link.

I had tried joining before, but I'm not getting which one would be right.

I recoded the query, but now the drop down has nothing in it.

More info:

There are 200 classes and 25 instructors, 15 of those instructors teach various numbers of the 200 classes.

 

Here's the replacement code:

 

$instrList=mysql_query("select distinct classOfferings.instructorId, instructors.fName, instructors.lName from instructorId AS co INNER JOIN instructors AS i ON instructorId WHERE co.instructorId = i.instructorId order by lName asc");

 

[fugix]

do you receive any errors if your add debugging...eg

$instrList=mysql_query("select distinct classOfferings.instructorId, instructors.fName, instructors.lName from instructorId AS co INNER JOIN instructors AS i ON instructorId WHERE co.instructorId = i.instructorId order by lName asc") or die(mysql_error());

[acuken]

I put in some debbuging thing:

ini_set('display_errors', 'On');

error_reporting(E_ALL | E_STRICT);

 

no bugs on our issue, but other stuff on the page has issues...

I think it's not finding anything.

Just to back up - the following code gives me the instructors I want, but it gives me the instructorId not the fName and lName. So that is the general query working.

 

<?

 

$instrList=mysql_query("select distinct instructorId from classOfferings order by instructorId asc");

 

// Show records by while loop.

while($instructor_list=mysql_fetch_assoc($instrList)){

?>

<option value="<? echo $instructor_list['instructorId']; ?>" <? if($instructor_list['instructorId']==$select){ echo "selected"; } ?>>

<? echo $instructor_list['instructorId']; ?></option>

<?

// End while loop.

}

?>

 

[acuken]

On further study inner join was the thing I needed.

So the following code finds only instructors who are teaching classes (from classOfferings table) and puts one instance into the drop down menu. For each option instructorId is the value and fName and lName (from instructors table) are the clickable choices.

Thanks for the help.

 

<select size="1" name="instructor">

<option value="" selected>Search By Teacher...</option>

<?

 

 

$instrList=mysql_query("SELECT distinct classOfferings.instructorId, instructors.fName, instructors.lName

      FROM classOfferings

      INNER JOIN instructors

      WHERE instructors.instructorId = classOfferings.instructorId

      ORDER BY lName asc");

//$instrNameList=mysql_query("select fName, lName from instructors where classOfferings.instructorId = instructors.instructorId order by lName asc");

 

// Show records by while loop.

while($instructor_list=mysql_fetch_assoc($instrList)){

$instrNames = ($instr_Name['fName']) . ($instr_Name['lName']);

?>

<option value="<? echo $instructor_list['instructorId']; ?>" <? if($instructor_list['instructorId']==$select){ echo "selected"; } ?>>

<? echo $instructor_list['fName'];?>

<? echo $instructor_list['lName'];?>

</option>

<?

// End while loop.

}

?>

 

</select>