Dynamic dropdown menu & Recordsets

I'm trying to create a page (form) that will display information from several of my tables in my database.  The way I would like to set it up is that the query would pull in the information via 2 recordsets (see below).  It would display the MaterialNumber, then next to that it would display the PacketDropText and PacketDropDownID in a dropdown menu, then have the MaterialID in a hidden field. (all displaying multiple data via repeat regions)

The problem I am having is in the dropdown menu.  I am wanting this information to be relative to the MaterialID...thus each dropdown next to the MaterialNumber should be unique to the MaterialID of that MaterialNumber.

First off I can't seem to get the recordsets to work with the queries below in Dreamweaver MX.  I've tested them with the MySQL console in phpmyadmin and it works fine.

Hopefully this makes sense and someone can shed some light on this for me.


CREATE TEMPORARY TABLE packets
SELECT PacketDetail.MaterialID, PacketDetail.MaterialNumber
FROM PacketDetail INNER JOIN PacketType ON PacketDetail.PacketType = PacketType.PacketTypeID
WHERE (((PacketDetail.HasDropDown)=1))
ORDER BY PacketDetail.MaterialID ASC;

SELECT PacketDropText, PacketDropDownID
FROM PacketDropDown INNER JOIN packets USING(MaterialID);
briscttAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

c_swankyCommented:
Can you post the html code so we can see exactly how you want the infromation / drop-downs to look.









 
0
briscttAuthor Commented:
Here is the code I have on this.  

It should be able to list the MaterialNumber, then the PacketDropText and PacketDropDownID in a dropdown menu (which is unique to the MaterialID and MaterialNumber), as well as have the MaterialID in a hidden form field.

Hopefully someone can help with this one...I've been on the page for a week.

Thanks in advance


<?php require_once('../../../Connections/excite.php'); ?>
<?php
mysql_select_db($database_excite, $excite);
$query_rs_packetdetail = "CREATE TEMPORARY TABLE packets SELECT PacketDetail.MaterialID, PacketDetail.MaterialNumber FROM PacketDetail INNER JOIN PacketType ON PacketDetail.PacketType = PacketType.PacketTypeID WHERE (((PacketDetail.HasDropDown)=1)) ORDER BY PacketDetail.DispOrder ASC;";
$rs_packetdetail = mysql_query($query_rs_packetdetail, $excite) or die(mysql_error());
$row_rs_packetdetail = mysql_fetch_assoc($rs_packetdetail);
$totalRows_rs_packetdetail = mysql_num_rows($rs_packetdetail);

mysql_select_db($database_excite, $excite);
$query_rs_dropdown = "SELECT PacketDropText, PacketDropDownID FROM packetdropdown INNER JOIN packets USING(MaterialID);";
$rs_dropdown = mysql_query($query_rs_dropdown, $excite) or die(mysql_error());
$row_rs_dropdown = mysql_fetch_assoc($rs_dropdown);
$totalRows_rs_dropdown = mysql_num_rows($rs_dropdown);

?>
<html>
<head>
<title>Select Packet Drop Down List&nbsp;&nbsp;&nbsp;&nbsp;</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
//-->
</script>
</head>

<body>
<div id="Layer1" style="position:absolute; width:200px; height:80px; z-index:1; left: -1px; top: -1px;"><img src="../images/topbar.gif" width="780" height="80"></div>

<div id="Layer2" style="position:absolute; width:772px; height:46px; z-index:2; left: 2px; top: 101px;">
  <div align="center"><strong><font color="#660000" size="4" face="Verdana, Arial, Helvetica, sans-serif">ADMINISTRATIVE FUNCTIONS</font></strong> <br>
    <font color="#660000" face="Verdana, Arial, Helvetica, sans-serif"><strong>Packet DROP DOWN LIST Maintenance - Select Packet Item </strong></font><br>
  </div>
