close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

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 s.joiner74419001 | 1017 views | 18 replies

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!

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