Populate Multiple Select Options from database

Submitted by: 

I am hoping someone can point me in the right direction.
I have a need to create a form that requires user selection of choices in 3 SELECT drop down lists.
I have trawled the web forums and youtube etc to try and find a resolution to my problems... tried multiple so called resolutions and have yet to achieve success.

Whether it is something to do with my code or the format of my database I do not know.

Please be kind as I am only a VERY novice programmer and will appreciate any assistance offered.

I have 3 files....
index.php (Form)
loadsubcat.php (PHP processing)
config.php (db connection)

The basics of the files were "borrowed" from user kabiru10 (thank you).
Using kabiru10 "Creating a Dependent Dropdown List with PHP, jQuery and Ajax" example code everything works. It's once I change databases and tables I get stuck and it breaks.

======= CONTENTS of index.php ---------------

  1. <?php
  2. include('config.php');
  3. $query_parent = mysql_query("SELECT DISTINCT State FROM Locations") or die("Query failed: ".mysql_error());
  4. ?>
  5. <!doctype html>
  6. <html>
  7. <head>
  8. <meta charset="utf-8">
  9. <title>Dependent DropDown List</title>
  10. <script type="text/javascript" src="js/jquery.js"></script>
  11. <script type="text/javascript">
  12. $(document).ready(function() {
  13. $("#parent_cat").change(function() {
  14. $.get('loadsubcat.php?parent_cat=' + $(this).val(), function(data) {
  15. $("#sub_cat").html(data);
  16. $('#loader').slideUp(200, function() {
  17. $(this).remove();
  18. });
  19. });
  20. });
  21. });
  22. </script>
  23. </head>
  24. <body>
  25. <form method="get">
  26. <label for="category">Parent Category</label>
  27. <select name="parent_cat" id="parent_cat">
  28. <option>Select State</option>
  29. <?php while($row = mysql_fetch_array($query_parent)): ?>
  30. <option value="<?php echo $row['ID']; ?>"><?php echo $row['State']; ?></option>
  31. <?php endwhile; ?>
  32. </select>
  33. <br/><hr/>
  34.  
  35. <label>Sub Category</label>
  36. <select name="sub_cat" id="sub_cat">
  37. <option>Select Suburb</option>
  38. </select>
  39. </form>
  40. </body>
  41. </html>

====== Contents of loadsubcat.php ===============

  1. <?php
  2. include('config.php');
  3.  
  4. $parent_cat = $_GET['parent_cat'];
  5.  
  6. $query = mysql_query("SELECT Area FROM Locations WHERE ID = {$parent_cat}");
  7. while($row = mysql_fetch_array($query)) {
  8. echo "<option value='$row[Area]'>$row[Area]</option>";
  9. }
  10.  
  11. ?>

====== Contents of config.php ==================
Not Provided
=======================================

====== Partial Database Dump ======================
-- phpMyAdmin SQL Dump
-- version 4.0.8
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 09, 2014 at 11:47 AM
-- Server version: 5.5.29
-- PHP Version: 5.4.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `ozr48210_FitFirst`
--

-- --------------------------------------------------------

--
-- Table structure for table `Locations`
--

  1. CREATE TABLE `Locations` (
  2. `State` VARCHAR(3) DEFAULT NULL,
  3. `Area` VARCHAR(23) DEFAULT NULL,
  4. `ClubID` INT(5) NOT NULL DEFAULT '0',
  5. `ClubName` VARCHAR(33) DEFAULT NULL,
  6. `ClubType` VARCHAR(8) DEFAULT NULL,
  7. `Lattitude` DECIMAL(10,7) DEFAULT NULL,
  8. `Longditude` DECIMAL(10,7) DEFAULT NULL,
  9. `ID` INT(1) DEFAULT NULL,
  10. PRIMARY KEY (`ClubID`),
  11. UNIQUE KEY `ClubID` (`ClubID`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  13.  
  14. --
  15. -- Dumping data for table `Locations`
  16. --
  17.  
  18. INSERT INTO `Locations` (`State`, `Area`, `ClubID`, `ClubName`, `ClubType`, `Lattitude`, `Longditude`, `ID`) VALUES
  19. ('NSW', 'Sydney West', 52958, 'Auburn', 'Passport', -33.8474460, 151.0455480, 2),
  20. ('WA', 'Balga', 52977, 'Balga', 'Passport', -31.8477860, 115.8245160, 5),
  21. ('NSW', 'Sydney Northern Beaches', 52998, 'Balgowlah', 'Platinum', -33.7931690, 151.2643430, 2),
  22. ('NSW', 'Sydney West', 53017, 'Bankstown', 'Passport', -33.9320540, 151.0295710, 2),
  23. ('VIC', 'Melbourne South East', 53037, 'Bayside', 'Passport', -37.9546882, 145.0312471, 4),
  24. ('NSW', 'Sydney Eastern Suburbs', 53064, 'Bondi (The Edge)', 'Passport', -33.8922310, 151.2477680, 2),
  25. ('NSW', 'Sydney Eastern Suburbs', 53083, 'Bondi', 'Platinum', -33.8923240, 151.2473300, 2),
  26. ('VIC', 'Melbourne South East', 53106, 'Brighton', 'Passport', -37.9092540, 145.0119030, 4),
  27. ('QLD', 'Brisbane Central', 53136, 'Brisbane CBD - Brisbane City', 'Passport', -27.4647970, 153.0290020, 3),
  28. ('ACT', 'Canberra', 53197, 'Canberra City', 'Passport', -35.2798580, 149.1337130, 1),
  29. ('WA', 'Cannington', 53212, 'Cannington', 'Passport', -32.0188900, 115.9347500, 5),
  30. ('QLD', 'Brisbane South', 53241, 'Carindale', 'Passport', -27.5170800, 153.0980070, 3),
  31. ('NSW', 'Sydney North West', 53267, 'Carlingford', 'Passport', -33.7778210, 151.0520690, 2),
  32. ('NSW', 'Sydney North West', 53292, 'Castle Hill - Castle Towers', 'Passport', -33.7328193, 151.0049599, 2),
  33. ('VIC', 'Melbourne South East', 53320, 'Chadstone', 'Passport', -37.8878763, 145.0952034, 4),
  34. ('VIC', 'Melbourne South East', 53336, 'Malvern Valley', 'Passport', -37.8773090, 145.0829280, 4),
  35. ('NSW', 'Sydney North Shore', 53360, 'Chatswood', 'Platinum', -33.7957960, 151.1821880, 2),
  36. ('WA', 'Perth CBD', 53391, 'Brookfield Place', 'Platinum', -31.9544740, 115.8552560, 5);

=======================================================

AttachmentSize
Package icon select.zip1.61 KB

this site is very useful

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.