I'm still working on implementing my database design that I've mentioned in a few other threads lately and I'm running into a few questions as I go that I can't seem to get a straight answer on, but i've figured a decent way to word them - hoping someone can provide me with a decent answer!
I have 5 tables:
tblProject
tblDwelling
tblDoorType
tblWallType
tblWindowType
A user creates a new project, that project can have one dwelling associated with it. Each dwelling can be made up of multiple door types, wall types and window types. Each table obviously has a unique ID. The dwelling also has a project ID as an FK, door/window and wall each have a dwelling ID as an FK.
What is the best way to implement this in PHP? I have created a form to create a new project and generate an ID and project name - how do I go about taking that ID, storing it (as a foreign key) in the dwelling and creating a new dwelling. Same goes for each indidual window/door/wall. I really am struggling to get my head around this andwould appreciate any help! Do i need to run a query to grab the project and dwelling ID and store in a session or what? Extremely confused here.
Cheers.
PHP and SQL - Relational Databases
Moderator: General Moderators
Re: PHP and SQL - Relational Databases
Passing a project / dwelling ID is no different than any other value in the form. Either you allow the user to select it using a SELECT dropdown or text input, or you put it as an hidden input (pass it via the URL or any other similar method)
Re: PHP and SQL - Relational Databases
But then we have an issue where this ID could then be changed... how can I validate this?
Currently I am using an auto-incrementing ID for primary keys, so it hasn't needed to be specified in a form.
Currently I am using an auto-incrementing ID for primary keys, so it hasn't needed to be specified in a form.
Re: PHP and SQL - Relational Databases
You misunderstood me - you pass the project ID in a dwelling form, the dwelling ID in a window form etc. Those are still auto-incremented IDs in their original tables. The foreign keys obviously are not auto-incremented numbers, they have to be specified when creating a row
Re: PHP and SQL - Relational Databases
I'll go ahead and try that! I've just discovered this function too:
http://www.php.net/manual/en/function.m ... ert-id.php
http://www.php.net/manual/en/function.m ... ert-id.php