Populate Multiple Select Options from database

10 years ago By OzReaction
Attachment Size
select.zip 1.61 KB
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);
=======================================================

Add new comment

About text formats