Forum Moderators: coopster
About my mysql database that I am pulling from - I have various 'Venues' in 3 'States' and certain 'Day' that there is an event at the venue.
-- phpMyAdmin SQL Dump
-- version 2.6.2-pl1
-- [phpmyadmin.net...]
--
-- Host: localhost
-- Generation Time: May 11, 2009 at 04:06 PM
-- Server version: 4.1.22
-- PHP Version: 4.4.9
--
-- Database: `**********`
--
-- --------------------------------------------------------
--
-- Table structure for table `Location`
--
CREATE TABLE `Location` (
`id` int(11) NOT NULL auto_increment,
`Venue` varchar(100) NOT NULL default '',
`Day` varchar(10) NOT NULL default '',
`Start` varchar(10) NOT NULL default '',
`Address` varchar(100) NOT NULL default '',
`City` varchar(20) NOT NULL default '',
`State` varchar(20) NOT NULL default '',
`Zip` varchar(5) NOT NULL default '',
`Phone` varchar(14) NOT NULL default '',
`New` char(3) NOT NULL default 'No',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;
<snipped data dump -- file layout is good enough>
When I created a drop down menu and pulled the States into it, it populates a State for each row above. I fixed that by changing ORDER by to GROUP by.
This is where I am stuck, how can I use the 3 results from the 1st drop down to create and populate a second drop down menu with the days of the week. I found a couple scripts online and each hung up when I did a GROUP by - in the day drop down it would only show one value such a Wednesday for only one row, couldn't figure it out.
I have figured out how to staticaly do it, but I would prefer to do it dynamicaly for future changes that may need to be made.
From there, I would like to "POST" the results through the URL. I have figured out how to do that with a very simple form, but I can't figure out the multiple drop down menus. Once I get the 2 drop downs working and posted in the URL I am set from there. Joys of saying you can do something for someone when you’re not sure, right? Lesson learned!
[edited by: coopster at 1:29 pm (utc) on May 12, 2009]
[edit reason] Removed unnecessary data dump [/edit]
Large code posts tend to steer folks away from discussion and may have been the case here.
When I created a drop down menu and pulled the States into it, it populates a State for each row above. I fixed that by changing ORDER by to GROUP by.
GROUP BY will create distinct rows for you by grouping all the rows with like columns specified into a single summary row for each. You can then apply the ORDER BY to sort the result set. Something like:
SELECT State FROM Location GROUP BY State ORDER BY State;
To carry the value from the first selection (State) on through to the next display or stage of your process you could write the State selection to a hidden form field, set a cookie, use a session, carry it in the QUERY STRING, etc. The hidden form field may be easiest.
