Using PHP to create a dynamicly changing javascript menu

Database of car makes and models that I need to put into a dropdown menu

[Soupisgoodfood]

Hi. I have a database like this:

create table carMakes (
 makeID int(3) unsigned not null auto_increment primary key,
 make char(20) unique,
 index (make(4))
);
create table carModels (
 modelID int(5) unsigned not null auto_increment primary key,
 model char(20) unique,
 index (model(4)),
 makeID int(4)
);
create table carVariants (
 variantID int(5) unsigned not null auto_increment primary key,
 variant char(30),
 index (variant(4)),
 modelID int(5)
);

And I want to create a drop down menu to choose the make, model, and variant.
I will have to use javaScript and create a set of arrays, and a fucction to swap the options in the dropdown menu.

I can do it with the makes and models. But if I use the same priciple (a nested query) with all the variants. There will be over 700 querys to the database (since there are 700 models). That's a tad silly.

Does anyonw know of a better way to do this?

Thanks,
Justin.

--

The code I currently have:

$makesQuery = 'select makeID, make from carMakes';
$makesResult = mysql_query($makesQuery);
while ($row = mysql_fetch_row($makesResult)) {
 echo "makes[".$row[0]."] = new Option(\"".$row[1]."\" \"".$row[0]."\")'\n";
 echo "models[".$row[0]."] = new Array()\n";
  $modelsQuery = 'select modelID, model from carModels where makeID = '.$row[0];
  $modelsResult = mysql_query($modelsQuery);
  while ($row2 = mysql_fetch_row($modelsResult)) {
   echo "models[".$row[0]."][".$row2[0]."] = new Option(\"".$row2[1]."\" \"".$row2[0]."\")'\n";
   // here is where I would have added another nested $query and while() etc.
  }
  echo "\n\n";
}

[gethan]

The approach I would take with this problem is to make three queries.

1: $makesQuery = 'select makeID, make from carMakes';
2: $modelsQuery = 'select makeID, modelId, model from carModels';
3: $variantsQuery = 'select modelID, variantID, variant from carVariants';

Then I would process the results by forming a data structure that maps make->models->variants. And then run a foreach loop on that structure to build up my javascript.

Hope that gives you some ideas.

[Soupisgoodfood]

Thanks.

When you mean mapping the data. Do you mean loading them into some sort of array? And then having a simlar script to what I already have to create the javascript?

An example of how to map out the data would be great if it's not too much trouble. I'm sure I can figure this out. But just having a bit of trouble getting my head round the concept.

[gethan]

Ok ... off the top of my head - (I don't have php available on this PC.)
[perl]

while ($row = mysql_fetch_array($variantResults))
{
$a = $row[modelId]; $b= $row[variantId];
if (! isset($models[$a])) { $models[$a] = array();}
# Check to see if you have an array for $models[makeid]
# if not make a new one. Now you can push elements on it
$models[$a] = push($models[$a],$b);
}

while ($row = mysql_fetch_array($modelsResults))
{
$a = $row[makeId]; $b= $row[modelId];
if (! isset($makes[$a])) { $makes[$a] = array();}
# see above
$makes[$a] = push($makes[$a],$b);
}
[/perl]

now you have two multidimensional arrays - one that maps makes to arrays of models and one for models to variants.

So something like this will work.

[perl]
foreach ($models as $key => $values) {
foreach ($values as $val) {
print "$key includes $val which in turn contains [";
foreach ($models[$val] as $variant) {
print "$variant,";
}
print "]\n";
# instead do your javascript here.
}
}
[/perl]

Well I hope that works for you - probably some debugging required. Let me know how much ;)