Need help with primary and foregin key use.

[jc1cell]

Hello all,

I'm working on a personal site related to my services and am trying to work on a registration system for clients in order for them to access their own specific area on the site. I have been creating websites for the past 6 months and have felt very comfortable with XHTML/CSS coding but databases and php are even newer to me than that. I decided to work a dynamic section into my site just to get my hands dirty (what better way to learn than with my own site - no foreign pressure or deadlines). Not knowing the first thing about php and databases I'm using the webassist extensions for dreamweaver. I have created a mysql database with three tables in them:

[Client Info] [Project Info] [Milestone Info]

The way it's set up (as I have interpreted the use of primary keys, foreign keys and identifying/non-identifying relationships) for the Milestones table to be a child of the projects table which in turn is a child of the clients table. In essence, milestones records cannot exist if a specific project record is not created and the same for the project record as it relates to the client record:

[Client]..................[Project]..................[Milestone Table]
ID{pr-key aIncr}-¦...ID{pr-key aIncr}--¦
Email.................¦-=ClientID{for-key}..¦--=Pr-ID{for-key}
Password...............Pr-name......................Mil-number
Name.....................Pr-budget....................Mil-details
Location................Pr-timeframe................Mil-amount
RegistrationDate......Pr-requestdate............Mil-payment

This is a crude layout of the schema showing the identifying one to many relationships between the tables (again, as I have interpreted it's use).

What I want to do is have the client fill in a register form that creates a record in the client table. From that form he's sent to the new project form (the client would have direct access to this form from their own site area also) where he would then fill that information also creating a record in the project table. He's then sent to the milestone form where the information creates a record in the milestone table.

Using the webassists extensions for dreamweaver I have created the forms for the registration and the projects (I stopped there to solve/understand the situation).

The registration form works as expected. It fills the record nicely. Now, when the project form is filled it errors out with the following statement:

"Cannot add or update a child row: a foreign key constraint fails (`zonapromo/Projects`, CONSTRAINT `fk_Projects_Clients1` FOREIGN KEY (`Clients_IDclient`) REFERENCES `Clients` (`IDclient`) ON DELETE CASCADE ON UPDATE CASCADE)"

Not knowing anything about PHP, I interpret this as telling me that it's missing the client id foreign key for the new project record. My train of thought took me to trying to create a session (session variable created in the registerform.php) that would keep the email information of the client in a session variable. Then using the recordset binding in dreamweaver (in the projectform.php) I would get the id of the record if email of recordset = to the session variable and have it fill in a form field that would drop into the cient id foreign key in the project table. This however is not working. If I test the binding with a test value from a record in the client table it gives me the information I'm looking for. But through the forms using the session variable does not fill in the field with anything. Again. I'm completely new to database and php so I don't know if my train of thought is correct or if the field should automatically fill into the foreign key.

I'm using a local virtual host (turnkey LAMP appliance, working nicely as far as I can tell) and don't know if that could be an issue.

Just wanted to know if I'm on the right track. If so, where am I going wrong? If not, which way should I be going? Reading some post here in the database section I read about joining and other aspects, but I'm not sure if that relates to me. If I'm overcomplicating (word?) it or what. As stated, total newbie to database and php.

Any positive replies would be great! Files are available if you need to read them. I don't see a way to upload so you would have to let me know how to get them to you if you want to see them.

BTW, excellent forum. I read up on several threads from the different options and found some very good solutions to different situations. Hoping to get mine solved.

jc

[lammert]

Foreign keys are used to ensure that values in a specific field always match an existing value in field in another table. In your case it is used to ensure that you can only add projects to the database after you have added the client. The message that a foreign key constraint fails means that you try to INSERT or UPDATE an entry in the Project table where the field ClientID contains a value which is not present in the ID field of the Client table.

You first have to check that you have setup your foreign key correctly. The ClientID field in the Project table should relate to the ID field in the Client table.

Then while working with the database you always have to make sure that when creating a new Project, you first create the client in the Clients table because the ClientID field only allows values which are present in the table. The only exception is when you allow NULL values in the ClientID field.

[jc1cell]

I appreciate your reply lammert. I feel it has backed up the way I interpreted the database relationships for primary and foreign keys and their basic use. If there were an upload option (I haven't seen one) I would show you an image of the schema created in mysql workbench.

The way things are planned to work is as follows. Current clients would access the new project form directly through their private section. There should be a record in the table for them already. New clients would use the registration form and a new record is created in the client table. The registration form is working like a charm (I was very excited to get that working, I'm loving this database work and web coding and all, it's great.)

Where I'm having the problem is figuring out how to get the client id foreign key filled in the project table. My interpretation is that it has to be filled in when first created. Reading up on some training sources for webassist extensions I have tried to place the email address of the client (either when they login or when they register for the first time) in a session variable (not knowing what I'm doing, I'm not sure I even did that correctly) and creating a recordset in the project form that accesses the variable to compare it to the email column in the client table. When it finds the match, it then brings the info into a field on the project form (thinking it should be a hidden field). When the form is submitted it fills the fk field in the project table.

When I test the recordset by placing a test value (email from a record already in the client table) I get the correct value placed in the field on that would send the client id to the fk column of the project table.

I feel so close to the solution, but just can't seem to get past this section. I'm doing something wrong with the sessions settings is what I'm thinking. If there were upload options I would upload the code for the form where you would see the session information and tell me where I went wrong.

Is code accepted in the posts? Or should I move to the PHP section?

jc