PHP Mysql Drop down car database

[alsheron]

Please bear with me on this one, i'm finding it hard to explain what i'm trying to do clearly, but here goes:

I've recently decided it's about time i learnt some back-end "developer" stuff instead of just carrying on being a pure web designer. I'm learning loads about CSS at the minute and am thoroughly loving it, but i'm now turning my attention to learning some PHP MySql and relevant scripting to make dynamic database driven sites.

The current project i have is to create or modify an existing site for a car dealer i designed so that the stock pages are based on a database instead of being manually entered pages.

I fairly recently bought a package calle easyGen (which i now regret) which supposedly does all the work for you in setting up pages to use PHP to dynamically update database records from web-pages, enter stock and display data from the database and so forth.

I tried to use easyGen to create a database driven website for this car dealer, but it's limitations are showing very early on.

What i'm trying to do is:

1) Set up a database with a table (or tables - this itself is confusing me) that holds data about cars currently in stock in fields like manufacturer, model, colour, year, price, transmission etc.

2) Create a web form where a user can enter in the above fields and submit the data to the database. Crucially, i want certain fields like manufacturer and model to be entered using a drop down list populated by database fields.

3) VERY importantly, i need the "model" drop down list to populate itself from the manufacturer field which will be selected first. For example, if "ford" is chose for manufacturer, then i'd want the "model" drop down list to only list Ford models and if BMW is selected then the "model" drop down would then show only BMW models. How can this be done - i think it's fairly simple, but of course if you don't know something then its going to seem hard.

I'm also not too sure on how to organise the database tables. For example, i was having great difficulty deciding whether to have a table with fields "model" and "manufacturer" together and no other fields in the table because of course a manufacturer would always have a model (including colours in this table i thought wouldn't work because then a colour would always be attached to a manufacturer and model - which isn't always the case, so colours would need to be stored separately?)

To sum up: I hope i've been clear enough above. I'm not asking for someone to do this for me as of course i want to learn it myself.

My particular question is how to make one drop down list populated by database fields cause (instly - client-side?) another drop down list to be populated by fields relating to the initial choice.

A good example of what i mean, and actually something i'd like to replicate exactly is on www.autotrader.co.uk on the left. Depending on what MAKE is selected, the model drop down changes accordingly.

From the books i've flicked through in bookshops (i'm looking for an excellent book describing how to do all this) i can't see how you can re-generate a drop down from a value chosen from another driop-down.

I'm pretty certain i'm not the first to need to do this relating to cars but searches on t'internet for similar projects/tutorials have proven fruitless so far. Does anyone know of any example material?

Of course i also need a primer/tutorial to get me started with understanding how to properly construct and organise databases. I did a bit of Oracle at university and know a bit about Normalisation for example, but not enought for it to be practically useful - but i do know about the importance of removing redundancy from database for eg. (obviously at the moment all of this is to a fairly simple level - i'm not asking for advanced stuff - yet!)

I'm sure a few Neowinians have gone through this process themselves - learning all this, and hope maybe someone can offer some advice/links/poin me in the right direction.

That was a LONG one....... phew..... :D

[Kudos Veteran]

  alsheron said:

3) VERY importantly, i need the "model" drop down list to populate itself from the manufacturer field which will be selected first. For example, if "ford" is chose for manufacturer, then i'd want the "model" drop down list to only list Ford models and if BMW is selected then the "model" drop down would then show only BMW models. How can this be done - i think it's fairly simple, but of course if you don't know something then its going to seem hard.

For conditional dropdowns you'll need javascript, tutorials on manipulating forms (which is what dropdowns are in) are plentiful on the web so a bit of googling will sort that out i reckon.

  alsheron said:

I'm also not too sure on how to organise the database tables. For example, i was having great difficulty deciding whether to have a table with fields "model" and "manufacturer" together and no other fields in the table because of course a manufacturer would always have a model (including colours in this table i thought wouldn't work because then a colour would always be attached to a manufacturer and model - which isn't always the case, so colours would need to be stored separately?)

Use as many tables as you like, even just for the sake of semantics. Your site isn't (i would imagine) going to have heavy database traffic so i don't think you need to worry about optimisation right now. A better way of thinking of it is every model has a manufacturer, ie you could talk about ford without needing to mention a car model, but you'd usually mention ford when talking about fiesta. For this reason i think you should have a table for manufacturer names which is referenced by the car model table, at the very least it's good practice.

  alsheron said:

My particular question is how to make one drop down list populated by database fields cause (instly - client-side?) another drop down list to be populated by fields relating to the initial choice.

A good example of what i mean, and actually something i'd like to replicate exactly is on www.autotrader.co.uk on the left. Depending on what MAKE is selected, the model drop down changes accordingly.

As i said earlier, javascript.

  alsheron said:

Of course i also need a primer/tutorial to get me started with understanding how to properly construct and organise databases. I did a bit of Oracle at university and know a bit about Normalisation for example, but not enought for it to be practically useful - but i do know about the importance of removing redundancy from database for eg. (obviously at the moment all of this is to a fairly simple level - i'm not asking for advanced stuff - yet!)

I recommend getting a good book on PHP/MySQL and, time allowing, read through the whole thing and do the sample code before working on the car site. If you are stuck for time, just jump through the sections you think are relevant. Anything you get stuck on, just post here and we'll try to help.

http://www.php.net/manual/en/ is an invaluable reference.

Hope this helps.

[trigger_my_passion]

http://www.dynamicdrive.com/dynamicindex1/...dmenu/index.htm

http://www.js-examples.com/page/javascript...le.html?id=1049.

[Trix.Neo]

To add to Kudos with the Javascript for the drop downs try looking out for AJAX as that would be a valuable thing to have as a resource for future projects.

Neo