Auto populate fields from sql database.

[olidenia]

I need some help or tips on the following.

I have a .sql database file with the folowing structure:

DROP TABLE IF EXISTS `car`;
CREATE TABLE IF NOT EXISTS `car` (
  `id` int(10) default NULL,
  `Make` text,
  `Model` text,
  `Year_` text,
  `Car_Fuel` text,
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And here is a sample of the data inserted in to this database:

INSERT INTO `car` (`id`, `Make`, `Model`, `Year_`, `Car_Fuel`) VALUES
(1, 'Toyota', '4Runner SR5 4x4', '2004', 'Gasoline'),
(2, 'Ford', 'Fiesta 1.4', '2003', 'Diesel'),
(3, 'Seat', '4Runner SR5 4x4', '2004', 'Diesel'),
(4, 'Volkswagen 2.0', 'Golf', '2008', 'Gasoline'),
(5, 'Nissan', '300z', '2004', 'Petrol'),
(6, 'Ford', 'Fiesta 1.8', '2005', 'Gasoline'),
(7, 'Porsche', '356 America', '1952', 'Diesel'),
(8, 'Lancia', 'Hit', '1988', 'Gasoline'),
 
 

And so on... up to 42000 different cars

How would I get around to make auto populating dropdown boxes selecting data from the database in the following order:

First dropdown: Make
Second auto populate: Car_Fuel
Third: Year
And then only show the Models acording to the above.

Thanks beforehand for any help or tips at all, I have done other scripts but on this one I dont know where to start.

[jhardman]

Unfortunately classic ASP doesn't do this for you automatically. First you will need to connect to the db, then pull the data you are interested in in a recordset (similar to a DataTable in .NET). Do you need help with this part?

After you have that done, you just need to loop through the db as you fill in the <select> drop down options. This is fairly easy, so don't worry about it until you have the first part done.

The tricky part is how do you get the second drop down box to respond to the selections you make in the first drop down (so if you select "Ford" make sure only Ford models are available in the next drop down). For this ASP is not sufficient, you will need to branch out to Javascript or Ajax. Anyway, let me know when you have the first part done, and I'll try to give you a hand on the next step.

Jared

[olidenia]

You see I have fair knowledge of php but lacking knowledge of AJAX, I'm willing to pay for this script if someone is interested in making it.

keep in mind it should only show the years a make of cars has and further on only the fuel types for make and year, so finally you only get the models of cars according to [Make] --- populate ---->[Year] --- populate ----> [Fuel] --- populate ----> [Models] the reason is to eliminate misspellings by the user and then when the listing is submitted other data comes up according to the model selected (As could be number of doors, fuell milage and so on...)

The Sample sql I have provided is only for referance, it has many more colums in the table car: (`id`, `Make`, `Model`, `Year_`, `Car_Fuel`, 'another', 'another'...)

But I only want the Auto populating drop downs at this moment.

[jhardman]

Well, I've seen a couple ways to do it before Ajax got big. A lot of sites used to submit the form after the first selection is made, then the page is reloaded with everything in place, the second select ready. I've also seen it done with a whole bunch of selects in hidden divs, then the correct div made visible when on e selection is made. If both of those sound awkward, then you see the reason why Ajax is used. I can help with either of those other options, but if you want an ajax solution (or if you want to contact an Ajax expert) post in the javascript/ajax forum.

Jared

[CroCrew]

Hello olidenia,

I am not interested in making money from helping you out. But, if you want to donate funds to BYTES then I am sure the owners of the site would appreciate it very much.

What I am interested in is providing solutions to people that need help. Do you what a example solution in Classic ASP? I know Jhardman is willing to help out too..

CroCrew~