close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 or 2018 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

populate foreign keys in relational databases from insert record behaviour

Thread began 9/11/2014 7:02 am by s.joiner74419001 | Last modified 10/06/2014 5:30 pm by Jason Byrnes | 993 views | 18 replies |

s.joiner74419001

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!

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

s.joiner74419001

displaying data from a third table

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!

Sign in to reply to this post

Jason ByrnesWebAssist

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).

Sign in to reply to this post

s.joiner74419001

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

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

s.joiner74419001

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!

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

s.joiner74419001

This post has been deleted.

s.joiner74419001

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!

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...