</div>
<div id="Layer3" style="position:absolute; width:769px; height:22px; z-index:3; left: 11px; top: 182px;">
  <div align="center">
    <form name="form1" method="get">
    <table width="85%" border="0">
    <?php do { ?>
    <tr>
        <td width="33%" valign="top"><div align="right"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong><?php echo ucfirst($row_rs_packetdetail['MaterialNumber']); ?>:</strong> </font></div>
        </td>
        <td width="45%" valign="top">
            <div align="center">
              <p align="left">
                <select name="select">
                  <?php
do {  
?>
                  <option value="<?php echo $row_rs_dropdown['PacketDropDownID']?>"><?php echo $row_rs_dropdown['PacketDropText']?></option>
                  <?php
} while ($row_rs_dropdown = mysql_fetch_assoc($rs_dropdown));
  $rows = mysql_num_rows($rs_dropdown);
  if($rows > 0) {
      mysql_data_seek($rs_dropdown, 0);
        $row_rs_dropdown = mysql_fetch_assoc($rs_dropdown);
  }
?>
                </select>            
              <font size="1" face="Verdana, Arial, Helvetica, sans-serif"> </font> <font size="1" face="Verdana, Arial, Helvetica, sans-serif"> </font> <font size="1" face="Verdana, Arial, Helvetica, sans-serif"> </font><font size="1" face="Verdana, Arial, Helvetica, sans-serif"> </font><font size="1" face="Verdana, Arial, Helvetica, sans-serif"> </font></p>
            </div>
        </td>
        <td width="22%" valign="top"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">&nbsp;
            <input name="hidden_MaterialID" type="hidden" id="hidden_MaterialID">
            <%=Date()%></font> </td>
    </tr>
    <?php } while ($row_rs_packetdetail = mysql_fetch_assoc($rs_packetdetail)); ?>
    </table>
    </form>
  </div>
</div>
</body>
</html>
<?php
mysql_free_result($rs_packetdetail);

mysql_free_result($rs_dropdown);

mysql_free_result($rs_packetdetail);
?>
0
c_swankyCommented:
Ok, got it. This is very easy to do, but you need to have ONE query that gets all the data. Is there any way you can join the two tables (there must be a realtionship between the two)?

If you return all the rows in ONE recordset and then this will be a breeze.




 
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

c_swankyCommented:

Do this:

1) A  "material Id" is associated with one "PacketDropDownID", right?  If so then add the PacketDropDownID to the query below (you will need to join to get that data somehow):


CREATE TEMPORARY TABLE packets
SELECT PacketDetail.MaterialID, PacketDetail.MaterialNumber
FROM PacketDetail INNER JOIN PacketType ON PacketDetail.PacketType = PacketType.PacketTypeID
WHERE (((PacketDetail.HasDropDown)=1))
ORDER BY PacketDetail.MaterialID ASC;


2) In this query return all packets (this will be used to populate all the values in the drop-down)

SELECT PacketDropText, PacketDropDownID
FROM PacketDropDown  


3) In your while statement you will loop through all the records in the second query to populate the drop down (similar to what you are doing now).

4) While looping, check to see if the PacketDropDownID from the first query equals the value of the PacketDropDownID from the second query if it matches  write "SELECTED" for that option. That will display the correct "Packet" value for the Material ID while still displaying all the Packets available if the user chooses to change it...


Here's a .html version of what I think you are trying to do  (The Material you are listing has a packet Id of 40, hence the "Selected" statement, yet you are still showing all the other packets incase you need to change it.)

<select name="select">
    <option value="10">PacketDropDown-10 TEXT </option>
    <option value="20">PacketDropDown-20 TEXT </option>
    <option value="30">PacketDropDownI-30 TEXT </option>
    <option value="40" selected>PacketDropDown-40 TEXT </option>
    <option value="50">PacketDropDown-50 TEXT </option>
  </select>
0
briscttAuthor Commented:
Yes, here is a page I created with one query.  I really appreciate your help.  

