Goal. For each client, they have a minimum of 1 pet, though upwards of n pets.
Two tables: client & pets; Two forms: client form & pet form. The parent table/form is the client. Complete & submitting the Client form will navigate to the Add a Pet form. When the pet form is complete the user has the option to end or add additional pets. Knowing the level of knowledge here, I’m here as a novice to learn with no need to over-design this simple database application.
1st off, having read through Kevin Yanks 4th edition Database Driven Web Site (great book / reference), I didn’t find any examples that fit the application model noted above. I did, however, learn how to set up the tables and much of the PHP involved with getting the form data into the table.
My hurdle is discovering how to use/transfer the mysql_insert_id() from the client query to the pets query in order to set up the one-to-many relationship for the eventual join queries.
links to examples would be greatly welcomed as I can’t imagine this is anything new.
// we need to start the session if we are accessing $_SESSION
session_start();
$query = mysql_query("INSERT INTO clients (a, b) VALUES ('$a', '$b')");
$newID = mysql_insert_id();
// at this stage I would store the client id into the session to remember it for the pet form
$_SESSION['client_id'] = $newID;
Now for the pet form
// we need to start the session if we are accessing $_SESSION
session_start();
$client_id = (int)$_SESSION['client_id'];
$query = mysql_query("INSERT INTO pets (client_id, b) VALUES ($client_id, '$b')");
This is probably the easiest it can be, perhaps the next level would be to allow user logins to retrieve and edit their pets in the future.
Worked brilliantly! Set within two controller pages (thanks Kevin Yank for that great direction) the data flow to the two tables worked perfectly. This has set me up to begin working on the join query.