populate foreign keys in relational databases from insert record behaviour

I have an enquiry form which sends its data to two different tables in a Database. This is done by creating two insert record behaviours. The tables are - 'enquiries' and 'property_enquiries'.

The 'enquiries' table has 'enquiries_id' as the primary key and 'property_id' as the foreign key.
The 'property_enquiries' table has 'property_id' as the primary key and 'enquiries' as the foreign key.

When the enquiries form is submitted I want both foreign keys created.

Can I use the 'Manage relational table' server behaviour for this or will it require hand coding to achieve this?

Thanks!

your structure doesn't make sense, the foreign key link is not needed in both tables.

manage relational table is used to manage a third linking table to create a many to many relationship, i.e. where an enquiry could realte to many properties.


otherwise, if using a one to many relationship, where an equerry could realte to only one property, you would not need the property ID in the enquiry table, but keep the enquiries_id column in the property table.

use 2 inserts the first to save the to the enquiries table, in the insert, you can set the session name to save the ID to.

In the second insert to the property_enquiries use the session binding for the enquiries column.

Cheers Jason!
I now have the foreign key issue sorted after your post correcting my school boy error!

I am now using the primary key in the "enquiries table" which is "enquiry_id" as the foreign key in the "property_enquiries" table

I now need to take the project to the next stage by introducing a "Further Correspondence Form" that will sit at the bottom of the page that displays data from the 'enquiries' table and the 'property_enquiries' table.

this page will be used when there is further correspondence to the original enquiry where the company rep will select the enquiry from the drop down list at the top. This list displays each 'Estate name' associated with each enquiry. I am using the following code for this drop down.

mysql_select_db($database_RTMFadmin, $RTMFadmin);
$query_rsEstate_name = "SELECT property_enquiries.estate_name, property_enquiries.enquiry_id, enquiries.enquiry_id FROM property_enquiries, enquiries WHERE property_enquiries.enquiry_id = enquiries.enquiry_id";
$rsEstate_name = mysql_query($query_rsEstate_name, $RTMFadmin) or die(mysql_error());
$row_rsEstate_name = mysql_fetch_assoc($rsEstate_name);
$totalRows_rsEstate_name = mysql_num_rows($rsEstate_name);

The drop down looks like this -

<ul class="dropdown-menu">
<?php
// RepeatSelectionCounter_1 Begin Loop
$RepeatSelectionCounter_1_IterationsRemaining = $RepeatSelectionCounter_1_Iterations;
while($RepeatSelectionCounter_1_IterationsRemaining--){
if($RepeatSelectionCounterBasedLooping_1 || $row_rsEstate_name){
?>
<li><a href="enquiries.php?enquiry_id=<?php echo $row_rsEstate_name['enquiry_id']; ?>"><?php echo $row_rsEstate_name['estate_name']; ?></a></li>
<?php
} // RepeatSelectionCounter_1 Begin Alternate Content
else{
?>
<?php } // RepeatSelectionCounter_1 End Alternate Content
if(!$RepeatSelectionCounterBasedLooping_1 && $RepeatSelectionCounter_1_IterationsRemaining != 0){
if(!$row_rsEstate_name && $RepeatSelectionCounter_1_Iterations == -1){$RepeatSelectionCounter_1_IterationsRemaining = 0;}
$row_rsEstate_name = mysql_fetch_assoc($rsEstate_name);
}
$RepeatSelectionCounter_1++;
} // RepeatSelectionCounter_1 End Loop
?>
</ul>

By selecting an estate name will display the associated enquiry.

To display the correct data I am using the following query.

$varEnquiryID_rsEnquiry = "-1";
if (isset($_GET['enquiry_id'])) {
$varEnquiryID_rsEnquiry = $_GET['enquiry_id'];
}
mysql_select_db($database_RTMFadmin, $RTMFadmin);
$query_rsEnquiry = sprintf("SELECT * FROM enquiries, property_enquiries WHERE enquiries.enquiry_id = property_enquiries.enquiry_id AND enquiries.enquiry_id = %s", GetSQLValueString($varEnquiryID_rsEnquiry, "int"));
$rsEnquiry = mysql_query($query_rsEnquiry, $RTMFadmin) or die(mysql_error());
$row_rsEnquiry = mysql_fetch_assoc($rsEnquiry);
$totalRows_rsEnquiry = mysql_num_rows($rsEnquiry);