<?php require_once('../../../Connections/excite.php'); ?>
<?php
mysql_select_db($database_excite, $excite);
$query_rs_packets = "SELECT PacketDetail.MaterialID, PacketDetail.MaterialNumber, PacketDropDown.PacketDropDownID, PacketDropDown.PacketDropText FROM PacketDropDown, PacketDetail INNER JOIN PacketType ON PacketDetail.PacketType = PacketType.PacketTypeID WHERE PacketDropDown.MaterialID = PacketDetail.MaterialID AND PacketDetail.HasDropDown = 1 ORDER BY PacketDetail.DispOrder ASC";
$rs_packets = mysql_query($query_rs_packets, $excite) or die(mysql_error());
$row_rs_packets = mysql_fetch_assoc($rs_packets);
$totalRows_rs_packets = mysql_num_rows($rs_packets);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
//-->
</script>
</head>

<body>
<div id="Layer1" style="position:absolute; width:200px; height:81px; z-index:1; left: 0px; top: 0px;"><img src="../images/topbar.gif" width="780" height="80"></div>

<div id="Layer2" style="position:absolute; width:773px; height:58px; z-index:2; left: 2px; top: 105px;">
  <div align="center"><strong><font color="#660000" size="4" face="Verdana, Arial, Helvetica, sans-serif">ADMINISTRATIVE
        FUNCTIONS</font></strong> <br>
    <font color="#660000" face="Verdana, Arial, Helvetica, sans-serif"><strong>Packet
  DROP DOWN LIST Maintenance - Select Packet Item </strong></font></div>
</div>
<div id="Layer3" style="position:absolute; width:771px; height:115px; z-index:3; left: 6px; top: 183px;">
  <div align="center">
    <form name="form1" method="post" action="">
    <table width="80%" border="0">
      <tr>
        <td width="40%"><div align="right"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong><?php echo $row_rs_packets['MaterialNumber']; ?>:</strong></font></div></td>
        <td width="19%"><select name="select">
          <?php
do {  
?>
          <option value="<?php echo $row_rs_packets['PacketDropDownID']?>"><?php echo $row_rs_packets['PacketDropText']?></option>
          <?php
} while ($row_rs_packets = mysql_fetch_assoc($rs_packets));
  $rows = mysql_num_rows($rs_packets);
  if($rows > 0) {
      mysql_data_seek($rs_packets, 0);
        $row_rs_packets = mysql_fetch_assoc($rs_packets);
  }
?>
        </select></td>
        <td width="41%"><input name="hidden_materialdi" type="hidden" id="hidden_materialdi" value="<?php echo $row_rs_packets['MaterialID']; ?>"></td>
      </tr>
    </table>
    </form>
  </div>
</div>
</body>
</html>
<?php
mysql_free_result($rs_packets);
?>
0
c_swankyCommented:
1) Add this query back to the top of you page (we need to get all the drop down values so you can populate the drop-down list)

mysql_select_db($database_excite, $excite);
$query_rs_dropdown = "SELECT PacketDropDownID, PacketDropText FROM packetdropdown";
$rs_dropdown = mysql_query($query_rs_dropdown, $excite) or die(mysql_error());
$row_rs_dropdown = mysql_fetch_assoc($rs_dropdown);
$totalRows_rs_dropdown = mysql_num_rows($rs_dropdown);




I come from an .asp background so I wrote out the psuedo code best I could, you will need to put it into the correct .php syntax. It looks like you are fairly proficient with .php, so this should not be a problem


<select name="select">


// Start Loop for $totalRows_rs_dropdown

<?php do {  ?>

       

      //-- (1) Here you are writing the opening past of the "<option value=" statement and setting the PacketDropDown ID as the value
      <?php echo '<option value="$totalRows_rs_dropdown['PacketDropDownID'] ?>


            //-- (2) Now you are checking to see if the Material Id's "Packet ID" is equal to the "Packet Id" in the looping Recordset
            IF    $totalRows_rs_packets['PacketDropDown.PacketDropDownID']   =  $totalRows_rs_dropdown['PacketDropDownID']  then

                  //-- (2a) Matching record so write "SELECTED"
                  <?php echo ' SELECTED' ?>
            ELSE
      
                  //-- (2b) Otherwise if NO match is found just close the opening statment in Step #1 with ">      
                  <?php echo ' ">' ?>
            End IF


      
      //-- (3) Write the Packet Drop Text and add the closing tags to Setp#1 "</option>"
      <?php echo $totalRows_rs_dropdown['PacketDropText']?></option>



<?php  } //--  (4) Do the above loop as long there is a record in the $totalRows_rs_dropdown record set
      while NOT $totalRows_rs_dropdown.EOF );
        

      //-- (5) Move to the next record in the $totalRows_rs_dropdown recordset
      $totalRows_rs_dropdown.MOVENEXT
        
      ?>


