Solved

Dynamic dropdown menu & Recordsets

Posted on 2003-12-02
11
3,032 Views
Last Modified: 2012-08-14
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);
0
Comment
Question by:brisctt
  • 6
  • 5
11 Comments
 
LVL 4

Expert Comment

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









 
0
 

Author Comment

by:brisctt
ID: 9868267
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
 
LVL 4

Expert Comment

by:c_swanky
ID: 9869226
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
 
LVL 4

Expert Comment

by:c_swanky
ID: 9869411

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
 

Author Comment

by:brisctt
ID: 9869416
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 4

Accepted Solution

by:
c_swanky earned 500 total points
ID: 9870042
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
 

Author Comment

by:brisctt
ID: 9870946
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
 
LVL 4

Expert Comment

by:c_swanky
ID: 9871224
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
 

Author Comment

by:brisctt
ID: 9871256
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
 
LVL 4

Expert Comment

by:c_swanky
ID: 9871708
see the latest code I put on your other post, It's getting close.
0
 

Author Comment

by:brisctt
ID: 9871739
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Record Button 3 214
How to distribute wireless to 4 other PCs. 3 200
Expression Web 2 - where to go? 3 333
Replace Dreamweaver with what? 8 155
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now