4 Replies - 197 Views - Last Post: 04 February 2020 - 05:12 AM Rate Topic: -----

#1 RobHowdle   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 03-December 19

Multiple dependant drop downs

Posted 03 February 2020 - 05:20 AM

Hi all,

Trying to create multiple dependant drop down menus from a database using php. I've tried to research it and I'm not fully understanding I don't think.

What I've got:

One table with all of the product information IE make, model, year, colour etc.
Multiple sections in a form that are pulling the different columns for the user to select.

Problems:

So my problem is that I am trying to have each drop down box filtered using the input of the previous drop down. Here's an example. Drop down 1 is Make, 2 is Model, 3 is colour.

If the user selects a specific make in drop down 1, then I'm trying to have drop down 2 filter so it only shows the models related to the make that was selected in option one. When we get to drop down 3, the colours are filtered by the make and model in the previous ones.

What I have found in my research is that nobody has used just one table. Everyone has multiple tables with ID's for that row as well as an ID to link it with the other tables. Maybe it's because I'm not understanding but this seems like a very inefficient way of doing it? Having multiple tables with extra columns referencing other tables usually to pull a small bit of data? I could be very wrong and there may be a good reason why it's done this way that I don't know and I'm happy for somebody to correct me.

Is there a way I can do what I'm trying to do with only one table or do I have to create multiple tables? I would assume for everything I want as a drop down item Ie searchable thing such as Make, Model etc, I'd need a new table just for that and then fill that table with everything product wise associated with that term for example if I wanted to add "Features" as another drop down option - I'd assume I'd need a features table with with it's own id, ID of the previous term that would filter that table and display the results?

Thanks
Rob

Is This A Good Question/Topic? 0
  • +

Replies To: Multiple dependant drop downs

#2 Ornstein   User is offline

  • D.I.C Head

Reputation: 32
  • View blog
  • Posts: 64
  • Joined: 13-May 15

Re: Multiple dependant drop downs

Posted 03 February 2020 - 07:04 AM

Are you familiar with normalisation/denormalisation? If not, you might want to read up on it.

As a general rule you'll want to aim for normalisation as much as is practical, but there are times when it's acceptable/necessary to denormalise.

If I was building something like you're describing with data integrity and some degree of scalability in mind, I'd have a table for colours, a table for models, a table for makes and a table linking models and makes. The products table would store the IDs for the colour and model. (I'm assuming you at least understand joining tables?)

In your search filter, establishing the relationship between models and colours gets a bit more difficult - and this is where you may choose to denormalise for the sake of performance (e.g. have another table linking models to colours where that model has products of those colours).

If you insist on using only the one (products) table, it can still be done; you'd just need to query that table at each stage and return the distinct values of interest. Obviously this risks becoming inefficient when you reach some number of products.

Does this help at all?

This post has been edited by Ornstein: 03 February 2020 - 07:05 AM

Was This Post Helpful? 3
  • +
  • -

#3 RobHowdle   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 03-December 19

Re: Multiple dependant drop downs

Posted 03 February 2020 - 07:06 AM

Hi there,

It does indeed thank you very much, I'll look into that now :)
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3070
  • View blog
  • Posts: 11,779
  • Joined: 03-December 12

Re: Multiple dependant drop downs

Posted 03 February 2020 - 08:45 AM

I think Ornstein summed it up pretty well. The tables populating the drop down is exactly what you are after, it prevents screw ups from being spread across the system when someone enters 'white' vs 'White' vs '#FFF'. Your can eaily bring these things together in views so that they are easier to query.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: Multiple dependant drop downs

Posted 04 February 2020 - 05:12 AM

Frankly, even if you had multiple tables, you still haven't solved the problem of doing what you want, have you?

Reasonably, in your code, you'd have something like:
function getAllMakes() { /* your code here */ }

function getModelsForMake($make) { /* your code here */ }

function getColorsForModel($model) { /* your code here */ }



Those functions could be selecting from one table or multiple, it really doesn't matter.

Even leaning heavily on PHP and doing postbacks, you're probably not going to get around the need for Javascript. At which point, depending on the size of your dataset, you could pass the whole table down and do it in the page.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1