</select>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
briscttAuthor Commented:
This is what I have, but it's giving me a parse error, so I think it's in my code.  I'm not the best at handcoding stuff like this, so I will have to take a look.  If you see anything, feel free to give me your input.  Thanks again for all your help.

<?php require_once('../../../Connections/excite.php'); ?>
<?php
mysql_select_db($database_excite, $excite);
$query_rs_packets = "SELECT PacketDetail.MaterialID, PacketDetail.MaterialNumber, PacketDropDown.PacketDropDownID, PacketDropDown.PacketDropText FROM PacketDropDown, PacketDetail INNER JOIN PacketType ON PacketDetail.PacketType = PacketType.PacketTypeID WHERE PacketDropDown.MaterialID = PacketDetail.MaterialID AND PacketDetail.HasDropDown = 1 ORDER BY PacketDetail.DispOrder ASC";
$rs_packets = mysql_query($query_rs_packets, $excite) or die(mysql_error());
$row_rs_packets = mysql_fetch_assoc($rs_packets);
$totalRows_rs_packets = mysql_num_rows($rs_packets);
mysql_select_db($database_excite, $excite);
$query_rs_dropdown = "SELECT PacketDropDownID, PacketDropText FROM packetdropdown";
$rs_dropdown = mysql_query($query_rs_dropdown, $excite) or die(mysql_error());
$row_rs_dropdown = mysql_fetch_assoc($rs_dropdown);
$totalRows_rs_dropdown = mysql_num_rows($rs_dropdown);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
//-->
</script>
</head>

<body>
<div id="Layer1" style="position:absolute; width:200px; height:81px; z-index:1; left: 0px; top: 0px;"><img src="../images/topbar.gif" width="780" height="80"></div>

<div id="Layer2" style="position:absolute; width:773px; height:58px; z-index:2; left: 2px; top: 105px;">
  <div align="center"><strong><font color="#660000" size="4" face="Verdana, Arial, Helvetica, sans-serif">ADMINISTRATIVE
        FUNCTIONS</font></strong> <br>
    <font color="#660000" face="Verdana, Arial, Helvetica, sans-serif"><strong>Packet
  DROP DOWN LIST Maintenance - Select Packet Item </strong></font></div>
</div>
<div id="Layer3" style="position:absolute; width:771px; height:115px; z-index:3; left: 6px; top: 183px;">
  <div align="center">
    <form name="form1" method="post" action="">
    <table width="80%" border="0">
      <tr>
        <td width="40%"><div align="right"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong><?php echo $row_rs_packets['MaterialNumber']; ?>:</strong></font></div></td>
        <td width="19%"><select name="select">
              <?php
                  // Start Loop for $totalRows_rs_dropdown
