nested sub items in drop down from mysql

[Renidrag]

I am trying to pull the data from mysql to create a nested list of sub links in a drop down box

got it working to a certain point, problem now is, if i add more than one link in the same level it shows the upper level twice

here is the code, any help would be great


<select name="parentid" id="parentid">
<option value="0">Select Main Link</option>
<?php
$sql = "SELECT t1.id as lev1ID, t1.name AS lev1, "
. "t2.id as lev2ID, t2.name AS lev2, "
. "t3.id as lev3ID, t3.name AS lev3, "
. "t4.id as lev4ID, t4.name AS lev4, "
. "t5.id as lev5ID, t5.name AS lev5, "
. "t6.id as lev6ID, t6.name AS lev6, "
. "t7.id as lev7ID, t7.name AS lev7, "
. "t8.id as lev8ID, t8.name AS lev8 "
. "FROM links AS t1 "
. "LEFT JOIN links AS t2 ON t2.parent_id = t1.id "
. "LEFT JOIN links AS t3 ON t3.parent_id = t2.id "
. "LEFT JOIN links AS t4 ON t4.parent_id = t3.id "
. "LEFT JOIN links AS t5 ON t5.parent_id = t4.id "
. "LEFT JOIN links AS t6 ON t6.parent_id = t5.id "
. "LEFT JOIN links AS t7 ON t7.parent_id = t6.id "
. "LEFT JOIN links AS t8 ON t8.parent_id = t7.id "
. "WHERE t1.parent_id = 0 ";

$db->query($sql);
while($row = $db->getNextResult()){
if($row['lev1'] != NULL){
echo "<option value='" . $row['lev1ID'] . "' title='" . $row['lev1ID'] . "' style='color: #000;'>" . $row['lev1'] . "</option>\n";
}
if($row['lev2'] != NULL){
echo "<option value='" . $row['lev2ID'] . "' title='" . $row['lev2ID'] . "' style='color:#660000;'>    - " . $row['lev2'] . "</option>\n";
}
if($row['lev3'] != NULL){
echo "<option value='" . $row['lev3ID'] . "' title='" . $row['lev3ID'] . "' style='color:#7a2426;'>      - " . $row['lev3'] . "</option>\n";
}
if($row['lev4'] != NULL){
echo "<option value='" . $row['lev4ID'] . "' title='" . $row['lev4ID'] . "' style='color:#af292e;'>        - " . $row['lev4'] . "</option>\n";
}
if($row['lev5'] != NULL){
echo "<option value='" . $row['lev5ID'] . "' title='" . $row['lev5ID'] . "' style='color:#d3222a;'>          - " . $row['lev5'] . "</option>\n";
}
if($row['lev6'] != NULL){
echo "<option value='" . $row['lev6ID'] . "' title='" . $row['lev6ID'] . "' style='color:#f12938;'>            - " . $row['lev6'] . "</option>\n";
}
if($row['lev7'] != NULL){
echo "<option value='" . $row['lev7ID'] . "' title='" . $row['lev7ID'] . "' style='color:#fb4357;'>              - " . $row['lev7'] . "</option>\n";
}
if($row['lev8'] != NULL){
echo "<option value='" . $row['lev8ID'] . "' title='" . $row['lev8ID'] . "' style='color:#ff8c9e;'>                - " . $row['lev8'] . "</option>\n";
}
}

?>
</select>

 

[Kozusnik]

Is there a sequence number in the links table? If not, you'll end up with a one-to-many ratio on each of your joins.

If there is a sequence number, just add 'AND t1.seq=1' to the JOIN. Of course, each JOIN will have a tx.seq=x filter.

Mark

 

[Kozusnik]

Nevermind. It's early here. I see what you did.

It's still a one-to-many if you have the same parent_id more than once. You end up with (let's say) the same t1.id, but the t2.id's for those records are different.

Since you're using PHP, maybe it would be best for you to loop through the results and store them in an array using the id as the key for each level. Then loop through the results to display them. That will eliminate the duplication.

While I don't use it often, you could also write the first record, store the tx.id in a variable or array as $last_id_1. Then add '&& $row['id1'] != $last_id_1' to your IF statements.

Mark