As you can see I am using a variable to retrieve the correct data from the specific drop down 'enquiry_id.


Some of this code might not be totally correct but it seems to work.

The problem I now have is trying to link up the further correspondence with the page.

When the 'further correspondence' form is submitted it will be sent to a THIRD table "enq_correspondence" in the Database. So then when a selection is made from the drop down list the correspondence is displayed at the bottom of the original enquiry.

I believe this might need to use an INNER JOIN in the tables but I cannot seem to get it to work. I assume I need to do this in the above samples but this is beyond my comfort zone/ ability.

Its worth mentioning that I have also used the primary key in the "enquiries" table as a foreign key in the "enq_correspondence" table

I have attached images of the enquiry form and the data display page with 'further correspondence' form.

If this requires paid support I am happy to do so!

Cheers!

I'm not really sure what the question is.


Please provide a Skype username or a US phone number and a good time to contact you when you will be in front of your computer and we will have an engineer contact you to resolve this issue over the phone.

We are available Monday - Friday from 9am - 4pm EST (except major U.S. holidays).

Ok Ill try again..

At the moment enquiries page displays Data from the "enquiries" table and the "property_enquiries" table. The user selects the data from the Select Enquiry by Estate" drop down menu. Each menu item sends its value to the url variable attached to there query that displays the data on the page.

The problem I've got is I want a form at the bottom of this page that a user completes during further correspondence ( phone call ). The form sends Data to a third Database table. As and when there is Data in the "further correspondence" table, I want to display this data at the end of the "enquiries" and "property_enquiries" data id displayed.

So when the user selects an option from the drop down it displays data from THREE tables.

SO my question is how do I set the "recordsets/queries" so they can send retrieve data form THREE tables instead of two?

I hope this is clear. If not I will forward a Skype username.

Thanks

your query would need to use to joins, one to join property_enquiries table, and the other to join the enq_correspondence

SELECT *
FROM enquiries
INNER JOIN property_enquiries ON enquiries.enquiry_id = property_enquiries.enquiry_id
INNER JOIN enq_correspondence ON enquiries.enquiry_id = enq_correspondence.enquiry_id


i'm just guessing the the enq_correspondence table has a column named enquiry_id, you will need to adjust accordingly if you use a different name.

Hi Jason.

The problem with the above query is that it only works if all three tables have related data. There will always be related data in TWO tables FIRST because you will first get an enquiry which will only put data in the 'enquiries' and 'property_enquiries' tables, At that point there is no 'Further Correspondence' thus there is no related data in the 'enq_crspndnce' table.

So my first question is how can I adjust the query so it can display records that don't have any 'Further Correspondence', so would only be pulling data from the 'enquiries' and 'property_enquiries' tables. But when 'Further Correspondence' is recorded it can also display records from all three tables.

I hope this is making sense!

My next problem is with the 'Further Correspondence Form' which is now on its own page.

Once the Admin user raises an enquiry on the 'Enquiries Details Page' there will be a button at the bottom of the record being displayed saying 'Add Correspondence' which they would click to be directed to the 'Further Correspondence Form'.

When the'Further Correspondence Form' is submitted I need the foreign key ( enquiry_id ) to be entered in the 'enq_correspondence table' that is related to the enquiry details being viewed. This is so I can JOIN the tables with the enquiry_id

Thanks!

try using left outer join in the query instead of inner join. for more details on the different join types, see this page:
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/


On the Enquiries Details Page page, the Add Correspondence link should pass the enquiry_id to the Further Correspondence Form page, for example:

FurtherCorrespondence.php?enquiry_id=3


use a hidden form element to capture the enquiry_id URL variable and store it in the insert behavior.

This post has been deleted.

Jason thanks for your help on this. I now have all the queries and behaviours working thus far.

My final problem is trying to had some kind of validation to the forms. I have tried using the tutorials on server validation but I can not seem to adjust it so it works with the insert record behaviours I have with the forms.

For example the enquiry forms insert record behaviour directs to the enquiries page which displays the data submitted from the form. This seems to confuse things with what should be the action page when setting the validation. It doesn't seem to stack up!! I've tried to proceed with the tutorial by applying server validation to the form page and the enquiries page but neither works.

Any idea on this?

Cheers!