do {  
?>
        <option value="<?php echo $row_rs_dropdown['PacketDropDownID']?>"><?php echo $row_rs_dropdown['PacketDropText']?>
        <?php
            if($totalRows_rs_packets['PacketDropDown.PacketDropDownID'] = $totalRows_rs_dropdown['PacketDropDownID'])(
         then <?php echo 'SELECTED' ?>
          ELSE
<?php echo ' ">' ?>
          End IF <?php echo $totalRows_rs_dropdown['PacketDropText']?></option>

<?php while NOT $totalRows_rs_dropdown.EOF );
$totalRows_rs_dropdown.MOVENEXT
       
     ?>

        </select></td>
        <td width="41%"><input name="hidden_materialdi" type="hidden" id="hidden_materialdi" value="<?php echo $row_rs_packets['MaterialID']; ?>"></td>
      </tr>
    </table>
    </form>
  </div>
</div>
</body>
</html>
<?php
mysql_free_result($rs_packets);
?>
0
c_swankyCommented:
The code I gave you semi-pseudo code, which means that some of the syntax was there (but not necessarily correct). I was just trying to give you the steps so you could convert it too full blown .php.


If I have some time tonight, I'll see what I can come up with.
0
briscttAuthor Commented:
Good deal.  I know I made some changes, but as I mentioned I not the best at handcoding.  That's the bad part of Dreamweaver for me, it makes me lazy and not learn as much.  I hand coded everything for 3 years or so, then I started using Dreamweaver and loved it, so needless to say, most of the time I look for extensions to do the work for me. :)

Thanks again for all your help with this.  I REALLY appreciate it.

0
c_swankyCommented:
see the latest code I put on your other post, It's getting close.
0
briscttAuthor Commented:
Yes, I did see it.  It looks like I have the page displaying without any errors, but just not getting anything to display in the dropdown menus.  This is the full page code that I have to get that:

<?php require_once('../../../Connections/excite.php'); ?>
<?php
mysql_select_db($database_excite, $excite);
$query_rs_packets = "SELECT PacketDetail.MaterialID, PacketDetail.MaterialNumber FROM PacketDetail INNER JOIN PacketType ON PacketDetail.PacketType = PacketType.PacketTypeID WHERE  PacketDetail.HasDropDown = 1 ORDER BY PacketDetail.DispOrder ASC";
$rs_packets = mysql_query($query_rs_packets, $excite) or die(mysql_error());
$row_rs_packets = mysql_fetch_assoc($rs_packets);
$totalRows_rs_packets = mysql_num_rows($rs_packets);
mysql_select_db($database_excite, $excite);
$query_rs_dropdown = "SELECT PacketDropDownID, PacketDropText FROM PacketDropDown";
$rs_dropdown = mysql_query($query_rs_dropdown, $excite) or die(mysql_error());
$row_rs_dropdown = mysql_fetch_assoc($rs_dropdown);
$totalRows_rs_dropdown = mysql_num_rows($rs_dropdown);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
//-->
</script>
</head>

<body>
<div id="Layer1" style="position:absolute; width:200px; height:81px; z-index:1; left: 0px; top: 0px;"><img src="../images/topbar.gif" width="780" height="80"></div>

<div id="Layer2" style="position:absolute; width:773px; height:58px; z-index:2; left: 2px; top: 105px;">
  <div align="center"><strong><font color="#660000" size="4" face="Verdana, Arial, Helvetica, sans-serif">ADMINISTRATIVE
        FUNCTIONS</font></strong> <br>
    <font color="#660000" face="Verdana, Arial, Helvetica, sans-serif"><strong>Packet
  DROP DOWN LIST Maintenance - Select Packet Item </strong></font></div>
</div>
<div id="Layer3" style="position:absolute; width:771px; height:115px; z-index:3; left: 6px; top: 183px;">
  <div align="center">
    <form name="form1" method="post" action="">
    <table width="80%" border="0">
      <tr>
        <td width="40%"><div align="right"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong><?php echo $row_rs_packets['MaterialNumber']; ?>:</strong></font></div></td>
        <td width="19%">
<select name="select">
 <?php
 
     while ($rs_dropdown = mysql_fetch_array($row_rs_packets))
     {          
          print '<option value="' . $row_rs_dropdown['PacketDropDownID'] . '"';
     
          if($rs_packets['PacketDropDown.PacketDropDownID'] = $rs_dropdown['PacketDropDownID'])
          {
               print ' SELECTED>';
          }else{
               print '>';
          }    

          //-- print text and close <option> string ::  drop-down-text-here</option>
          print $row_dropdown['PacketDropText'] . '</option>';
     }
                        while ($row_rs_dropdown.MOVENEXT)
                   
                   
       
                              ?>
            </select>
          </td>
        <td width="41%"><input name="hidden_materialdi" type="hidden" id="hidden_materialdi" value="<?php echo $row_rs_packets['MaterialID']; ?>"></td>
      </tr>
    </table>
    </form>
  </div>
</div>
</body>
</html>
<?php
mysql_free_result($rs_packets);
?>
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Adobe Dreamweaver

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.