How to create search using dataassist and dynamic dropdowns

Hi,

I'm trying to figure out how to set up a search form that will search my database by 3 fields
Category
Brand
Description

Both the category and brand will be dropdown menus that would dynamically change based on making a selection from the other and the description will be a free text field.

I can create a search form using the dataassist wizard and I can create dynamic menus using dynamic dropdowns but I can't seem to get them to work together. Is there a tutorial out there on this?

B

Most likely the foreign keys that link brand to category and description to brand are not included in the applicable recordsets.

For dynamic dropdowns to work there must be a "link" between the recordsets in the form of a foreign key. When the data assist wizard ran did you get just two values in the recordset i.e. the id and one other value which you choose as the text option for the dropdown ? If so you need to open up those recordsets from the servers behaviour panel and add the extra required foreign key that dynamic dropdowns requires.

I have a general tutorial on dynamic dropdowns here multiple-dynamic-dropdowns/ which shows how it all goes together

I gave it a try but I'm getting a little lost. I thing your tutorial would be more useful if I could see the database that is being used to create the menus. I tried to import the sql data into my server but apparently I need access to your server.

Originally Said By: basim

  I tried to import the sql data into my server but apparently I need access to your server.  

I am a bit bemused by that as the sql file that is supplied should be able to be imported via PHPMyAdmin or similar if you have admin rights.

Try just importing the tables into a database you have already created by just using the sql command to create and populate the tables i.e.

-- 
-- Table structure for table `engine`
-- 

CREATE TABLE `engine` (
  `engineID` int(11) unsigned NOT NULL auto_increment,
  `engineSize` decimal(3,1) NOT NULL,
  PRIMARY KEY  (`engineID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `engine`
-- 

INSERT INTO `engine` (`engineID`, `engineSize`) VALUES 
(1, '1.1'),
(2, '1.3'),
(3, '1.6'),
(4, '1.8'),
(5, '2.0'),
(6, '2.5');

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

-- 
-- Table structure for table `manufacturer`
-- 

CREATE TABLE `manufacturer` (
  `manID` int(11) unsigned NOT NULL auto_increment,
  `manDesc` varchar(20) NOT NULL,
  PRIMARY KEY  (`manID`),
  FULLTEXT KEY `manDesc` (`manDesc`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- 
-- Dumping data for table `manufacturer`
-- 

INSERT INTO `manufacturer` (`manID`, `manDesc`) VALUES 
(1, 'Ford'),
(2, 'Toyota'),
(3, 'Vauxhall');

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

-- 
-- Table structure for table `model_engine`
-- 

CREATE TABLE `model_engine` (
  `modelID` int(11) NOT NULL,
  `engineID` int(11) NOT NULL,
  PRIMARY KEY  (`modelID`,`engineID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `model_engine`
-- 

INSERT INTO `model_engine` (`modelID`, `engineID`) VALUES 
(1, 4),
(1, 5),
(1, 6),
(2, 2),
(2, 3),
(2, 4),
(3, 1),
(3, 2),
(3, 3),
(4, 3),
(4, 4),
(4, 5),
(5, 2),
(5, 3),
(5, 4),
(6, 1),
(6, 2),
(6, 3),
(7, 4),
(7, 5),
(7, 6),
(8, 3),
(8, 4),
(8, 5),
(9, 1),
(9, 2),
(9, 3);

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

-- 
-- Table structure for table `models`
-- 

CREATE TABLE `models` (
  `modelID` int(11) unsigned NOT NULL auto_increment,
  `manID` int(11) NOT NULL,
  `modelDesc` varchar(20) NOT NULL,
  PRIMARY KEY  (`modelID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

-- 
-- Dumping data for table `models`
-- 

INSERT INTO `models` (`modelID`, `manID`, `modelDesc`) VALUES 
(1, 1, 'Mondeo'),
(2, 1, 'Focus'),
(3, 1, 'Fiesta'),
(4, 2, 'Avensis'),
(5, 2, 'Auris'),
(6, 2, 'Yaris'),
(7, 3, 'Vectra'),
(8, 3, 'Astra'),
(9, 3, 'Corsa');

It works as the zip has been downloaded from my site many many times and the number of questions I get are minimal.

OK I've almost got it....took me a while but I have it all figured out. Just 2 issues left.

1. When I search it seems to be searching using a contains search instead of exact match so when I search for item number 19 it give 119 as well. How do I stop this.

2. also when I choose my category it will populate the brand list but I lose my "please choose a brand" default message. How do I stop this from happening?

Here's the site address for reference

002jfood_Search.php

Thank you for your help!

B

1) In the Data Assist Search server behavior you can change the search criteria from includes to Equals

2) in the Populate List from Array behavior, set the Leave Top option to 1 to leave the first static option in the list.

Thanks! How do I do both of these without having to start from the beginning? I tried to find documentation showing me how to edit an existing data assist search.

Edit: I actually found the leave top option to 1 option...just need to know how to edit the search behavior.

Find the DataAssist Search entry in your Server Behaviours Panel like so:

Double click on it and it will open up the interface where you can edit the search options

This post has been deleted.

I opened the search entry but didn't find any where I could change the search criteria from includes to equals.

See screenshot for what I get.