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
ORDER BY PacketDetail.MaterialID ASC;
SELECT PacketDropText, PacketDropDownID
FROM PacketDropDown INNER JOIN packets USING(MaterialID);