Edit Form Drop down selected value problems.

[astratt]

Okay here is my first shot in asking for php help.  I have an edit Form in which I need to allow the user to change a drop down value if needed. The problem is the selected value is stored in the Milestones table and the Drop down values are in the Quarters table. I need to make available all of the drop down options from the quarters table but highlight what was selected by the user which is stored in Milestones table, which only holds the selected values.  Its a 1 to many relationship, 1 quarter can be part of many milestones if that helps. Please also be aware that I'm new to php so be gentle in explaining help if possible.

 

Edit Select Drop Down code:

echo '<select name="Quarter">';
	$getQuarter = "Select q.QuarterID,q.Quarter,m.QuarterID FROM tblQuarters AS q LEFT JOIN tblMileStones AS m ON q.QuarterID = m.QuarerID";
  		$result = $connector->query($getQuarter);  				
	if(mysql_num_rows($result)==0) echo "There is no data in table..";
		else
		for($i=0;$i<mysql_num_rows($result);$i++) {
		$row=mysql_fetch_assoc($result);
		echo"<option value='$row[QuarterId]'>$row[Quarter]</option>";
		}
		echo'</select>';

 

So if you can help, that would be great.

[lemmin]

Though you can get all the information in one query, I think it makes sense to do two queries:

$qry1 = mysql_query("SELECT QuarterID FROM tblMileStones WHERE UserID = ".$userid);
$qrtid = mysql_result($qry1, 0);
$qry2 = mysql_query("SELECT QuarterID, Quarter FROM tblQuarters");

while ($row = mysql_fetch_assoc($qry2))
{
	if ($row['QuarterID'] == $qrtid)
		echo "<option value=\"".$row['QuarterID']."\" SELECTED=\"true\">".$row['Quarter']."</option>";
	else
		echo "<option value=\"".$rown['QuarterID']."\">".$row['Quarter']."</option>";
}

[GingerRobot]

Untested, but you'll want something like this:

 

<?php
$sql = "SELECT q.QuarterID, q.Quarter, m.QuarterID AS selected FROM tblquarters AS q LEFT JOIN tblMileStones AS m USING QuarterID";
$result = $connector->query($sql);
if(mysql_num_rows($result)==0){
echo "There is no data in table..";	
}else{
echo '<select name="Quarter>';
while($row = mysql_fetch_assoc($result)){
	echo '<option value="'.$row['QuarterID'].'"';
	if(!is_null($row['selected'])){
		echo 'selected="selected"';
	}
	echo '>'.$row['Quarter'].'</option>';
}
echo '</select>';
}
?>

 

Now, you were correct in thinking that you'll need to retrieve IDs from both tables. However, you need to be able to access these separately in your results set, so you need to give one an alias (i gave the one form your milestones table the alias selected). The whole point of a left join is that it allows you to find rows that don't match. In the results set, they are null.

 

Therefore, for the rows which aren't null (that is, they are in both tables), we want to echo a selected tag.

 

I also changed your for loop to a while; since there's less to write . Oh, and i thought i'd show you the USING clause too. When you're doing a join, if the two columns have the same name, you can just give that column name and use USING.

 

Edit: @lemmin. If you can get all the info in more query, why would it make more sense to use two? Why double the number of queries if you dont have to?

[lemmin]

@GingerRobot, because he said that this was for a single user so I am assuming that he will need only one row from tblMileStones instead of any of them that match, like your query would return. In this case, you would have to do a sub query to only get the one value from tblMileStones and still return all of the rows in tblQuarters, and it makes every row have a reduntant value in it, just as your query does. In either case, since using two queries returns no redundant data, it is going to be faster (in most cases) than trying to get all of the information in one query; however, if his DB is on a remote server, I would definately recommend using one query.

[astratt]

Well in some degree you are both right. An example of what I'm trying to show on the edit form is this.

 

Quarters Table

QuarterID(PK) Quarters

1                    1st

2                    2nd

3                    3rd

4                    4th

etc..

 

MileStones Table

ID F1 F2 F3 QuarterID(FK) F4 etc..

1                3

2                1

3                1

4                2

5                4

6                1

7                2

etc

 

Now this how I would like the drop down to display on the Milestone Edit page:

 

QuarterDDL

1st

2nd -- Lets say this is the value that is being displayed for this MileStone which relates to milestone ID 4( but the user has the abiltity to change this value.

3rd

4th

etc

 

 

Maybe this could clarify it some more or either Im not getting it. LOL, I'm such a noob.